From d8d60e4e72c29f787fb508bd6d759d18d4b35dba Mon Sep 17 00:00:00 2001 From: PJ Fanning Date: Fri, 30 Jul 2021 13:28:02 +0000 Subject: [PATCH] SUMIFS should treat #N/A correctly git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1891902 13f79535-47bb-0310-9956-ffa450edef68 --- .../poi/ss/formula/functions/TestSumif.java | 38 +++++---------- .../poi/ss/formula/functions/TestSumifs.java | 46 +++++++++++++++++++ .../java/org/apache/poi/ss/util/Utils.java | 22 +++++++-- 3 files changed, 75 insertions(+), 31 deletions(-) diff --git a/poi/src/test/java/org/apache/poi/ss/formula/functions/TestSumif.java b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestSumif.java index 96bd358234..d59788a20e 100644 --- a/poi/src/test/java/org/apache/poi/ss/formula/functions/TestSumif.java +++ b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestSumif.java @@ -17,7 +17,7 @@ package org.apache.poi.ss.formula.functions; -import static org.apache.poi.ss.util.Utils.addRow; +import static org.apache.poi.ss.util.Utils.*; import static org.junit.jupiter.api.Assertions.assertEquals; import static org.junit.jupiter.api.Assertions.assertNotEquals; import static org.junit.jupiter.api.Assertions.assertTrue; @@ -111,10 +111,10 @@ final class TestSumif { try (HSSFWorkbook wb = initWorkbook1()) { HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb); HSSFCell cell = wb.getSheetAt(0).getRow(0).createCell(100); - confirmDouble(fe, cell, "SUMIF(A2:A5,\">160000\",B2:B5)", 63000); - confirmDouble(fe, cell, "SUMIF(A2:A5,\">160000\")", 900000); - confirmDouble(fe, cell, "SUMIF(A2:A5,300000,B2:B5)", 21000); - confirmDouble(fe, cell, "SUMIF(A2:A5,\">\" & C2,B2:B5)", 49000); + assertDouble(fe, cell, "SUMIF(A2:A5,\">160000\",B2:B5)", 63000); + assertDouble(fe, cell, "SUMIF(A2:A5,\">160000\")", 900000); + assertDouble(fe, cell, "SUMIF(A2:A5,300000,B2:B5)", 21000); + assertDouble(fe, cell, "SUMIF(A2:A5,\">\" & C2,B2:B5)", 49000); } } @@ -123,7 +123,7 @@ final class TestSumif { try (HSSFWorkbook wb = initWorkbook1WithNA()) { HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb); HSSFCell cell = wb.getSheetAt(0).getRow(0).createCell(100); - confirmError(fe, cell, "SUMIF(A2:A6,\">160000\",B2:B6)", FormulaError.NA); + assertError(fe, cell, "SUMIF(A2:A6,\">160000\",B2:B6)", FormulaError.NA); } } @@ -132,7 +132,7 @@ final class TestSumif { try (HSSFWorkbook wb = initWorkbook1WithBooleanAndString()) { HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb); HSSFCell cell = wb.getSheetAt(0).getRow(0).createCell(100); - confirmDouble(fe, cell, "SUMIF(A2:A7,\">160000\",B2:B7)", 63000); + assertDouble(fe, cell, "SUMIF(A2:A7,\">160000\",B2:B7)", 63000); } } @@ -141,10 +141,10 @@ final class TestSumif { try (HSSFWorkbook wb = initWorkbook2()) { HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb); HSSFCell cell = wb.getSheetAt(0).getRow(0).createCell(100); - confirmDouble(fe, cell, "SUMIF(A2:A7,\"Fruits\",C2:C7)", 2000); - confirmDouble(fe, cell, "SUMIF(A2:A7,\"Vegetables\",C2:C7)", 12000); - confirmDouble(fe, cell, "SUMIF(B2:B7,\"*es\",C2:C7)", 4300); - confirmDouble(fe, cell, "SUMIF(A2:A7,\"\",C2:C7)", 400); + assertDouble(fe, cell, "SUMIF(A2:A7,\"Fruits\",C2:C7)", 2000); + assertDouble(fe, cell, "SUMIF(A2:A7,\"Vegetables\",C2:C7)", 12000); + assertDouble(fe, cell, "SUMIF(B2:B7,\"*es\",C2:C7)", 4300); + assertDouble(fe, cell, "SUMIF(A2:A7,\"\",C2:C7)", 400); } } @@ -201,20 +201,4 @@ final class TestSumif { NumericValueEval nve = (NumericValueEval)actualEval; assertEquals(expected, nve.getNumberValue(), 0); } - - private static void confirmDouble(HSSFFormulaEvaluator fe, HSSFCell cell, String formulaText, double expectedResult) { - cell.setCellFormula(formulaText); - fe.notifyUpdateCell(cell); - CellValue result = fe.evaluate(cell); - assertEquals(result.getCellType(), CellType.NUMERIC); - assertEquals(expectedResult, result.getNumberValue()); - } - - private static void confirmError(HSSFFormulaEvaluator fe, HSSFCell cell, String formulaText, FormulaError expectedError) { - cell.setCellFormula(formulaText); - fe.notifyUpdateCell(cell); - CellValue result = fe.evaluate(cell); - assertEquals(result.getCellType(), CellType.ERROR); - assertEquals(expectedError.getCode(), result.getErrorValue()); - } } diff --git a/poi/src/test/java/org/apache/poi/ss/formula/functions/TestSumifs.java b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestSumifs.java index 629032c5a1..234d6392f4 100644 --- a/poi/src/test/java/org/apache/poi/ss/formula/functions/TestSumifs.java +++ b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestSumifs.java @@ -19,6 +19,7 @@ package org.apache.poi.ss.formula.functions; +import static org.apache.poi.ss.util.Utils.*; import static org.junit.jupiter.api.Assertions.assertEquals; import static org.junit.jupiter.api.Assertions.assertTrue; @@ -33,8 +34,11 @@ import org.apache.poi.ss.formula.eval.NumberEval; import org.apache.poi.ss.formula.eval.NumericValueEval; import org.apache.poi.ss.formula.eval.StringEval; import org.apache.poi.ss.formula.eval.ValueEval; +import org.apache.poi.ss.usermodel.FormulaError; import org.junit.jupiter.api.Test; +import java.io.IOException; + /** * Test cases for SUMIFS() */ @@ -358,4 +362,46 @@ final class TestSumifs { assertTrue(result instanceof ErrorEval, "Expect to have an error when an input is an invalid value, but had: " + result.getClass()); assertEquals(ErrorEval.NAME_INVALID, result); } + + @Test + void testMicrosoftExample1() throws IOException { + try (HSSFWorkbook wb = initWorkbook1()) { + HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb); + HSSFCell cell = wb.getSheetAt(0).getRow(0).createCell(100); + assertDouble(fe, cell, "SUMIFS(A2:A9, B2:B9, \"=A*\", C2:C9, \"Tom\")", 20); + assertDouble(fe, cell, "SUMIFS(A2:A9, B2:B9, \"<>Bananas\", C2:C9, \"Tom\")", 30); + } + } + + @Test + void testMicrosoftExample1WithNA() throws IOException { + try (HSSFWorkbook wb = initWorkbook1WithNA()) { + HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb); + HSSFCell cell = wb.getSheetAt(0).getRow(0).createCell(100); + assertError(fe, cell, "SUMIFS(A2:A10, B2:B10, \"<>Bananas\", C2:C10, \"Tom\")", FormulaError.NA); + } + } + + //see https://support.microsoft.com/en-us/office/sumifs-function-c9e748f5-7ea7-455d-9406-611cebce642b + private HSSFWorkbook initWorkbook1() { + HSSFWorkbook wb = new HSSFWorkbook(); + HSSFSheet sheet = wb.createSheet(); + addRow(sheet, 0, "Quantity Sold", "Product", "Salesperson"); + addRow(sheet, 1, 5, "Apples", "Tom"); + addRow(sheet, 2, 4, "Apples", "Sarah"); + addRow(sheet, 3, 15, "Artichokes", "Tom"); + addRow(sheet, 4, 3, "Artichokes", "Sarah"); + addRow(sheet, 5, 22, "Bananas", "Tom"); + addRow(sheet, 6, 12, "Bananas", "Sarah"); + addRow(sheet, 7, 10, "Carrots", "Tom"); + addRow(sheet, 8, 33, "Carrots", "Sarah"); + return wb; + } + + private HSSFWorkbook initWorkbook1WithNA() { + HSSFWorkbook wb = initWorkbook1(); + HSSFSheet sheet = wb.getSheetAt(0); + addRow(sheet, 9, FormulaError.NA, "Pears", "Tom"); + return wb; + } } diff --git a/poi/src/test/java/org/apache/poi/ss/util/Utils.java b/poi/src/test/java/org/apache/poi/ss/util/Utils.java index 0b01547308..c2c8d67cb1 100644 --- a/poi/src/test/java/org/apache/poi/ss/util/Utils.java +++ b/poi/src/test/java/org/apache/poi/ss/util/Utils.java @@ -17,16 +17,15 @@ package org.apache.poi.ss.util; -import org.apache.poi.ss.usermodel.Cell; -import org.apache.poi.ss.usermodel.FormulaError; -import org.apache.poi.ss.usermodel.Row; -import org.apache.poi.ss.usermodel.Sheet; +import org.apache.poi.ss.usermodel.*; import java.time.LocalDate; import java.time.LocalDateTime; import java.util.Calendar; import java.util.Date; +import static org.junit.jupiter.api.Assertions.assertEquals; + public class Utils { public static void addRow(Sheet sheet, int rownum, Object... values) { Row row = sheet.createRow(rownum); @@ -56,4 +55,19 @@ public class Utils { } } + public static void assertDouble(FormulaEvaluator fe, Cell cell, String formulaText, double expectedResult) { + cell.setCellFormula(formulaText); + fe.notifyUpdateCell(cell); + CellValue result = fe.evaluate(cell); + assertEquals(result.getCellType(), CellType.NUMERIC); + assertEquals(expectedResult, result.getNumberValue()); + } + + public static void assertError(FormulaEvaluator fe, Cell cell, String formulaText, FormulaError expectedError) { + cell.setCellFormula(formulaText); + fe.notifyUpdateCell(cell); + CellValue result = fe.evaluate(cell); + assertEquals(result.getCellType(), CellType.ERROR); + assertEquals(expectedError.getCode(), result.getErrorValue()); + } } -- 2.39.5