Bug 45334 - Excel function "ERRORTYPE" not recognized properly
Summary: Excel function "ERRORTYPE" not recognized properly
Status: RESOLVED FIXED
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 3.0-FINAL
Hardware: All Linux
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2008-07-03 10:32 UTC by Brad Sneade
Modified: 2008-07-03 16:27 UTC (History)
0 users


Attachments
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 Brad Sneade 2008-07-03 10:32:32 UTC 
When encountering a formula that uses the ERRORTYPE the FormulaParser does not recognize it as a function.  Here is the stack trace:

java.lang.IllegalArgumentException: Invalid Formula cell reference: 'ERROR'
	at org.apache.poi.hssf.util.CellReference.<init>(CellReference.java:61)
	at org.apache.poi.hssf.util.AreaReference.<init>(AreaReference.java:63)
	at org.apache.poi.hssf.record.formula.AreaPtgBase.<init>(AreaPtgBase.java:59)
	at org.apache.poi.hssf.record.formula.AreaPtg.<init>(AreaPtg.java:36)
	at org.apache.poi.hssf.model.FormulaParser.parseIdentifier(FormulaParser.java:242)
	at org.apache.poi.hssf.model.FormulaParser.parseFunctionOrIdentifier(FormulaParser.java:229)
	at org.apache.poi.hssf.model.FormulaParser.parseSimpleFactor(FormulaParser.java:468)
	at org.apache.poi.hssf.model.FormulaParser.percentFactor(FormulaParser.java:433)
	at org.apache.poi.hssf.model.FormulaParser.powerFactor(FormulaParser.java:420)
	at org.apache.poi.hssf.model.FormulaParser.Term(FormulaParser.java:628)
	at org.apache.poi.hssf.model.FormulaParser.additiveExpression(FormulaParser.java:709)
	at org.apache.poi.hssf.model.FormulaParser.concatExpression(FormulaParser.java:693)
	at org.apache.poi.hssf.model.FormulaParser.comparisonExpression(FormulaParser.java:650)
	at org.apache.poi.hssf.model.FormulaParser.Arguments(FormulaParser.java:405)
	at org.apache.poi.hssf.model.FormulaParser.function(FormulaParser.java:308)
	at org.apache.poi.hssf.model.FormulaParser.parseFunctionOrIdentifier(FormulaParser.java:227)
	at org.apache.poi.hssf.model.FormulaParser.parseSimpleFactor(FormulaParser.java:468)
	at org.apache.poi.hssf.model.FormulaParser.percentFactor(FormulaParser.java:433)
	at org.apache.poi.hssf.model.FormulaParser.powerFactor(FormulaParser.java:420)
	at org.apache.poi.hssf.model.FormulaParser.Term(FormulaParser.java:628)
	at org.apache.poi.hssf.model.FormulaParser.additiveExpression(FormulaParser.java:709)
	at org.apache.poi.hssf.model.FormulaParser.concatExpression(FormulaParser.java:693)
	at org.apache.poi.hssf.model.FormulaParser.comparisonExpression(FormulaParser.java:650)
	at org.apache.poi.hssf.model.FormulaParser.Arguments(FormulaParser.java:405)
	at org.apache.poi.hssf.model.FormulaParser.function(FormulaParser.java:308)
	at org.apache.poi.hssf.model.FormulaParser.parseFunctionOrIdentifier(FormulaParser.java:227)
	at org.apache.poi.hssf.model.FormulaParser.parseSimpleFactor(FormulaParser.java:468)
	at org.apache.poi.hssf.model.FormulaParser.percentFactor(FormulaParser.java:433)
	at org.apache.poi.hssf.model.FormulaParser.powerFactor(FormulaParser.java:420)
	at org.apache.poi.hssf.model.FormulaParser.Term(FormulaParser.java:628)
	at org.apache.poi.hssf.model.FormulaParser.additiveExpression(FormulaParser.java:709)
	at org.apache.poi.hssf.model.FormulaParser.concatExpression(FormulaParser.java:693)
	at org.apache.poi.hssf.model.FormulaParser.comparisonExpression(FormulaParser.java:650)
	at org.apache.poi.hssf.model.FormulaParser.parse(FormulaParser.java:751)
	at org.apache.poi.hssf.model.FormulaParser.parse(FormulaParser.java:113)
	at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateCell(HSSFFormulaEvaluator.java:346)
	at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.internalEvaluate(HSSFFormulaEvaluator.java:338)
	at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateFormulaCell(HSSFFormulaEvaluator.java:183)
	at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateAllFormulaCells(HSSFFormulaEvaluator.java:278)
	... 

Note: there is no way to select the 3.1 final in the Version select.  The jars I used to get this are poi-*FINAL-20080629.jar
Comment 1 Brad Sneade 2008-07-03 10:38:44 UTC 
Debugging into it I have found the following:

org.apache.poi.hssf.model.FormulaParser.parseFunctionOrIdentifier() calls GetName() and uses the existence of '(' to decide to parse and node or handle a function.  

The ERRORTYPE function is passed around as "ERROR.TYPE(args)".  When GetName() parses this it sets 'look' to '.' instead of '('.  It looks as if ERROR.TYPE comes from functionMetadata.txt.  I've overridden this file (via the classpath) and changed
"261	ERROR.TYPE	1	1	V	V	"
to
"261	ERRORTYPE	1	1	V	V	"
and this seems to fix the problem.
Comment 2 Josh Micich 2008-07-03 16:27:46 UTC 
Fixed in svn r673853 and r673863

Thanks for the research.  Your proposed patch fixes your case because the formula name isn't encoded in the xls file, but it's not quite right.  

This Excel function 'ERROR.TYPE' really *does* have a dot. POI code like cell.setCellFormula("ERROR.TYPE(A1)") also needs to work.

The code was changed to allow dots in identifiers.  Unfortunately, for area refs dots can be used in place of colon, so special logic was needed to distinguish these cases.

Also added an implementation for Errortype.java and junits.

Note - The new logic in Errortype.java is correct, but there are still a few bugs in the way POI evaluates errors in other functions (see TestFormulasFromSpreadsheet:134). In these cases, POI may get the error code of the evaluated argument wrong, in which case ERROR.TYPE() will seem to return the wrong value.