Bug 47048

Summary: HSSF is unable to evaulate named ranges with the 'complex' flag set
Product: POI Reporter: Kiran Sireesh <kiransireesh>
Component: HSSFAssignee: POI Developers List <dev>
Severity: major    
Priority: P2    
Version: 3.2-FINAL   
Target Milestone: ---   
Hardware: PC   
OS: Windows XP   
Attachments: workbook containing the scenario described

Description Kiran Sireesh 2009-04-18 04:39:56 UTC
Created attachment 23508 [details]
workbook containing the scenario described

I've got a named range in my Excel sheet: 
Is_Multicar_Vehicle(='Veh Applicability'!$B$17:$G$17) i.e the above named range refers to cells from B17 to G17 in another sheet named "Veh Applicability".

Now I've the following formula in a cell C19 of sheet named "Input".

The following is the code snippet used to evaluate the above cell
			HSSFCell cell1 =  workbook.getSheet("Input").getRow(18).getCell(2);
			CellValue MultipleCarIndicator1 =  hsf.evaluate(cell1);

When I try to evalute the above cell, I am getting the below exception
Exception in thread "main" java.lang.RuntimeException: Don't now how to evalate name 'Is_Multicar_Vehicle'
	at org.apache.poi.ss.formula.WorkbookEvaluator.getEvalForPtg(WorkbookEvaluator.java:439)
	at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:334)
	at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:216)
	at org.apache.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:180)
	at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateFormulaCellValue(HSSFFormulaEvaluator.java:297)
	at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluate(HSSFFormulaEvaluator.java:159)
I'm attaching the excel sheet used.
I've used both POI 3.2 final and the 3.5beta5 release.
Please fix this issue. I am very much in need of this.

Kiran Sireesh
Comment 1 Josh Micich 2009-05-15 15:56:11 UTC
Fixed in svn r775376.

junits added

In your sample workbook, the defined name "Is_Multicar_Vehicle" is marked as 'complex' even though it is just an area ref.
Currently POI only evaluates defined names which are simple cell or area references.  Until now (according to existing test samples) it was assumed that these would not be flagged as complex.  It is not clear what the 'complex' flag is used for.

BTW - there was another bug in POI's implementation of INDEX() that would prevent your example code from completing. This has been fixed in svn r775360.  POI now produces the correct result: 1.0