使用apache POI-FileNotFound读取excel 2003文件

Question

我正在写一些代码导入Excel文件到数据库。文件可能很大（数千行），所以我使用事件API。POI版本为3.9

我这样打开文件：FileInputStream fin=new FileInputStream（file）；

//create record listener
HSSFRecordListener mainListener =  new HSSFRecordListener("aaa.xls");
// create a new org.apache.poi.poifs.filesystem.Filesystem
POIFSFileSystem poifs = new POIFSFileSystem(fin);
// get the Workbook (excel part) stream in a InputStream
din = poifs.createDocumentInputStream("Workbook");

有些文件在最后一行引发FileNotFoundException。的确，如果我用7zip打开那些文件，就没有workbook条目，而是有book。

try {
    din = poifs.createDocumentInputStream("Workbook");
} catch (FileNotFoundException e) {
    try {
        din = poifs.createDocumentInputStream("Book");
    } catch (FileNotFoundException e1) {                    
        FileNotFoundException e2 = new FileNotFoundException("Neither Workbook nor Book found in file!");                    
        e2.initCause(e1);
        throw e2;
    }
}

org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.apache.poi.hssf.record.RecordFormatException: Unable to construct record instance
    org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:894)
    org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:789)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:647)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
    org.netbeans.modules.web.monitor.server.MonitorFilter.doFilter(MonitorFilter.java:393)
root cause

org.apache.poi.hssf.record.RecordFormatException: Unable to construct record instance
    org.apache.poi.hssf.record.RecordFactory$ReflectionConstructorRecordCreator.create(RecordFactory.java:65)
    org.apache.poi.hssf.record.RecordFactory.createSingleRecord(RecordFactory.java:301)
    org.apache.poi.hssf.record.RecordFactoryInputStream$StreamEncryptionInfo.<init>(RecordFactoryInputStream.java:65)
    org.apache.poi.hssf.record.RecordFactoryInputStream.<init>(RecordFactoryInputStream.java:182)
    org.apache.poi.hssf.eventusermodel.HSSFEventFactory.genericProcessEvents(HSSFEventFactory.java:139)
    org.apache.poi.hssf.eventusermodel.HSSFEventFactory.processEvents(HSSFEventFactory.java:106)
    pl.veracomp.service.SpreadsheetImportService.process(SpreadsheetImportService.java:369)
    pl.veracomp.controller.uploadController.onSubmit(uploadController.java:57)
    org.springframework.web.servlet.mvc.SimpleFormController.processFormSubmission(SimpleFormController.java:272)
    org.springframework.web.servlet.mvc.AbstractFormController.handleRequestInternal(AbstractFormController.java:268)
    org.springframework.web.servlet.mvc.AbstractController.handleRequest(AbstractController.java:153)
    org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(SimpleControllerHandlerAdapter.java:48)
    org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:923)
    org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:852)
    org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:882)
    org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:789)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:647)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
    org.netbeans.modules.web.monitor.server.MonitorFilter.doFilter(MonitorFilter.java:393)
root cause

org.apache.poi.hssf.record.RecordFormatException: Not enough data (0) to read requested (2) bytes
    org.apache.poi.hssf.record.RecordInputStream.checkRecordPosition(RecordInputStream.java:216)
    org.apache.poi.hssf.record.RecordInputStream.readShort(RecordInputStream.java:233)
    org.apache.poi.hssf.record.InterfaceHdrRecord.<init>(InterfaceHdrRecord.java:43)
    sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
    sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
    java.lang.reflect.Constructor.newInstance(Constructor.java:513)
    org.apache.poi.hssf.record.RecordFactory$ReflectionConstructorRecordCreator.create(RecordFactory.java:57)
    org.apache.poi.hssf.record.RecordFactory.createSingleRecord(RecordFactory.java:301)
    org.apache.poi.hssf.record.RecordFactoryInputStream$StreamEncryptionInfo.<init>(RecordFactoryInputStream.java:65)
    org.apache.poi.hssf.record.RecordFactoryInputStream.<init>(RecordFactoryInputStream.java:182)
    org.apache.poi.hssf.eventusermodel.HSSFEventFactory.genericProcessEvents(HSSFEventFactory.java:139)
    org.apache.poi.hssf.eventusermodel.HSSFEventFactory.processEvents(HSSFEventFactory.java:106)
    pl.veracomp.service.SpreadsheetImportService.process(SpreadsheetImportService.java:369)
    pl.veracomp.controller.uploadController.onSubmit(uploadController.java:57)
    org.springframework.web.servlet.mvc.SimpleFormController.processFormSubmission(SimpleFormController.java:272)
    org.springframework.web.servlet.mvc.AbstractFormController.handleRequestInternal(AbstractFormController.java:268)
    org.springframework.web.servlet.mvc.AbstractController.handleRequest(AbstractController.java:153)
    org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(SimpleControllerHandlerAdapter.java:48)
    org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:923)
    org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:852)
    org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:882)
    org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:789)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:647)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
    org.netbeans.modules.web.monitor.server.MonitorFilter.doFilter(MonitorFilter.java:393)

可以在Excel2007中成功打开相同的文件。当我用save as=>Excel 97/2003手动保存它们时，7zip显示book条目已替换为workbook并且我可以用Apache POI成功地导入它们。

有人发现这个问题了吗？如何解决这个问题？

编辑

Answer 1

在您提出问题时，Apache POI没有其他解决方案。好消息是现在有了！

在Apache POI的新版本中，如果使用这些旧文件之一调用HSSFWorkbook或WorkbookFactory，您将得到更有帮助的OldExcelFormatException

如果您想从这些文件中获取一些信息，那么OldExcelExtractor可以从包括Excel95（以及更早的版本）在内的格式中获取文本和数字。