Bug 40786 - #N/D Excel error in cell Forumula
Summary: #N/D Excel error in cell Forumula
Status: RESOLVED FIXED
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: unspecified
Hardware: PC Windows XP
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2006-10-18 04:00 UTC by Simone
Modified: 2008-05-10 17:35 UTC (History)
0 users



Attachments
EXCEL FILE WITH BUG CELL (13.50 KB, application/excel)
2006-10-19 02:53 UTC, Simone
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Simone 2006-10-18 04:00:04 UTC
When I try to read some Excel file with a cell that contains a formula with the
#N/D word, HSSFWorkbook can't istanciates the workbook...Is there any
work-around solution for this kind of problem? I want to ignore this type of
error and continue to work with other cells... Tnx for support!
This is the stacktrace:

java.lang.reflect.InvocationTargetException
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
	at java.lang.reflect.Constructor.newInstance(Unknown Source)
	at org.apache.poi.hssf.record.RecordFactory.createRecord(RecordFactory.java:213)
	at org.apache.poi.hssf.record.RecordFactory.createRecords(RecordFactory.java:149)
	at org.apache.poi.hssf.usermodel.HSSFWorkbook.<init>(HSSFWorkbook.java:183)
	at org.apache.poi.hssf.usermodel.HSSFWorkbook.<init>(HSSFWorkbook.java:230)
	at org.apache.poi.hssf.usermodel.HSSFWorkbook.<init>(HSSFWorkbook.java:211)
Comment 1 Simone 2006-10-19 02:51:17 UTC
The problem is in createRecord(RecordInputStream in) function of RecordFactory
Class (package org.apache.hssf.record)
When the flow encounter the cell with formula that contains #N/D string,
Constructor returns sid=0x06 that corresponds to FormulaRecord object.
FormulaRecord fillFields(RecordInputStream in) method try to parse the formula with:
field_8_parsed_expr = Ptg.createParsedExpressionTokens(field_7_expression_len, in);

Ptg.createPtg was called and in some case of the switch was generated an
exception, I haven't verified what id was detected...I'll try to work again in
this days..

(In reply to comment #0)
> When I try to read some Excel file with a cell that contains a formula with the
> #N/D word, HSSFWorkbook can't istanciates the workbook...Is there any
> work-around solution for this kind of problem? I want to ignore this type of
> error and continue to work with other cells... Tnx for support!
> This is the stacktrace:
> 
> java.lang.reflect.InvocationTargetException
> 	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
> 	at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
> 	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
> 	at java.lang.reflect.Constructor.newInstance(Unknown Source)
> 	at org.apache.poi.hssf.record.RecordFactory.createRecord(RecordFactory.java:213)
> 	at org.apache.poi.hssf.record.RecordFactory.createRecords(RecordFactory.java:149)
> 	at org.apache.poi.hssf.usermodel.HSSFWorkbook.<init>(HSSFWorkbook.java:183)
> 	at org.apache.poi.hssf.usermodel.HSSFWorkbook.<init>(HSSFWorkbook.java:230)
> 	at org.apache.poi.hssf.usermodel.HSSFWorkbook.<init>(HSSFWorkbook.java:211)

Comment 2 Simone 2006-10-19 02:53:33 UTC
Created attachment 19029 [details]
EXCEL FILE WITH BUG CELL

look at the red cell...
Comment 3 Josh Micich 2008-05-10 17:35:45 UTC
Retested OK in 3.1-beta1

This bug must have been fixed between version 3.0 and 3.0.2.

Unfortunately the stack trace was truncated.  We actually needed to see the exception inside the ITE.  It also would have been nice to know the POI version which had this bug.  I couldn't get a similar error out of a few recent versions of POI.
I also made sure that Excel can open the spreadsheet that POI re-writes.

For recent versions (svn trunk, 3.1-beta1, 3.0.2) POI reads and writes the example sheet silently.  Excel opens it OK.

In version 3.0, POI stderr: "WARN. Unread 4 bytes of record 0x6".  Excel does not open the output file properly.

In version 2.5.1, POI stderr "POI:[WARNING] Unknown Ptg 1c (28) at cell (3,1)", but Excel seems to open OK