Bug 44950 - Area3DEval.getValue() calculates wrong array index
Summary: Area3DEval.getValue() calculates wrong array index
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 3.0-dev
Hardware: PC Windows XP
: P3 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2008-05-07 08:28 UTC by David Webster
Modified: 2008-05-07 18:03 UTC (History)
0 users

java source prog. used to demonstrate the problem (3.92 KB, application/octet-stream)
2008-05-07 08:28 UTC, David Webster
Excel file showing failing VLOOKUP function (14.00 KB, application/vnd.ms-excel)
2008-05-07 08:28 UTC, David Webster

Note You need to log in before you can comment on or make changes to this bug.
Description David Webster 2008-05-07 08:28:06 UTC
Created attachment 21930 [details]
java source prog. used to demonstrate the problem

Hello All, 


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.

=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.

=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!

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);
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(sheet, wb);

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.