1 添加依赖
.xls格式的excel文件需要HSSF支持,需要相应的poi.jar,.xlsx格式的excel文件需要XSSF支持,需要poi-ooxml.jar,
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
2 解析
/**
* 根据不同的格式获取不同的对象
*
* @param in 输入流
* @param fileName 文件名
* @return Excel的对象
* @throws Exception
*/
public Workbook getWorkbook(InputStream in, String fileName) throws Exception {
Workbook workbook = null;
String fileType = fileName.substring(fileName.lastIndexOf("."));//获得后缀
if (".xls".equals(fileType)) {
workbook = new HSSFWorkbook(in);
} else if (".xlsx".equals(fileType)) {
workbook = new XSSFWorkbook(in);
} else {
throw new Exception("请上传excel文件!");
}
return workbook;
}
public void readExcel(InputStream in, String fileName){
try {
Workbook wb = getWorkbook(in, fileName);//获得正确的流
//获取第一个sheet页
Sheet sheet= wb.getSheetAt(0);
int firstRowIndex = sheet.getFirstRowNum()+1; //第一行是列名,所以不读
int lastRowIndex = sheet.getLastRowNum();
System.out.println("firstRowIndex: "+firstRowIndex);
System.out.println("lastRowIndex: "+lastRowIndex);
for(int rIndex = firstRowIndex; rIndex <= lastRowIndex; rIndex++) { //遍历行
System.out.println("rIndex: " + rIndex);
Row row = sheet.getRow(rIndex);
if (row != null) {
int firstCellIndex = row.getFirstCellNum();
int lastCellIndex = row.getLastCellNum();
System.out.println("firstCellIndex = " + firstCellIndex + ".lastCellIndex = " + lastCellIndex);
for (int cIndex = firstCellIndex; cIndex < lastCellIndex; cIndex++) { //遍历列
Cell cell = row.getCell(cIndex);
if (cell != null) {
System.out.print(getCellFormatValue(cell) + " ");
}
}
}
System.out.println();
}
} catch (Exception e) {
e.printStackTrace();
}
}
public Object getCellFormatValue(Cell cell) {
Object cellValue = null;
if (cell == null) {
return "";
}
//cell 类型
CellType cellType = cell.getCellTypeEnum();
// 判断cell类型
switch (cellType) {
//数字类型
case NUMERIC: {
cellValue = NumberToTextConverter.toText(cell.getNumericCellValue());
break;
}
//字符串类型
case STRING: {
cellValue = cell.getRichStringCellValue().getString();
break;
}
//空
case BLANK: {
cellValue = "";
break;
}
default:
System.out.println("无法解析的类型");
cellValue = "";
}
return cellValue;
}
3 Controller调用
@PostMapping("/uploadExcel")
public void uploadExcel(@RequestBody MultipartFile file) {
try {
InputStream inputStream = file.getInputStream();
excelImportService.readExcel(inputStream, file.getOriginalFilename());
inputStream.close();
} catch (Exception ex) {
}
}
参考博客:https://blog.csdn.net/gxx_csdn/article/details/79085713