Bug 49783 - [PATCH] Error Code for "#REF!" same as for "#NAME?"
Summary: [PATCH] Error Code for "#REF!" same as for "#NAME?"
Status: RESOLVED FIXED
Alias: None
Product: POI
Classification: Unclassified
Component: POI Overall (show other bugs)
Version: 3.6-dev
Hardware: PC Windows XP
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2010-08-19 08:35 UTC by alex
Modified: 2010-08-25 09:31 UTC (History)
0 users


Attachments
Updates error code from 0x1D to 0x17 for REF value (423 bytes, patch)
2010-08-19 08:35 UTC, alex 		Details | Diff
Example file and code (6.98 KB, application/x-zip-compressed)
2010-08-20 01:43 UTC, alex 		Details
View All Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this bug.
Description alex 2010-08-19 08:35:40 UTC 
Created attachment 25914 [details]
Updates error code from 0x1D to 0x17 for REF value

In org.apache.poi.ss.usermodel.FormulaError, the REF and NAME values have the same error code (0x1D). This results in an "IllegalArgumentException" when running forString()/forInt(). REF should instead have code 0x17 as it is also defined in other places (e.g. org.apache.poi.ss.usermodel.ErrorConstants)

To reproduce this: 
- make an excel file (tested with OOXML)
- make a reference to a cell from another cell
- delete the first cell
- run the second cell through "org.apache.poi.ss.usermodel.FormulaEvaluator.evaluateInCell()". 

The "IllegalArgumentException" will state "Unknown error type: 23".
Comment 1 Yegor Kozlov 2010-08-19 13:48:09 UTC 
I can't reproduce the problem. I created two excel files as you described, one binary and the other OOXML. 

The binary .xls file evaluates OK and the correct error value is set. 

The OOXML workbook evaluates with exception, but with a different one:

org.apache.poi.ss.formula.FormulaParseException: Cell reference expected after sheet name at index 8.
	at org.apache.poi.ss.formula.FormulaParser.parseRangeable(FormulaParser.java:420)
	at org.apache.poi.ss.formula.FormulaParser.parseRangeExpression(FormulaParser.java:266)
	at org.apache.poi.ss.formula.FormulaParser.parseSimpleFactor(FormulaParser.java:1113)

Can you upload a sample workbook that exhibits the problem ? It would be helpful to properly diagnose the problem and also write a unit test against a real file. 

Yegor
Comment 2 alex 2010-08-20 01:43:34 UTC 
Created attachment 25918 [details]
Example file and code

Running this throws the following exception:

Exception in thread "main" java.lang.IllegalArgumentException: Unknown error type: 23
	at org.apache.poi.ss.usermodel.FormulaError.forInt(FormulaError.java:131)
	at org.apache.poi.xssf.usermodel.XSSFCell.setCellErrorValue(XSSFCell.java:611)
	at org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator.setCellValue(XSSFFormulaEvaluator.java:203)
	at org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator.evaluateInCell(XSSFFormulaEvaluator.java:175)
	at org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator.evaluateInCell(XSSFFormulaEvaluator.java:46)
	at Example.main(Example.java:20)
Comment 3 alex 2010-08-20 01:44:52 UTC 
Please note that I forgot to tag the first attachment as a "patch", but it is indeed a patch.
Comment 4 Yegor Kozlov 2010-08-25 09:31:09 UTC 
Fixed in r989100, junit added.

Thanks,
Yegor