https://issues.apache.org/bugzilla/show_bug.cgi?id=44950
Summary: VLookup: fails if "table array" (2nd arg) contains
worksheet reference
Product: POI
Version: 3.0-dev
Platform: PC
OS/Version: Windows XP
Status: NEW
Severity: normal
Priority: P3
Component: HSSF
AssignedTo: [email protected]
ReportedBy: [EMAIL PROTECTED]
Created an attachment (id=21930)
--> (https://issues.apache.org/bugzilla/attachment.cgi?id=21930)
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
--
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
