【xls是excel2003及以前版本生成的文件格式,而xlsx是excel2007及以后版本生成的文件格式】
一、场景:
压缩包方式导入,需要兼容xlsx和xls的excel文件,文件流的方式作为入参
二、遇到问题:
org.apache.poi.poifs.filesystem.OfficeXmlFileException: The supplied data appears to be in the Office 2007+ XML. You are calling the part of POI that deals with OLE2 Office Documents. You need to call a different part of POI to process this data (eg XSSF instead of HSSF)
翻译一下:提供的数据似乎在Office 2007+XML中。您正在调用POI中处理OLE2 Office文档的部分。您需要调用POI的不同部分来处理这些数据(例如XSSFWorkbook代替HSSFWorkbook)
问题原因:上传文件类型和读取Workbook不对应【XSSFWorkbook用来读取xlsx类型的,HSSFWorkbook用来读取xls类型的】
三、解决办法:
List<ImportAddStoreEntity> myObjects = new ArrayList<>();
try (ZipInputStream zis = new ZipInputStream(zisInputStream,Charset.forName("GBK")))
{
ZipEntry entry;
while ((entry = zis.getNextEntry()) != null) {
// 获取Excel文件的输入流
InputStream inputStream = zis;
// 创建工作簿对象
Workbook workbook = null;
try {
workbook = new XSSFWorkbook(inputStream);
}catch (Exception ex){
workbook = new HSSFWorkbook(inputStream);
}
//读取excel内容到实体类
readExcelFile(workbook,myObjects);
inputStream.close();
zis.closeEntry();
}
}catch (IOException e) {
}
/**
* 读取excel内容到实体类
* @param workbook
* @param myObjects
* @throws Exception
*/
private static void readExcelFile(Workbook workbook,List<ImportEntity>
myObjects) throws Exception
{
// 获取第一个工作表
Sheet sheetAt = workbook.getSheetAt(0);
int rowNum = sheetAt.getLastRowNum();
// 第一行为标题,从第二行开始读取
for (int i = 2; i <= rowNum; i++) {
Row row;
row = sheetAt.getRow(i);
if (row != null) {
ImportEntity obj = createExcelEntity(row);
myObjects.add(obj);
}
}
// 关闭工作簿
workbook.close();
}
/**
* 获取excel行数据的每一列数据填充到实体类
* @param row
*/
private static ImportEntity createExcelEntity(Row row) {
ImportEntity obj = new ImportEntity();
DataFormatter dataFormatter = new DataFormatter();
for (int j = 0; j < row.getLastCellNum(); j++) {
Cell cell = row.getCell(j);
String cellValue = dataFormatter.formatCellValue(cell);
if(j == 0){
obj.setStoreName(cellValue);
}else if(j == 1){
obj.setSelfStatusStr(cellValue);
}else if(j == 2){
obj.setCooperationStateStr(cellValue);
}
}
return obj;
}