POI - 读取Excel2003、Excel2007的兼容性问题

POI - 读取Excel2003、Excel2007的兼容性问题

错误信息:

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)
	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 com.playmath.common.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读取到的文件名称是excel2003版本, 而文件中的数据是以Office2007+XML保存的, 导致通过excel2003版本创建出来的Workbook 类去简析该文件中以Office2007+XML保存的数据, 从而造成上面版本不兼容性问题.

解决方案:

try {
	/** 根据版本选择创建Workbook的方式 */
	/*Workbook wb = null;
	if (isExcel2003) {
		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读取Excel2003、Excel2007的兼容性问题是因为使用poi-ooxml中的WorkbookFactory.create(inputStream)来创建Workbook，HSSFWorkbook和XSSFWorkbook都实现了Workbook接口, 且WorkbookFactory对读取Excel做了内置处理.

Workbook wb = null; 
if (isExcel2003) {
    //2003及以下版本使用 
    wb = new HSSFWorkbook(inputStream);
 } else {
    //2007及以上版本使用 
    wb = new XSSFWorkbook(inputStream);
 }

总结: 通过上面的方法创建Workbook 类来读取Excel数据时,必须保证文件的后缀名和文件中数据的版本一致, 否则会抛上面的那个不兼容异常信息