You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@poi.apache.org by fa...@apache.org on 2021/07/30 13:28:02 UTC
svn commit: r1891902 - in /poi/trunk/poi/src/test/java/org/apache/poi/ss:
formula/functions/TestSumif.java formula/functions/TestSumifs.java
util/Utils.java
Author: fanningpj
Date: Fri Jul 30 13:28:02 2021
New Revision: 1891902
URL: http://svn.apache.org/viewvc?rev=1891902&view=rev
Log:
SUMIFS should treat #N/A correctly
Modified:
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestSumif.java
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestSumifs.java
poi/trunk/poi/src/test/java/org/apache/poi/ss/util/Utils.java
Modified: poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestSumif.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestSumif.java?rev=1891902&r1=1891901&r2=1891902&view=diff
==============================================================================
--- poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestSumif.java (original)
+++ poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestSumif.java Fri Jul 30 13:28:02 2021
@@ -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());
- }
}
Modified: poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestSumifs.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestSumifs.java?rev=1891902&r1=1891901&r2=1891902&view=diff
==============================================================================
--- poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestSumifs.java (original)
+++ poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestSumifs.java Fri Jul 30 13:28:02 2021
@@ -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.Nu
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;
+ }
}
Modified: poi/trunk/poi/src/test/java/org/apache/poi/ss/util/Utils.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/test/java/org/apache/poi/ss/util/Utils.java?rev=1891902&r1=1891901&r2=1891902&view=diff
==============================================================================
--- poi/trunk/poi/src/test/java/org/apache/poi/ss/util/Utils.java (original)
+++ poi/trunk/poi/src/test/java/org/apache/poi/ss/util/Utils.java Fri Jul 30 13:28:02 2021
@@ -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());
+ }
}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org