引入maven依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.0.0</version>
</dependency>
利用FileMagic对文件格式进行判断
public ResResult importExcel(MultipartFile file) {
try {
InputStream in = new BufferedInputStream(file.getInputStream());
/**
* 优化excel类型判断
* 注意可能会获取报错 getFileMagic() only operates on streams which support mark(int)
* 原因,BufferedInputStream中markSupported方法返回值为false造成的InputStream中返回值是true,所以改为InputStream is = new BufferedInputStream(multipartFile.getInputStream())
*/
FileMagic fileMagic = FileMagic.valueOf(in);
if (Objects.equals(fileMagic, FileMagic.OLE2)) {
return importExcelXLS(file); // 这是xls
} else if (Objects.equals(fileMagic, FileMagic.OOXML)) {
return importExcelXLSX(file); // 这是xlsx
}
} catch (Exception e) {
e.printStackTrace();
}
return ResResult.fail("导入异常!");
}
HSSFWorkbook
读取xls文件,97-2003版本的excel
package com.excel;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
/**
* 一个EXCEL包含如下几个基础组成
* HSSFSheet:表单,通过HSSFWorkbook获取
* Row:行,通过HSSFSheet获取
* Cell:单元格,通过Row获取
* 下标均从0开始
* 导入97-2003版本的excel,xls格式
* */
public ResResult importExcelXLS(MultipartFile file) throws FileNotFoundException {
if (file.isEmpty()) {
return ResResult.fail("文件为空!");
}
InputStream inputStream = null;
try {
inputStream = file.getInputStream();
HSSFWorkbook wb = new HSSFWorkbook(inputStream);
// 获取sheet页数
int sheetNum = wb.getNumberOfSheets();
for (int i=0;i<sheetNum;i++) {
Sheet sheet = wb.getSheetAt(i);
for (int rowNum=1;rowNum<=sheet.getLastRowNum();rowNum++) {
// 获取行数
Row row = sheet.getRow(rowNum);
log.warn("开始读取第"+(i+1)+"个Sheet!第"+rowNum+"行!该行一共"+row.getLastCellNum()+"列!");
Cell orderNumber = row.getCell(1);
if (null != orderNumber) {
orderNumber.setCellType(CellType.STRING);
orderDelivery.setOrderNumber(orderNumber.getStringCellValue());
}
Cell deliveryName = row.getCell(2);
if (null != deliveryName) {
deliveryName.setCellType(CellType.STRING);
orderDelivery.setDeliveryName(deliveryName.getStringCellValue());
}
}
} catch (IOException e) {
log.error("excel文件读取异常:"+ e);
e.printStackTrace();
return ResResult.fail("导入异常,请咨询管理员!");
} finally {
try {
inputStream.close();
} catch (IOException e) {
log.error("excel文件流关闭异常:"+ e);
e.printStackTrace();
return ResResult.fail("导入异常,请咨询管理员!");
}
}
}
XSSFWorkbook
读取xlsx文件,高于2003版本的excel,读写均与HSSFWorkbook保持一致
或者直接使用开源组件Excel-Boot
GitHub - programmeres/excel-boot: Easy-POI是一款Excel导入导出解决方案组成的轻量级开源组件。
兼容
Workbook workbook=null;
if (is2003Excel){
workbook= new HSSFWorkbook(inputStream);
}else {
workbook = new XSSFWorkbook(inputStream);
}