aboutsummaryrefslogtreecommitdiffstats
path: root/poi
diff options
context:
space:
mode:
authorPJ Fanning <fanningpj@apache.org>2021-07-30 13:28:02 +0000
committerPJ Fanning <fanningpj@apache.org>2021-07-30 13:28:02 +0000
commitd8d60e4e72c29f787fb508bd6d759d18d4b35dba (patch)
tree27702049abad0d12305b0db0ccdfa10d3dd88d58 /poi
parent1a8fdb6b6c69699dbd2b0288460e38c0d0c8af82 (diff)
downloadpoi-d8d60e4e72c29f787fb508bd6d759d18d4b35dba.tar.gz
poi-d8d60e4e72c29f787fb508bd6d759d18d4b35dba.zip
SUMIFS should treat #N/A correctly
git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1891902 13f79535-47bb-0310-9956-ffa450edef68
Diffstat (limited to 'poi')
-rw-r--r--poi/src/test/java/org/apache/poi/ss/formula/functions/TestSumif.java38
-rw-r--r--poi/src/test/java/org/apache/poi/ss/formula/functions/TestSumifs.java46
-rw-r--r--poi/src/test/java/org/apache/poi/ss/util/Utils.java22
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());
+ }
}