POI - 读取Excel、Excel的兼容性问题
错误信息:
org.apache.poi.poifs.filesystem.OfficeXmlFileException: The supplied data appears to be in the Office + 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)at org.apache.poi.poifs.storage.HeaderBlock.<init>(HeaderBlock.java:131)at org.apache.poi.poifs.storage.HeaderBlock.<init>(HeaderBlock.java:104)at org.apache.poi.poifs.filesystem.POIFSFileSystem.<init>(POIFSFileSystem.java:138)at org.apache.poi.hssf.usermodel.HSSFWorkbook.<init>(HSSFWorkbook.java:322)at org.apache.poi.hssf.usermodel.HSSFWorkbook.<init>(HSSFWorkbook.java:303)at mon.util.ExcelUtil.read(ExcelUtil.java:225)at com.playmath.playmathcrm.controller.StudentController.importStudentInfo(StudentController.java:484)at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)at java.lang.reflect.Method.invoke(Method.java:498)at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:209)at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:136)at
错误原因:通过POI读取到的文件名称是excel版本,而文件中的数据是以Office+XML保存的,导致通过excel版本创建出来的Workbook 类去简析该文件中以Office+XML保存的数据,从而造成上面版本不兼容性问题.
解决方案:
try {/** 根据版本选择创建Workbook的方式 *//*Workbook wb = null;if (isExcel) {wb = new HSSFWorkbook(inputStream);} else {wb = new XSSFWorkbook(inputStream);}*/Workbook wb = WorkbookFactory.create(inputStream);dataLst = read(wb, sheetIndex);} catch (IOException e) {e.printStackTrace();} catch (InvalidFormatException e) {e.printStackTrace();}
之所以用上面的方法解决POI读取Excel、Excel的兼容性问题是因为使用poi-ooxml中的WorkbookFactory.create(inputStream)来创建Workbook，HSSFWorkbook和XSSFWorkbook都实现了Workbook接口,且WorkbookFactory对读取Excel做了内置处理.
Workbook wb = null; if (isExcel) {//及以下版本使用 wb = new HSSFWorkbook(inputStream);} else {//及以上版本使用 wb = new XSSFWorkbook(inputStream);}
总结:通过上面的方法创建Workbook类来读取Excel数据时,必须保证文件的后缀名和文件中数据的版本一致,否则会抛上面的那个不兼容异常信息
