From 4e3008acdb1287325e93ce27de91e28d265be058 Mon Sep 17 00:00:00 2001 From: PJ Fanning Date: Fri, 14 Aug 2020 07:42:18 +0000 Subject: [PATCH] =?utf8?q?[github-191]=20Fix=20RuntimeException=20on=20arr?= =?utf8?q?ay=20formula=20referencing=20blank=20cell.=20Thanks=20to=20Mi?= =?utf8?q?=C5=82osz=20Rembisz.=20This=20closes=20#191?= MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1880844 13f79535-47bb-0310-9956-ffa450edef68 --- .../poi/ss/formula/WorkbookEvaluator.java | 6 +++ .../poi/ss/formula/TestWorkbookEvaluator.java | 39 +++++++++++++++++++ 2 files changed, 45 insertions(+) diff --git a/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java b/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java index 060f274ef0..77d0937876 100644 --- a/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java +++ b/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java @@ -633,6 +633,12 @@ public final class WorkbookEvaluator { else { value = dereferenceResult(evaluationResult, ec.getRowIndex(), ec.getColumnIndex()); } + if (value == BlankEval.instance) { + // Note Excel behaviour here. A blank final final value is converted to zero. + return NumberEval.ZERO; + // Formulas _never_ evaluate to blank. If a formula appears to have evaluated to + // blank, the actual value is empty string. This can be verified with ISBLANK(). + } return value; } diff --git a/src/testcases/org/apache/poi/ss/formula/TestWorkbookEvaluator.java b/src/testcases/org/apache/poi/ss/formula/TestWorkbookEvaluator.java index 06c37dc025..bf89fca10c 100644 --- a/src/testcases/org/apache/poi/ss/formula/TestWorkbookEvaluator.java +++ b/src/testcases/org/apache/poi/ss/formula/TestWorkbookEvaluator.java @@ -46,6 +46,7 @@ import org.apache.poi.ss.usermodel.Name; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; +import org.apache.poi.ss.util.CellRangeAddress; import org.junit.Ignore; import org.junit.Test; @@ -613,4 +614,42 @@ public class TestWorkbookEvaluator { testIFEqualsFormulaEvaluation_teardown(wb); } + + @Test + public void testRefToBlankCellInArrayFormula() { + Workbook wb = new HSSFWorkbook(); + Sheet sheet = wb.createSheet(); + Row row = sheet.createRow(0); + Cell cellA1 = row.createCell(0); + Cell cellB1 = row.createCell(1); + Cell cellC1 = row.createCell(2); + Row row2 = sheet.createRow(1); + Cell cellA2 = row2.createCell(0); + Cell cellB2 = row2.createCell(1); + Cell cellC2 = row2.createCell(2); + Row row3 = sheet.createRow(2); + Cell cellA3 = row3.createCell(0); + Cell cellB3 = row3.createCell(1); + Cell cellC3 = row3.createCell(2); + + cellA1.setCellValue("1"); + // cell B1 intentionally left blank + cellC1.setCellValue("3"); + + cellA2.setCellFormula("A1"); + cellB2.setCellFormula("B1"); + cellC2.setCellFormula("C1"); + + sheet.setArrayFormula("A1:C1", CellRangeAddress.valueOf("A3:C3")); + + wb.getCreationHelper().createFormulaEvaluator().evaluateAll(); + + assertEquals(cellA2.getStringCellValue(), "1"); + assertEquals(cellB2.getNumericCellValue(),0, 0.00001); + assertEquals(cellC2.getStringCellValue(),"3"); + + assertEquals(cellA3.getStringCellValue(), "1"); + assertEquals(cellB3.getNumericCellValue(),0, 0.00001); + assertEquals(cellC3.getStringCellValue(),"3"); + } } -- 2.39.5