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
Created attachment 21931 [details] Excel file showing failing VLOOKUP function
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.