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.
While I think that I can make this work, it would be good to know if anyone has
seen this problem before. I don't see a bug report on it, but I don't want to
be a bother.
Does anyone have a better solution?
Thanks,
David Crocker
Scientist
National Renewable Energy Laboratory
Integrated Biorefinery Research Facilities
MS 3512
16173 Denver West Parkway
Golden, CO 80401
