1.pom依赖如下
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.16</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.47</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
2.测试代码
package com.example.demo.test;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.ObjectUtils;
import java.io.*;
import java.text.DecimalFormat;
import java.util.HashMap;
import java.util.Map;
public class ImportExcel {
public static void main(String[] args) {
parseRowCell(new File("C:\\Users\\hasee\\Desktop\\测试导入.xls"));
}
/**
* 解析表格
* @param file 文件,只支持.xls,.xlsx类型文件
* @return
*/
private static String parseRowCell(File file) {
String filename = file.getName();
InputStream is = null;
try {
is = new FileInputStream(file);
} catch (FileNotFoundException e) {
e.printStackTrace();
}
Map map = new HashMap<>();
try {
Workbook workbook = null;
// 判断excel的后缀,不同的后缀用不同的对象去解析
// xls是低版本的Excel文件
if (filename.endsWith(".xls")) {
workbook = new HSSFWorkbook(is);
}
// xlsx是高版本的Excel文件
if (filename.endsWith(".xlsx")) {
workbook = new XSSFWorkbook(is);
}
// 取到excel 中的第一张工作表
Sheet sheet = workbook.getSheetAt(0);
// 第一行开始获取内容
for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {
// 获取到这一行的数据
Row row = sheet.getRow(rowNum);
System.out.print("--------第"+(rowNum+1)+"行--------");
//获取这一行总列数
short lastCellNum = row.getLastCellNum();
//获取当前行的数据
for (int i=0;i<lastCellNum;i++){
Cell cell = row.getCell(i);
if (ObjectUtils.isEmpty(cell)){
System.out.print("null ");
}else {
String cellValue = getCellValue(cell, cell.getCellTypeEnum().getCode());
// System.out.print("参数类型:"+cell.getCellTypeEnum());
System.out.print(cellValue+" ");
}
}
System.out.println();
}
return "true";
} catch (IOException e) {
return e.getMessage();
}
}
//获取数据类型然后获取到值
protected static String getCellValue(Cell cell,int type){
String cellValue = "";
DecimalFormat df = new DecimalFormat("#");
switch (type) {
case HSSFCell.CELL_TYPE_STRING:
cell.getRichStringCellValue();
cellValue = cell.getRichStringCellValue().getString().trim();
break;
case HSSFCell.CELL_TYPE_NUMERIC:
cellValue = df.format(cell.getNumericCellValue()).toString();
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
cellValue = String.valueOf(cell.getBooleanCellValue()).trim();
break;
case HSSFCell.CELL_TYPE_FORMULA:
FormulaEvaluator evaluator = cell.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator();
int cellType = evaluator.evaluateFormulaCell(cell);
CellValue _cellV = evaluator.evaluate(cell);
switch (cellType) {
case HSSFCell.CELL_TYPE_STRING:
cellValue = _cellV.getStringValue().trim();
break;
case HSSFCell.CELL_TYPE_NUMERIC:
cellValue = df.format(_cellV.getNumberValue());
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
cellValue = String.valueOf(_cellV.getBooleanValue()).trim();
break;
default:
cellValue = null;
}
break;
default:
cellValue = null;
}
return cellValue;
}
}
3.表格文件内容和代码输出结果如下图