Bug 55413 - XSSFCell.setCellFormula(String) Formula Parsing Error
Summary: XSSFCell.setCellFormula(String) Formula Parsing Error
Status: RESOLVED WORKSFORME
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 3.9-FINAL
Hardware: PC All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on: 57840
Blocks:
  Show dependency tree
 
Reported: 2013-08-13 20:29 UTC by Seth Ellison
Modified: 2016-06-17 07:49 UTC (History)
1 user (show)


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 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.