SUMIFS should sum and treat booleans and strings as zero

git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1891900 13f79535-47bb-0310-9956-ffa450edef68

3 files changed, 59 insertions, 3 deletions

diff --git a/poi/src/test/java/org/apache/poi/ss/formula/functions/TestSum.java b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestSum.java
index bd90ccb22d..9be0c81485 100644
--- a/poi/src/test/java/org/apache/poi/ss/formula/functions/TestSum.java
+++ b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestSum.java
@@ -38,7 +38,7 @@ final class TestSum {
 
     @Test
     void testSum() throws IOException {
-        try (HSSFWorkbook wb = initWorkbook1()) {
+        try (HSSFWorkbook wb = initWorkbookWithNA()) {
             HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
             HSSFCell cell = wb.getSheetAt(0).getRow(0).createCell(100);
             confirmDouble(fe, cell, "SUM(B2:B5)", 70000);
@@ -46,15 +46,24 @@ final class TestSum {
     }
 
     @Test
+    void testSumWithBooleanAndString() throws IOException {
+        try (HSSFWorkbook wb = initWorkbookWithBooleanAndString()) {
+            HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
+            HSSFCell cell = wb.getSheetAt(0).getRow(0).createCell(100);
+            confirmDouble(fe, cell, "SUM(B2:B7)", 70000);
+        }
+    }
+
+    @Test
     void testSumWithNA() throws IOException {
-        try (HSSFWorkbook wb = initWorkbook1()) {
+        try (HSSFWorkbook wb = initWorkbookWithNA()) {
             HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
             HSSFCell cell = wb.getSheetAt(0).getRow(0).createCell(100);
             confirmError(fe, cell, "SUM(B2:B6)", FormulaError.NA);
         }
     }
 
-    private HSSFWorkbook initWorkbook1() {
+    private HSSFWorkbook initWorkbookWithNA() {
         HSSFWorkbook wb = new HSSFWorkbook();
         HSSFSheet sheet = wb.createSheet();
         addRow(sheet, 0, "Property Value", "Commission", "Data");
@@ -66,6 +75,19 @@ final class TestSum {
         return wb;
     }
 
+    private HSSFWorkbook initWorkbookWithBooleanAndString() {
+        HSSFWorkbook wb = new HSSFWorkbook();
+        HSSFSheet sheet = wb.createSheet();
+        addRow(sheet, 0, "Property Value", "Commission", "Data");
+        addRow(sheet, 1, 100000, 7000, 250000);
+        addRow(sheet, 2, 200000, 14000);
+        addRow(sheet, 3, 300000, 21000);
+        addRow(sheet, 4, 400000, 28000);
+        addRow(sheet, 5, 500000, true);
+        addRow(sheet, 6, 600000, "abc");
+        return wb;
+    }
+
     private static void confirmDouble(HSSFFormulaEvaluator fe, HSSFCell cell, String formulaText, double expectedResult) {
         cell.setCellFormula(formulaText);
         fe.notifyUpdateCell(cell);
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 f8b06b7d5b..96bd358234 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
@@ -128,6 +128,15 @@ final class TestSumif {
     }
 
     @Test
+    void testMicrosoftExample1WithBooleanAndString() throws IOException {
+        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);
+        }
+    }
+
+    @Test
     void testMicrosoftExample2() throws IOException {
         try (HSSFWorkbook wb = initWorkbook2()) {
             HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
@@ -158,6 +167,14 @@ final class TestSumif {
         return wb;
     }
 
+    private HSSFWorkbook initWorkbook1WithBooleanAndString() {
+        HSSFWorkbook wb = initWorkbook1();
+        HSSFSheet sheet = wb.getSheetAt(0);
+        addRow(sheet, 5, 500000, true);
+        addRow(sheet, 6, 600000, "abc");
+        return wb;
+    }
+
     private HSSFWorkbook initWorkbook2() {
         HSSFWorkbook wb = new HSSFWorkbook();
         HSSFSheet sheet = wb.createSheet();
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 09e4d428d0..311c4f610e 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
@@ -5,6 +5,11 @@ import org.apache.poi.ss.usermodel.FormulaError;
 import org.apache.poi.ss.usermodel.Row;
 import org.apache.poi.ss.usermodel.Sheet;
 
+import java.time.LocalDate;
+import java.time.LocalDateTime;
+import java.util.Calendar;
+import java.util.Date;
+
 public class Utils {
     public static void addRow(Sheet sheet, int rownum, Object... values) {
         Row row = sheet.createRow(rownum);
@@ -12,6 +17,18 @@ public class Utils {
             Cell cell = row.createCell(i);
             if (values[i] instanceof Integer) {
                 cell.setCellValue((Integer) values[i]);
+            } else if (values[i] instanceof Double) {
+                cell.setCellValue((Double) values[i]);
+            } else if (values[i] instanceof Boolean) {
+                cell.setCellValue((Boolean) values[i]);
+            } else if (values[i] instanceof Calendar) {
+                cell.setCellValue((Calendar) values[i]);
+            } else if (values[i] instanceof Date) {
+                cell.setCellValue((Date) values[i]);
+            } else if (values[i] instanceof LocalDate) {
+                cell.setCellValue((LocalDate) values[i]);
+            } else if (values[i] instanceof LocalDateTime) {
+                cell.setCellValue((LocalDateTime) values[i]);
             } else if (values[i] instanceof FormulaError) {
                 cell.setCellErrorValue(((FormulaError)values[i]).getCode());
             } else if (values[i] == null) {