Bug 51875 - [PATCH] Newline characters in shared formulas for XLSX sheets results in FormulaParseException
Summary: [PATCH] Newline characters in shared formulas for XLSX sheets results in Form...
Status: RESOLVED FIXED
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 3.8-dev
Hardware: PC All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2011-09-22 20:24 UTC by Marcel May
Modified: 2011-12-05 00:52 UTC (History)
1 user (show)



Attachments
Updated test-data/spreadsheet/NewlineInFormulas.xlsx containing shared formula with newline to trigger exception (8.73 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2011-09-22 20:24 UTC, Marcel May
Details
Fixes FormularParser, extends the TestXSSFBugs test case, and reverts the previous and obsolete workaround from #50440 (3.61 KB, patch)
2011-09-22 20:32 UTC, Marcel May
Details | Diff

Note You need to log in before you can comment on or make changes to this bug.
Description Marcel May 2011-09-22 20:24:16 UTC
Created attachment 27570 [details]
Updated test-data/spreadsheet/NewlineInFormulas.xlsx containing shared formula with newline to trigger exception

When a shared formular in a XLSM sheet contains a newline ('\r\n') character POI throws an exception:

org.apache.poi.ss.formula.FormulaParseException: Unused input [
+B2] after attempting to parse the formula [B1
+B2]
    at org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:1571)
    at org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:174)
    at org.apache.poi.xssf.usermodel.XSSFCell.convertSharedFormula(XSSFCell.java:396)
    at org.apache.poi.xssf.usermodel.XSSFCell.getCellFormula(XSSFCell.java:368)
    at org.apache.poi.xssf.usermodel.XSSFEvaluationWorkbook.getFormulaTokens(XSSFEvaluationWorkbook.java:150)
    at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:281)
    at org.apache.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:225)
    at org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator.evaluateFormulaCellValue(XSSFFormulaEvaluator.java:264)
    at org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator.evaluateFormulaCell(XSSFFormulaEvaluator.java:151)
    at org.apache.poi.xssf.usermodel.TestXSSFBugs.test50440(TestXSSFBugs.java:637)
   ...

My proposed fix modifies the formula parser to treat newlines like whitespaces
(which get skipped and therefore will prevent the parser to 'throw up' when encountering newlines).

This makes a previous workaround obsolete (see https://issues.apache.org/bugzilla/show_bug.cgi?id=50440).

Patch will follow.

Cheers,
Marcel
Comment 1 Marcel May 2011-09-22 20:32:51 UTC
Created attachment 27571 [details]
Fixes FormularParser, extends the TestXSSFBugs test case, and reverts the previous and obsolete workaround from #50440
Comment 2 Nick Burch 2011-12-05 00:52:48 UTC
Thanks for this, patch applied in r1210299.