Whenever in array mode, a) operands are collected for an array function or b) array function is evaluated, and an ErrorEval is encountered, an EvaluationException is thrown, and the whole result is set to ErrorEval. This doesn't correspond to Excel behavior, where calculations fall into separate "threads", so that an error in a "thread" doens't affect other ones. Example: A | B | C | D | E | F | 1 | #N/A! | 2 | {=A1:C1*A1:C1} | ======================================= expected | 1 | #N/A! | 4 | actual | #N/A! | #N/A! | #N/A! | A testcase: @Test public void elementwiseEvaluation() { Workbook workbook = new XSSFWorkbook(); Row row = workbook.createSheet().createRow(0); row.createCell(0).setCellValue(1); row.createCell(1).setCellErrorValue(FormulaError.NUM.getCode()); row.createCell(2).setCellValue(2); row.getSheet().setArrayFormula("A1:C1*A1:C1", CellRangeAddress.valueOf("D1:F1")); workbook.getCreationHelper().createFormulaEvaluator().evaluateAll(); assertEquals(CellType.NUMERIC, row.getCell(3).getCachedFormulaResultType()); assertEquals(1, row.getCell(3).getNumericCellValue(), 0); assertEquals(CellType.ERROR, row.getCell(4).getCachedFormulaResultType()); assertEquals(FormulaError.NUM.getCode(), row.getCell(4).getErrorCellValue()); assertEquals(CellType.NUMERIC, row.getCell(5).getCachedFormulaResultType()); assertEquals(4, row.getCell(5).getNumericCellValue(), 0); }
The fix in bug #62904 fixed this issue as well. The test cases are in TwoOperandNumericFunctionTestCaseData.xls The only case that fails is #NAME?, but that's the way POI handles unknown names, it's not a problem in evaluation of array arguments. When creating a spreadsheet manually you can set a cell formula to reference an unknown name, e.g. =not_here and WorkbookEvaluator will fail early with java.lang.RuntimeException: Don't now how to evaluate name 'not_here' at org.apache.poi.ss.formula.WorkbookEvaluator.getEvalForNameRecord(WorkbookEvaluator.java:771) at org.apache.poi.ss.formula.WorkbookEvaluator.getEvalForPtg(WorkbookEvaluator.java:681) at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:548) it doesn't even make it to the evaluation code. Regards, Yegor