I was afraid of that. Well, thanks for the link. I'll get on it. I found a bug. Cool.
Thanks, Dave -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Eric Peters Sent: Tuesday, February 04, 2014 2:52 PM To: POI Users List Subject: Re: End of Line character in formula throws exception in XSSFFormulaEvaluator.evaluate() 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 > > > --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
