Bug 51875

Summary: [PATCH] Newline characters in shared formulas for XLSX sheets results in FormulaParseException
Product: POI Reporter: Marcel May <marcel.may.de>
Component: XSSFAssignee: POI Developers List <dev>
Status: RESOLVED FIXED    
Severity: normal CC: marcel.may.de
Priority: P2    
Version: 3.8-dev   
Target Milestone: ---   
Hardware: PC   
OS: All   
Attachments: Updated test-data/spreadsheet/NewlineInFormulas.xlsx containing shared formula with newline to trigger exception
Fixes FormularParser, extends the TestXSSFBugs test case, and reverts the previous and obsolete workaround from #50440

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.