You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by "Crocker, David" <Da...@nrel.gov> on 2014/02/04 22:37:37 UTC
End of Line character in formula throws exception in
XSSFFormulaEvaluator.evaluate()
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
RE: End of Line character in formula throws exception in
XSSFFormulaEvaluator.evaluate()
Posted by "Crocker, David" <Da...@nrel.gov>.
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: ericpeters@gmail.com [mailto:ericpeters@gmail.com] 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 <Da...@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
>
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: End of Line character in formula throws exception in XSSFFormulaEvaluator.evaluate()
Posted by Eric Peters <Er...@Peters.org>.
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 <Da...@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
>
>
>