1.导入Maven依赖
<!--xls(03)-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
<!--xlsx(07)-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
<!--日期格式化工具-->
<dependency>
<groupId>joda-time</groupId>
<artifactId>joda-time</artifactId>
<version>2.10.1</version>
</dependency>
<!--test-->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
2.根据实际需求,写入读取路径。
读取不同类型的Excel数据:
public void readCellType(FileInputStream inputStream) throws IOException {
// 创建一个工作簿,使用excel能操作的这边他都可以操作!
Workbook workbook = new HSSFWorkbook(inputStream);
Sheet sheet = workbook.getSheetAt(0);
// 获取标题内容
Row rowTitle = sheet.getRow(0);
if (rowTitle != null) {
// 读取多少列有数据
int cellCount = rowTitle.getPhysicalNumberOfCells();
for (int cellNum = 0; cellNum < cellCount; cellNum++) {
Cell cell = rowTitle.getCell(cellNum);
if (cell != null) {
int cellType = cell.getCellType();
String cellValue = cell.getStringCellValue();
System.out.print(cellValue + "|");
}
System.out.println();
}
}
// 获取表中的内容
int rowCount = sheet.getNumMergedRegions();
for (int rowNum = 1; rowNum < rowCount; rowNum++) {
Row rowData = sheet.getRow(rowNum);
if (rowData != null) {
// 读取列
int cellCount = rowTitle.getPhysicalNumberOfCells();
for (int cellNum = 0; cellNum < cellCount; cellNum++) {
System.out.print("[" + (rowNum+1) + "-" + (cellNum+1) + "]");
Cell cell = rowData.getCell(cellNum);
// 匹配列的数据类型
if (cell != null) {
int cellType = cell.getCellType();
String cellValue = "";
switch (cellType) {
case HSSFCell.CELL_TYPE_STRING: // 字符串
System.out.print("[String]");
cellValue = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_BOOLEAN: // 字符串
System.out.print("[boolean]");
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_BLANK: // 空
System.out.print("[blank]");
break;
case HSSFCell.CELL_TYPE_NUMERIC: // 数字(日期)
System.out.println("[numeric]");
if (HSSFDateUtil.isCellDateFormatted(cell)) { // 日期
System.out.print("[日期]");
Date date = cell.getDateCellValue();
cellValue = new DateTime(date).toString("yyyy-MM-dd");
} else {
// 不是日期格式,防止数字过长!
System.out.print("[转化为字符串输出]");
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cellValue = cell.toString();
}
break;
case HSSFCell.CELL_TYPE_ERROR: // 空
System.out.print("[数据类型错误]");
break;
}
System.out.println(cellValue);
}
}
}
}
inputStream.close();
}
获取Excel表中的计算公式:
public void poiFormula(FileInputStream inputStream) throws IOException {
Workbook workbook = new HSSFWorkbook(inputStream);
Sheet sheet = workbook.getSheetAt(0);
Row row = sheet.getRow(1);
Cell cell = row.getCell(0);
// 拿到计算公式 eval
FormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook)workbook);
// 输出单元格的内容
int cellType = cell.getCellType();
switch (cellType) {
case Cell.CELL_TYPE_FORMULA: // 公式
String formula = cell.getCellFormula();
System.out.println(formula);
// 计算
CellValue evaluate = formulaEvaluator.evaluate(cell);
String cellValue = evaluate.formatAsString();
System.out.println(cellValue);
break;
}
}
注意:
HSSFWorkbook:是操作Excel2003以前(包括2003)的版本,扩展名是.xls;
XSSFWorkbook:是操作Excel2007后的版本,扩展名是.xlsx;
SXSSFWorkbook:是操作Excel2007后的版本,扩展名是.xlsx;