Bug 47048 - HSSF is unable to evaulate named ranges with the 'complex' flag set
Summary: HSSF is unable to evaulate named ranges with the 'complex' flag set
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 3.2-FINAL
Hardware: PC Windows XP
: P2 major (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2009-04-18 04:39 UTC by Kiran Sireesh
Modified: 2009-05-15 15:56 UTC (History)
0 users

workbook containing the scenario described (715.50 KB, application/vnd.ms-excel)
2009-04-18 04:39 UTC, Kiran Sireesh

Note You need to log in before you can comment on or make changes to this bug.
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