Bug 45041

Summary: Invalid Formula cell reference when creating formula
Product: POI Reporter: Andreas Goetz <cpuidle>
Component: HSSFAssignee: POI Developers List <dev>
Status: RESOLVED FIXED    
Severity: major    
Priority: P2    
Version: 3.0-FINAL   
Target Milestone: ---   
Hardware: PC   
OS: Windows XP   
Bug Depends on: 45060    
Bug Blocks:    

Description Andreas Goetz 2008-05-19 12:57:19 UTC
After filling a couple of rows with 5 columns of data I'm trying to create the following formula that works fine in Excel:

HSSFRow r = sheet.createRow(0);

// create the IRR formula 
short col = 0;
HSSFCell c = r.createCell(col++);
c.setCellType(HSSFCell.CELL_TYPE_FORMULA);
c.setCellFormula("(1+IRR(SUMIF(A:A;ROW(INDIRECT(MIN(A:A)&\":\"&MAX(A:A)));B:B);0))^365-1");

This results in the following inlikely error:

Exception in thread "main" java.lang.IllegalArgumentException: Invalid Formula cell reference: 'A'
	at org.apache.poi.hssf.util.CellReference.<init>(CellReference.java:48)
	at org.apache.poi.hssf.util.AreaReference.<init>(AreaReference.java:43)
	at org.apache.poi.hssf.record.formula.AreaPtg.<init>(AreaPtg.java:55)
	at org.apache.poi.hssf.model.FormulaParser.Ident(FormulaParser.java:290)
	at org.apache.poi.hssf.model.FormulaParser.Factor(FormulaParser.java:522)
	at org.apache.poi.hssf.model.FormulaParser.Term(FormulaParser.java:660)
	at org.apache.poi.hssf.model.FormulaParser.Expression(FormulaParser.java:708)
	at org.apache.poi.hssf.model.FormulaParser.Arguments(FormulaParser.java:486)
	at org.apache.poi.hssf.model.FormulaParser.function(FormulaParser.java:358)
	at org.apache.poi.hssf.model.FormulaParser.Ident(FormulaParser.java:280)
	at org.apache.poi.hssf.model.FormulaParser.Factor(FormulaParser.java:522)
	at org.apache.poi.hssf.model.FormulaParser.Term(FormulaParser.java:660)
	at org.apache.poi.hssf.model.FormulaParser.Expression(FormulaParser.java:708)
	at org.apache.poi.hssf.model.FormulaParser.Arguments(FormulaParser.java:486)
	at org.apache.poi.hssf.model.FormulaParser.function(FormulaParser.java:358)
	at org.apache.poi.hssf.model.FormulaParser.Ident(FormulaParser.java:280)
	at org.apache.poi.hssf.model.FormulaParser.Factor(FormulaParser.java:522)
	at org.apache.poi.hssf.model.FormulaParser.Term(FormulaParser.java:660)
	at org.apache.poi.hssf.model.FormulaParser.Add(FormulaParser.java:674)
	at org.apache.poi.hssf.model.FormulaParser.Expression(FormulaParser.java:710)
	at org.apache.poi.hssf.model.FormulaParser.Factor(FormulaParser.java:518)
	at org.apache.poi.hssf.model.FormulaParser.Term(FormulaParser.java:660)
	at org.apache.poi.hssf.model.FormulaParser.Expression(FormulaParser.java:708)
	at org.apache.poi.hssf.model.FormulaParser.parse(FormulaParser.java:812)
	at org.apache.poi.hssf.usermodel.HSSFCell.setCellFormula(HSSFCell.java:649)

Is there any way this formula could be created from POI?
Comment 1 Andreas Goetz 2008-05-19 13:18:59 UTC
I've verified error message with 3.1b1- still existing but different:

Exception in
thread "main" org.apache.poi.hssf.model.FormulaParser$FormulaParseException: Integer Expected
	at
org.apache.poi.hssf.model.FormulaParser.expected(FormulaParser.java:137)
	at org.apache.poi.hssf.model.FormulaParser.parseNumber(FormulaParser.java:578)
	at
org.apache.poi.hssf.model.FormulaParser.parseSimpleFactor(FormulaParser.java:545)
	at
org.apache.poi.hssf.model.FormulaParser.percentFactor(FormulaParser.java:502)
	at org.apache.poi.hssf.model.FormulaParser.powerFactor(FormulaParser.java:489)
	at
org.apache.poi.hssf.model.FormulaParser.Term(FormulaParser.java:703)
	at org.apache.poi.hssf.model.FormulaParser.additiveExpression(FormulaParser.java:781)
	at
org.apache.poi.hssf.model.FormulaParser.concatExpression(FormulaParser.java:766)
	at
org.apache.poi.hssf.model.FormulaParser.comparisonExpression(FormulaParser.java:723)
	at
org.apache.poi.hssf.model.FormulaParser.Arguments(FormulaParser.java:479)
	at org.apache.poi.hssf.model.FormulaParser.function(FormulaParser.java:326)
	at
org.apache.poi.hssf.model.FormulaParser.parseIdent(FormulaParser.java:236)
	at org.apache.poi.hssf.model.FormulaParser.parseSimpleFactor(FormulaParser.java:542)
	at
org.apache.poi.hssf.model.FormulaParser.percentFactor(FormulaParser.java:502)
	at org.apache.poi.hssf.model.FormulaParser.powerFactor(FormulaParser.java:489)
	at
org.apache.poi.hssf.model.FormulaParser.Term(FormulaParser.java:703)
	at org.apache.poi.hssf.model.FormulaParser.additiveExpression(FormulaParser.java:781)
	at
org.apache.poi.hssf.model.FormulaParser.concatExpression(FormulaParser.java:766)
	at
org.apache.poi.hssf.model.FormulaParser.comparisonExpression(FormulaParser.java:723)
	at
org.apache.poi.hssf.model.FormulaParser.Arguments(FormulaParser.java:479)
	at org.apache.poi.hssf.model.FormulaParser.function(FormulaParser.java:326)
	at
org.apache.poi.hssf.model.FormulaParser.parseIdent(FormulaParser.java:236)
	at org.apache.poi.hssf.model.FormulaParser.parseSimpleFactor(FormulaParser.java:542)
	at
org.apache.poi.hssf.model.FormulaParser.percentFactor(FormulaParser.java:502)
	at org.apache.poi.hssf.model.FormulaParser.powerFactor(FormulaParser.java:489)
	at
org.apache.poi.hssf.model.FormulaParser.Term(FormulaParser.java:703)
	at org.apache.poi.hssf.model.FormulaParser.additiveExpression(FormulaParser.java:787)
	at
org.apache.poi.hssf.model.FormulaParser.concatExpression(FormulaParser.java:766)
	at
org.apache.poi.hssf.model.FormulaParser.comparisonExpression(FormulaParser.java:723)
	at
org.apache.poi.hssf.model.FormulaParser.parseSimpleFactor(FormulaParser.java:532)
	at
org.apache.poi.hssf.model.FormulaParser.percentFactor(FormulaParser.java:502)
	at org.apache.poi.hssf.model.FormulaParser.powerFactor(FormulaParser.java:489)
	at
org.apache.poi.hssf.model.FormulaParser.Term(FormulaParser.java:703)
	at org.apache.poi.hssf.model.FormulaParser.additiveExpression(FormulaParser.java:781)
	at
org.apache.poi.hssf.model.FormulaParser.concatExpression(FormulaParser.java:766)
	at
org.apache.poi.hssf.model.FormulaParser.comparisonExpression(FormulaParser.java:723)
	at
org.apache.poi.hssf.model.FormulaParser.parse(FormulaParser.java:820)
	at org.apache.poi.hssf.usermodel.HSSFCell.setCellFormula(HSSFCell.java:641)

Comment 2 Andreas Goetz 2008-05-19 13:21:30 UTC
Based on the 3.1b1 error message I've also investigated if POI was potentially confused
by the A:A column notation and changed the formula like this, unfortunately still with
the same error message:
(1+IRR(SUMIF(A1:A100;ROW(INDIRECT(MIN(A1:A100)&\":\"&MAX(A1:A100)));B1:B100);0))^365-1

I
could find if this should work- may be the "Everything else" under unsupported features
on the POI website includes my case :(

Comment 3 Josh Micich 2008-05-19 16:16:32 UTC
The problem appears to be with the use of semicolons (;) for argument separators when POI expects commas (,).  My understanding was that Excel uses commas too.  Is there any circumstance that allows semicolon?

If I change the semicolons to commas, POI parses the formula OK.  

I have added a patch (in svn r658033) to improve the formula parse error messages.  The first code example in this bugzilla should now produce this:

Exception in thread "main" org.apache.poi.hssf.model.FormulaParser$FormulaParseException: Parse error near char 16';' in specified formula '(1+IRR(SUMIF(A:A;ROW(INDIRECT(MIN(A:A)&":"&MAX(A:A)));B:B);0))^365-1'. Expected Integer
	at org.apache.poi.hssf.model.FormulaParser.expected(FormulaParser.java:140)
	at ...


BTW - The full column references should not be a problem for this example.  A partial fix for bug 44410 was applied in svn r628044 (rel 3.1-beta1), and this seems to be enough to make expressions like 'A:A' work.
Comment 4 Andreas Goetz 2008-05-20 07:38:56 UTC
Can confirm ; was the problem, thank you. My mistake- copied formula our of English Excel
on English XP with German local settings... 
Should the error message potentially say
"Expected integer or ;"?
Comment 5 Andreas Goetz 2008-05-20 08:32:13 UTC
Update- there is a second bug lurking behind the first one. Let me know if I should open
separately:

If you enter two sample lines of data into columns a and b like this:
01.01.2007	100,00
01.01.2008	-250

In
Excel however I see either #VALUE! or #DIV/0! in the formula field. Entering the field,
appending a " " character and OKing it fixes the problem and field displays a value. It
seems Excel is correcting the formula in the background- visually it does not change by
this process.
Comment 6 Josh Micich 2008-05-27 23:35:04 UTC
Bug 45060 was opened to deal with the additional issue mentioned in comment #5.  As it turns out after fixing that bug, there is nothing else stopping the example in this bugzilla from working.