[Bug 55413] New: XSSFCell.setCellFormula(String) Formula Parsing Error

[bu...@apache.org]

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: dev@poi.apache.org
          Reporter: sellison@dig-inc.net

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: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org

[Bug 55413] XSSFCell.setCellFormula(String) Formula Parsing Error

[bu...@apache.org]

https://issues.apache.org/bugzilla/show_bug.cgi?id=55413

Seth Ellison <se...@dig-inc.net> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 CC|                            |sellison@dig-inc.net
                 OS|                            |All

-- 
You are receiving this mail because:
You are the assignee for the bug.

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org