阅读excel 2003文件与apache POI - FileNotFound

Question

我正在编写一些代码来导入Excel文件到数据库。文件可能很大(数千行)，所以我使用Event 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条目。

如果没有找到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)

Google有一些关于固定的 bug在POI 3.2和3.7，这与异常Not enough data (0) to read requested (2) bytes有关，但似乎这是别的东西。

相同的文件可以在Excel 2007中成功打开。当我用Save As=>Excel 97/2003手动保存它们时，7zip显示Book条目已被Workbook取代，我可以成功地用Apache POI导入它们。

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

编辑

问题是当我试图打开保存在Microsoft Excel 5.0/95文件格式的文件。

若要重现此问题，请创建新的电子表格，输入任何数据，然后另存为=> Microsoft Excel 5.0/95 Workbook (*.xls)。

是否有办法阅读这种格式与apache POI或我必须强迫我的用户在上传之前升级他们的工作簿?

Answer 1

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

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

如果你想从这些文件中获得一些信息，那么OldExcelExtractor能够从包括Excel 95(和更老的!)在内的格式中获取文本和数字。

为了支持这一点，也有一些Record类，所以你可以做一些基于事件的解析来更详细地处理它们。没有友好的UserModel支持

Answer 2

这是版本问题:文件是旧版本。要确认这一点，请使用新版本的Excel打开您的文件，修改它，保存它并重试。

Answer 3

更普遍的是，从POI的角度来看，OpenOffice或LibreOffice可能会比MS Office更好地编写旧MS Office格式的文档。当POI无法读取97版本的.xls文件作为HSSFWorkbook时，我解决了这个问题。