From c61d6bfbbcf8f75e402246b62cc6b7358fc4408a Mon Sep 17 00:00:00 2001 From: PJ Fanning Date: Fri, 30 Jul 2021 12:47:03 +0000 Subject: [PATCH] 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 --- .../poi/ss/formula/functions/TestSum.java | 28 +++++++++++++++++-- .../poi/ss/formula/functions/TestSumif.java | 17 +++++++++++ .../java/org/apache/poi/ss/util/Utils.java | 17 +++++++++++ 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,23 +38,32 @@ 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); } } + @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 @@ -127,6 +127,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()) { @@ -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) { -- 2.39.5