poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Eric Peters <E...@Peters.org>
Subject Re: End of Line character in formula throws exception in XSSFFormulaEvaluator.evaluate()
Date Tue, 04 Feb 2014 21:51:51 GMT
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 <David.Crocker@nrel.gov>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
>
>
>

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message