Bug 56737

Summary: FormulaParseException for sheet or workbook scoped named ranges when evaluating xlsx formulas
Product: POI Reporter: Nick Burch <apache>
Component: XSSFAssignee: POI Developers List <dev>
Status: RESOLVED FIXED    
Severity: normal    
Priority: P2    
Version: 3.11-dev   
Target Milestone: ---   
Hardware: PC   
OS: Linux   
Attachments: Test Excel file with the described formulas and named ranges

Description Nick Burch 2014-07-17 15:40:22 UTC 
Consider a .xlsx workbook with two sheets. Sheet two "Defines" has a named range "NR_To_A1" on it, while there's a workbook scoped named range "NR_Global_B2". The first sheet has formulas which use it

For a sheet scoped named range reference, with a formula like 

=Defines!NR_To_A1

Evaluation will fail with org.apache.poi.ss.formula.FormulaParseException: Cell reference expected after sheet name at index 9.

For a workbook scoped named range reference, with a formula like 

=TestFile.xlsx!NR_Global_B2

Evaluation will fail with org.apache.poi.ss.formula.FormulaParseException: Parse error near char 0 '[' in specified formula '[0]!NR_Global_B2'. Expected number, string, or defined name
Comment 1 Nick Burch 2014-07-17 15:42:17 UTC 
Created attachment 31826 [details]
Test Excel file with the described formulas and named ranges
Comment 2 Nick Burch 2014-07-17 15:46:42 UTC 
Note that this is different to with .xls, where you get a different error "java.lang.RuntimeException: Unexpected eval class (org.apache.poi.ss.formula.eval.NameXEval)" when evaluating
Comment 3 Nick Burch 2014-07-17 17:49:59 UTC 
Disabled, failing unit tests added in r1611400.
Comment 4 Nick Burch 2014-07-20 19:24:58 UTC 
As of r1612156, this file can now be evaluated without error