vlookup function with "empty" fourth argument. ex. vlookup(A1,E1:F10,2,) An exception is raised. Unexpected eval type (org.apache.poi.ss.formula.eval.MissingArgEval) Sorry for bad explanation.
I also encountered this bug. I think it will be fixed that if we check the last argument is MissingArgEval in following classes: - Var1or2ArgFunction - Var2or3ArgFunction - Var3or4ArgFunction What do you think?
Can you check with the latest nightly build? https://poi.apache.org/download.html#nightly I think that we might have added a fix for this already.
This bug is not fixed. Even in the latest nightly build, it is reproduced with the following code. CreationHelper crateHelper = wb.getCreationHelper(); FormulaEvaluator eval = crateHelper.createFormulaEvaluator(); Cell cell = row.getCell(columnIndex); eval.evaluate(cell);
Could you provide a full test case?
It reproduced with the following test case. public void test62275_bug() throws IOException { Workbook wb = new XSSFWorkbook(); Sheet sheet = wb.createSheet(); Row row = sheet.createRow(0); Cell cell = row.createCell(0); cell.setCellFormula("vlookup(A2,B1:B5,2,)"); CreationHelper crateHelper = wb.getCreationHelper(); FormulaEvaluator eval = crateHelper.createFormulaEvaluator(); eval.evaluate(cell); wb.close(); }
https://svn.apache.org/viewvc?view=revision&revision=1836857 was merged
Thank you for fixing. but,In EXCEL, when the fourth argument of the vlookup function is "empty", it operates in the same way as when FALSE is specified. I suggest adding the following to resolveRangeLookupArg method of LookupUtils. if (valEval instanceof MissingArgEval) { return false; } Thank you.
This was actually already fixed some time ago via r1836857, the given test-case and some other cases work fine now.
(In reply to dolphin.in.the.sky.51 from comment #7) > In EXCEL, when the fourth argument of the vlookup function is "empty", > it operates in the same way as when FALSE is specified. https://support.office.com/en-us/article/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1 > Optionally, you can specify TRUE if you want an approximate > match or FALSE if you want an exact match of the return > value. If you don't specify anything, the default value will > always be TRUE or approximate match. In reply to Dominik Stadler from comment #8) > This was actually already fixed some time ago via r1836857, the given > test-case and some other cases work fine now. > try { > + isRangeLookup = LookupUtils.resolveRangeLookupArg(range_lookup, srcRowIndex, srcColumnIndex); > + } catch(RuntimeException e) { > + isRangeLookup = true; > + } It appears that POI defaults the fourth argument to true if it's omitted.
At least the documentation at https://support.office.com/en-us/article/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1 indicates that the default is "true", was the statement about Excel using "false" based on actual experiments?