Bug 45334

Summary: Excel function "ERRORTYPE" not recognized properly
Product: POI Reporter: Brad Sneade <bsneade+apache>
Component: HSSFAssignee: POI Developers List <dev>
Status: RESOLVED FIXED    
Severity: normal    
Priority: P2    
Version: 3.0-FINAL   
Target Milestone: ---   
Hardware: All   
OS: Linux   

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.