Bug 44950

Summary: Area3DEval.getValue() calculates wrong array index
Product: POI Reporter: David Webster <dave.webster>
Component: HSSFAssignee: POI Developers List <dev>
Status: RESOLVED FIXED    
Severity: normal    
Priority: P3    
Version: 3.0-dev   
Target Milestone: ---   
Hardware: PC   
OS: Windows XP   
Attachments: java source prog. used to demonstrate the problem
Excel file showing failing VLOOKUP function

Description David Webster 2008-05-07 08:28:06 UTC
Created attachment 21930 [details]
java source prog. used to demonstrate the problem

Hello All, 


Problem
-------

Using poi-3.1-beta2-20080506.jar ...

If a worksheet cell contains a VLOOKUP formula ( signature = VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) ), then evaulation of the formula fails if the "table_array" value contains a reference to another worksheet.

i.e.
=VLOOKUP(Sheet1!A1, Sheet2!A1:B2, 2, 0)

Using the HSSFFormulaEvaluator.evaluateInCell() method, when it comes across a cell with a VLOOKUP such as the one above, the following Java exception is displayed:

java.lang.NumberFormatException: You cannot get an error value from a non-error cell
        at org.apache.poi.hssf.usermodel.HSSFCell.getErrorCellValue(HSSFCell.java:889)
        at org.apache.poi.hssf.usermodel.HSSFCell.setCellType(HSSFCell.java:476)
        at org.apache.poi.hssf.usermodel.HSSFCell.setCellType(HSSFCell.java:316)
        at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateInCell(HSSFFormulaEvaluator.java:235)
        at VLookupBug.evaluateAllFormulasInCell(VLookupBug.java:55)
        at VLookupBug.main(VLookupBug.java:98)

Using HSSFFormulaEvaluator.evaluate() also does not work, with the cell type coming back as CELL_TYPE_ERROR and "#N/A" as the actual value.


If the VLOOKUP function does NOT contain a reference to another sheet in its "table_array" field, all works fine with POI.

i.e.
=VLOOKUP(Sheet1!A1, A1:B2, 2, 0)

works okay.


Expected Behaviour
------------------

Well, this signature of VLOOKUP works in Excel, so was hoping it would work in POI as well.


To Replicate
------------

See attached Excel spreadsheet, and Java source prog (compiled with JDK 1.5, but don't think the version is relevant).

Once compiled, 

prompt% java VLookupBug vlookup_bug.xls 

shows the problem.



Any ideas first before I start digging about in Formula Evaluator code?


Cheers and muchas thanks as always!

Dave
Comment 1 David Webster 2008-05-07 08:28:52 UTC
Created attachment 21931 [details]
Excel file showing failing VLOOKUP function
Comment 2 Josh Micich 2008-05-07 18:03:53 UTC
Fixed in svn r654356.

The Vlookup impl was working OK, but there were 2 bugs elsewhere, so I changed the summary of this bugzilla.

The problem only surfaced if you use a 3D area reference.  This first problem resulted in Vlookup returning '#N/A'.  A secondary problem was in HSSFFormulaEvaluator.evaluateInCell(), that prevented the '#N/A' error code from being set in the cell. The following code shows that error by itself:

HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("Sheet1");
HSSFRow row = sheet.createRow(1);
HSSFCell cell = row.createCell((short) 0);
cell.setCellFormula("na()");'#N/A'
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(sheet, wb);
fe.setCurrentRow(row);
fe.evaluateInCell(cell);


2 new junit test cases were added for these bugs.

The common code of Area2DEval and Area3DEval was abstracted into a new superclass.  An additional check was added to make sure that the number of elements in the values array agrees with the row/col size of the area ref Ptg.  Some junits needed updating.