Bug 50440

Summary: Newline characters in all formulas results in FormulaParseException
Product: POI Reporter: Tommy Y Li <tommy.li>
Component: XSSFAssignee: POI Developers List <dev>
Status: RESOLVED FIXED    
Severity: major    
Priority: P2    
Version: 3.7-FINAL   
Target Milestone: ---   
Hardware: Macintosh   
OS: All   
Attachments: Test spreadsheet for newline in formula error
Groovy test harness for newline in formula error

Description Tommy Y Li 2010-12-09 02:35:33 UTC
The following snippet of code will fail:

FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
evaluator.evaluate(source)

workbook is of type org.apache.poi.ss.usermodel.Workbook
source is of type org.apache.poi.ss.usermodel.Cell with the following formula:
=SUM(1+
2)

Here's the exception:
Caught: org.apache.poi.ss.formula.FormulaParseException: Parse error near char 6 '
' in specified formula 'SUM(1+
2)'. Expected cell ref or constant literal
Comment 1 Nick Burch 2010-12-09 05:34:41 UTC
Are newlines really valid characters within the formula though?

Are you creating the formula with the newline it in through POI or excel?
Comment 2 Tommy Y Li 2010-12-09 17:46:37 UTC
Created attachment 26388 [details]
Test spreadsheet for newline in formula error
Comment 3 Tommy Y Li 2010-12-09 17:48:24 UTC
Created attachment 26389 [details]
Groovy test harness for newline in formula error
Comment 4 Tommy Y Li 2010-12-09 17:48:52 UTC
(In reply to comment #1)
> Are newlines really valid characters within the formula though?
> 
> Are you creating the formula with the newline it in through POI or excel?

New lines (created using alt+enter in formula bar on Windows Excel 2010 OR command+option+return in Mac Office 2008) are valid, i.e. Excel correctly calculates the formula.

I created the formula using Excel and used Apache POI to read the workbook.

I attached a test harness in Groovy and a spreadsheet (xlsx) that has new line chars in the formula.

Cheers,

Tommy.
Comment 5 Nick Burch 2010-12-13 00:35:08 UTC
It looks like you can only have the newline in .xlsx files - when I save as a .xls and re-open then the newline has gone

If you can create a .xls file with the newlines, please upload it

Otherwise, I'm going to work on the basis that this is a .xlsx only feature and try to fix it as that.
Comment 6 Nick Burch 2010-12-13 00:40:49 UTC
On the basis that this is a XSSF (.xlsx) only feature), then the newlines can't be turned into Ptgs so we don't need them when doing the Ptg parsing. As such, fixed in r1045021 by stripping them out during the XSSF Ptg step.
Comment 7 Tommy Y Li 2010-12-13 01:13:09 UTC
(In reply to comment #6)
> On the basis that this is a XSSF (.xlsx) only feature), then the newlines can't
> be turned into Ptgs so we don't need them when doing the Ptg parsing. As such,
> fixed in r1045021 by stripping them out during the XSSF Ptg step.

Awesome, that was real quick.

I should've mentioned that this problem happens in XSSF only, I couldn't replicate the problem in HSSF.

So when is the next stable release (assume it's 3.8) coming?  Where can I download the nightly builds?