Bug 49783

Summary: [PATCH] Error Code for "#REF!" same as for "#NAME?"
Product: POI Reporter: alex <i8c.alex>
Component: POI OverallAssignee: POI Developers List <dev>
Severity: normal    
Priority: P2    
Version: 3.6-dev   
Target Milestone: ---   
Hardware: PC   
OS: Windows XP   
Attachments: Updates error code from 0x1D to 0x17 for REF value
Example file and code

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. 

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.