https://issues.apache.org/bugzilla/show_bug.cgi?id=55413
Bug ID: 55413
Summary: XSSFCell.setCellFormula(String) Formula Parsing Error
Product: POI
Version: 3.9
Hardware: PC
Status: NEW
Severity: normal
Priority: P2
Component: XSSF
Assignee: [email protected]
Reporter: [email protected]
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.
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
