https://issues.apache.org/bugzilla/show_bug.cgi?id=56106
Bug ID: 56106
Summary: End of Line character in formula throws exception in
XSSFFormulaEvaluator.evaluate()
Product: POI
Version: 3.9
Hardware: PC
Status: NEW
Severity: normal
Priority: P2
Component: XSSF
Assignee: [email protected]
Reporter: [email protected]
Created attachment 31282
--> https://issues.apache.org/bugzilla/attachment.cgi?id=31282&action=edit
this file has lots of hidden line breaks.
Our spreadsheets have long formulas in them, so I spent some effort formatting
them to be able to make sense out of them. I used Shift+Enter to produce the
soft line break. See below for an example:
IF(
INDEX('TRB Record'!C$2:C$61,ROW()*2-5,,1)="",
"",
INDEX('TRB Record'!C$2:C$61,ROW()*2-5,,1)
)
Microsoft Excel handles the line breaks without any trouble, and they are easy
to read and debug. (Okay, I'm uptight.) But hidden in these formulae are the
end of line characters ['\n'].
And unfortunately, the POI formula parser breaks when it reaches one of these
special EOL characters. I'm using Eclipse, and I can see the string array
reporting the offending EOLs. Here's the text of the Exception (with Eclipse
using the formatting when it reports):
org.apache.poi.ss.formula.FormulaParseException: Parse error near char 3 '
' in specified formula 'IF(
INDEX('TRB Record'!C$2:C$61,ROW()*2-5,,1)="",
"",
INDEX('TRB Record'!C$2:C$61,ROW()*2-5,,1)
)'. Expected cell ref or constant literal
Here's a code snippet that produces the Exception:
XSSFSheet sheet = workbook.getSheet(worksheet);
XSSFFormulaEvaluator evaluator =
workbook.getCreationHelper().createFormulaEvaluator();
...
int ctype = 0;
Cell cell;
...
Row row = sheet.getRow(rowCtr);//rows.next();
...
cell =
row.getCell(col,Row.RETURN_BLANK_AS_NULL);
...
ctype =
evaluator.evaluateFormulaCell(cell);
My planned work-around for this is to wrap the call in a function that strips
out the offending characters, builds a temporary cell with the new formula
string, and return it. Then I'll be able to run the evaluate() function
against it for processing.
--
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]
