You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@poi.apache.org by bu...@apache.org on 2008/05/07 17:28:07 UTC
DO NOT REPLY [Bug 44950] New: VLookup: fails if "table array" (2nd
arg) contains worksheet reference
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: dev@poi.apache.org
ReportedBy: dave.webster@virginmedia.co.uk
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: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
DO NOT REPLY [Bug 44950] Area3DEval.getValue() calculates wrong
array index
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=44950
Josh Micich <jo...@gildedtree.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|NEW |RESOLVED
Resolution| |FIXED
Summary|VLookup: fails if "table |Area3DEval.getValue()
|array" (2nd arg) contains |calculates wrong array index
|worksheet reference |
--- Comment #2 from Josh Micich <jo...@gildedtree.com> 2008-05-07 18:03:53 PST ---
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.
--
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: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
DO NOT REPLY [Bug 44950] VLookup: fails if "table array" (2nd arg)
contains worksheet reference
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=44950
--- Comment #1 from David Webster <da...@virginmedia.co.uk> 2008-05-07 08:28:52 PST ---
Created an attachment (id=21931)
--> (https://issues.apache.org/bugzilla/attachment.cgi?id=21931)
Excel file showing failing VLOOKUP function
--
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: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org