Bug 55413

Summary: XSSFCell.setCellFormula(String) Formula Parsing Error
Product: POI Reporter: Seth Ellison <sellison>
Component: XSSFAssignee: POI Developers List <dev>
Status: RESOLVED WORKSFORME    
Severity: normal CC: sellison
Priority: P2    
Version: 3.9-FINAL   
Target Milestone: ---   
Hardware: PC   
OS: All   
Bug Depends on: 57840    
Bug Blocks:    

Description Seth Ellison 2013-08-13 20:29:09 UTC
The formula comes from an .xlsm file, brought out via:

cell.getCellFormula();

The original formula in Excel is:
=IFERROR(IF([@Status]="C",ABS([@[Code: Lag]]),ABS([@[Code: Aging]])),"Data Error")

The formula I get back is:

IFERROR(IF(DCASdata[[#This Row],[Status]]="C",ABS(DCASdata[[#This Row],[Code: Lag]]),ABS(DCASdata[[#This Row],[Code: Aging]])),"Data Error")

Code:

In a loop.
...
XSSFCell testCell = testRow.createCell(i);
testCell.setCellType(2); // Formula Type (ENUM, I know, just testing)
testCell.setCellFormula(formula);
...


This formula is one among many which XSSFCell.setCellFormula(formula) chokes on. The parser spits out this error:

Caused by: org.apache.poi.ss.formula.FormulaParseException: Parse error near char 19 '[' in specified formula 'IFERROR(IF(DCASdata[[#This Row],[Status]]="C",ABS(DCASdata[[#This Row],[Code: Lag]]),ABS(DCASdata[[#This Row],[Code: Aging]])),"Data Error")'. Expected ',' or ')'
	at org.apache.poi.ss.formula.FormulaParser.expected(FormulaParser.java:219)
	at org.apache.poi.ss.formula.FormulaParser.Arguments(FormulaParser.java:1056)
	at org.apache.poi.ss.formula.FormulaParser.function(FormulaParser.java:936)
	at org.apache.poi.ss.formula.FormulaParser.parseNonRange(FormulaParser.java:558)
	at org.apache.poi.ss.formula.FormulaParser.parseRangeable(FormulaParser.java:517)
	at org.apache.poi.ss.formula.FormulaParser.parseRangeExpression(FormulaParser.java:268)
	at org.apache.poi.ss.formula.FormulaParser.parseSimpleFactor(FormulaParser.java:1119)
	at org.apache.poi.ss.formula.FormulaParser.percentFactor(FormulaParser.java:1079)
	at org.apache.poi.ss.formula.FormulaParser.powerFactor(FormulaParser.java:1066)
	at org.apache.poi.ss.formula.FormulaParser.Term(FormulaParser.java:1426)
	at org.apache.poi.ss.formula.FormulaParser.additiveExpression(FormulaParser.java:1526)
	at org.apache.poi.ss.formula.FormulaParser.concatExpression(FormulaParser.java:1510)
	at org.apache.poi.ss.formula.FormulaParser.comparisonExpression(FormulaParser.java:1467)
	at org.apache.poi.ss.formula.FormulaParser.Arguments(FormulaParser.java:1051)
	at org.apache.poi.ss.formula.FormulaParser.function(FormulaParser.java:936)
	at org.apache.poi.ss.formula.FormulaParser.parseNonRange(FormulaParser.java:558)
	at org.apache.poi.ss.formula.FormulaParser.parseRangeable(FormulaParser.java:429)
	at org.apache.poi.ss.formula.FormulaParser.parseRangeExpression(FormulaParser.java:268)
	at org.apache.poi.ss.formula.FormulaParser.parseSimpleFactor(FormulaParser.java:1119)
	at org.apache.poi.ss.formula.FormulaParser.percentFactor(FormulaParser.java:1079)
	at org.apache.poi.ss.formula.FormulaParser.powerFactor(FormulaParser.java:1066)
	at org.apache.poi.ss.formula.FormulaParser.Term(FormulaParser.java:1426)
	at org.apache.poi.ss.formula.FormulaParser.additiveExpression(FormulaParser.java:1526)
	at org.apache.poi.ss.formula.FormulaParser.concatExpression(FormulaParser.java:1510)
	at org.apache.poi.ss.formula.FormulaParser.comparisonExpression(FormulaParser.java:1467)
	at org.apache.poi.ss.formula.FormulaParser.unionExpression(FormulaParser.java:1447)
	at org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:1568)
	at org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:176)
	at org.apache.poi.xssf.usermodel.XSSFCell.setFormula(XSSFCell.java:439)
	at org.apache.poi.xssf.usermodel.XSSFCell.setCellFormula(XSSFCell.java:419)

My workaround:

In a loop.
...
XSSFCell testCell = testRow.createCell(i);
testCell.setCellType(2); // Formula Type
		        	
CTCell c = testCell.getCTCell();
CTCellFormula x = c.getF();
x.setStringValue(formulas.get(new Integer(i))); // map of formulas <col,formula>
c.setF(x);
...

And this works perfectly, no errors, and the formulas work perfectly when the workbook is opened in Excel.

It seems to me that XSSFCell's setCellFormula(String) ought to behave as my workaround does.
Comment 1 Dominik Stadler 2015-09-13 12:37:15 UTC
I tried to reproduce this, but could not, I did get some different errors that likely are because my sheet looks misses some setup-data, can you please re-check this with the latest version of poi, e.g. 3.13-beta1 and also provide a full code-example including any template-sheet if possible, e.g. as self-sufficient unit-test that we can add to the test-suite to verify this and keep it fixed in the future?
Comment 2 Javen O'Neal 2016-06-17 07:49:53 UTC
Evaluation of structured references (data tables) were not supported until a fix for bug 57840 was implemented.

This issue has probably been resolved in the latest builds (pre-3.15 beta 2) if it wasn't fixed by 3.13-beta1 (per Dominik's testing).

Without a test file, I cannot write a unit test to verify if this is fixed or ensure that it stays fixed in the future.

Closing as WorksForMe. Please reopen with an Excel file with a Data Table called "DCASdata", columns titled "Status", "Code: Lag", and "Code: Aging" if you are able.