I'd submit a bug report via https://issues.apache.org/bugzilla/describecomponents.cgi?product=POI
& make sure to include a simple test file -Eric On Tue, Feb 4, 2014 at 1:37 PM, Crocker, David <[email protected]>wrote: > 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 > > >
