POI should have no problems reading files containing formulas

Could you please upload one of your problem files, and a code snippet that's currently failing for this file? We'll also need to know what version of excel generated the file.

Created attachment 21640 [details]
File which fails to be read with POI

This is the file which fails when read with POI 3.1
But if the same file is opened and saved in Open office and then fed to POI , then it read fine.

WARN. Unread 77 bytes of record 0x18
WARN. Unread 103 bytes of record 0x18
WARN. Unread 103 bytes of record 0x18
WARN. Unread 103 bytes of record 0x18
WARN. Unread 104 bytes of record 0x18
WARN. Unread 77 bytes of record 0x18
WARN. Unread 103 bytes of record 0x18
WARN. Unread 103 bytes of record 0x18
WARN. Unread 7 bytes of record 0x1c1
WARN. Unread 6589 bytes of record 0xeb
WARN. Unread 8223 bytes of record 0xfc
WARN. Unread 7986 bytes of record 0x3c
Cannot create Workbook so reject this file.
java.lang.IndexOutOfBoundsException: Index: 571, Size: 0
        at java.util.ArrayList.RangeCheck(ArrayList.java:546)
        at java.util.ArrayList.get(ArrayList.java:321)
        at org.apache.poi.util.IntMapper.get(IntMapper.java:81)
        at org.apache.poi.hssf.record.SSTRecord.getString(SSTRecord.java:195)
        at org.apache.poi.hssf.model.Workbook.getSSTString(Workbook.java:729)
        at org.apache.poi.hssf.usermodel.HSSFRichTextString.<init>(HSSFRichTextString.java:57)
        at org.apache.poi.hssf.usermodel.HSSFCell.<init>(HSSFCell.java:202)
        at org.apache.poi.hssf.usermodel.HSSFRow.createCellFromRecord(HSSFRow.java:193)
        at org.apache.poi.hssf.usermodel.HSSFSheet.setPropertiesFromSheet(HSSFSheet.java:177)
        at org.apache.poi.hssf.usermodel.HSSFSheet.<init>(HSSFSheet.java:131)
        at org.apache.poi.hssf.usermodel.HSSFWorkbook.<init>(HSSFWorkbook.java:226)
        at org.apache.poi.hssf.usermodel.HSSFWorkbook.<init>(HSSFWorkbook.java:148)
        at com.ch2m.gsri.wizards.ExcelSheetBean.getExcelSheetNames(ExcelSheetBean.java:37)
        at org.apache.jsp.validate_jsp._jspService(validate_jsp.java:255)
        at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:98)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
        at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:328)
        at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:315)
        at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:265)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:269)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:210)
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:174)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:117)
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:108)
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:151)
        at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:870)
        at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:665)
        at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:528)
        at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:81)
        at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:685)
        at java.lang.Thread.run(Thread.java:595)

The errors tend to indicate a very messed up file. Where did it come from?

This file is frm our customers.
What special thing does Open office does, which makes sense to POI, any idea?

OpenOffice may be more tollerant of borderline corrupt files than POI is

Unless we know how the file was created, and that it's really valid to be in that messed-up a state, it's unlikely that we'll hack poi with workarounds for a file this broken

Please re-open the bug if you're able to confirm this file really came from excel, and hasn't been corrupted since then

Microsoft Excel 97 - Excel 2003 & 5.0/95 Workbook 

Microsoft Office Excel Workbook.

These are the two forms in which the file could be saved as.
It is really valid to be in that messed up state.


Another thing is, if the same file (sheet) is copied and do a paste special with values only option then also even if it is not touched by open office POI reads is fine.

our apologies, but please let us know what best can u do for us.

You still haven't said how the file was created. What version of excel? Is this problem reproducable with other files, or is it just this one file?

The file was created using MS Office Excel 2003 and is reproducable in all other files which contains the formulae  e.g. - Cell: H22 in PAF sheet.

I opened the attached file using Excel 2004 for Mac.

It contains macros, so I clicked to open the file with Macros disabled. Excel then reported that the file contains Excel 4.0 Macros which cannot be disabled.

I don't think that POI supports the Excel file format from before Excel 5.0.

This could be the problem. What happens if you remove the macros?

I deleted all the macros for this file and saved it and then fed to POI but again the same error. Problem persists even if i remove all the macros.

No promises about progress, but would you attach the "macro-less" file as well?

Created attachment 21642 [details]
Macro less file

Hi,
sending you the same file after deleting the macros.

There are still macros on this sheet. This is easy to see. Tighten up you macro security so that it asks if you want to enable.

I believe remaining macros are on your "Site Details" worksheet attached to two buttons.

It looks like this work order template has had a long history and I think it is likely that there are old records within. You might try the BiffReader and look at the records that are unrecognized to see if they are described in any of the newly released documentation.

I didnot understand the Biff Reader part. can u please elaborate.

If i uncomment the code for unknownrecord(records.add(record)) then the Excel file is read by POI but fails to evaluate formula and to retrieve strings from the sheet.

in RecordFactory.java
----------------------
if (lastRecord instanceof UnknownRecord) {
    //Gracefully handle records that we dont know about,
   //that happen to be continued
     records.add(record);
} 
else {
  // throw new RecordFormatException("Unhandled Continue Record");
}

But if i again comment it out ie.
//records.add(record)

then POI fails to read my excel file.

I'm sorry I should have written BiffViewer.

See http://poi.apache.org/hssf/how-to.html and look for org.apache.poi.hssf.dev.BiffViewer

This should help you see those records and what is in them. It does sound like they are some kind of embedded macro in an older form. Maybe the POI developers have never seen these before. Only now has Microsoft provided documentation.

Good luck.

Re-tested OK in 3.1 and svn trunk (r683126)

Both attachment (id=21640) and attachment (id=21642) can be read OK by POI.

Some things are not quite right though.  Comment #2 mentions POI version '3.1' but in March, only version 3.0.2 was available.  Perhaps '3.0.1' was intended.  I tried both attachments against various earlier versions (3.0, 3.0.1, 3.0.2) of POI, and none of them fail with the same error described in comment #3.  It seems that the most common error was "Unknown grbit '16'".  This seems to be related to bug 42564 which was fixed in May.  

If I've made a mistake, please specify the exact POI version and attachment that produce the error.