From 6b6bd5b773d676ec21612b54d3ee94188d0ef1a6 Mon Sep 17 00:00:00 2001 From: PJ Fanning Date: Thu, 26 May 2022 13:48:30 +0000 Subject: [PATCH] support DCOUNTA function git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1901290 13f79535-47bb-0310-9956-ffa450edef68 --- .../poi/ss/formula/eval/FunctionEval.java | 2 +- .../poi/ss/formula/functions/DCount.java | 2 +- .../poi/ss/formula/functions/DCountA.java | 49 ++++++++++++++ .../poi/ss/formula/functions/DStarRunner.java | 2 + .../poi/ss/formula/functions/TestDCount.java | 5 +- .../poi/ss/formula/functions/TestDCountA.java | 66 +++++++++++++++++++ 6 files changed, 123 insertions(+), 3 deletions(-) create mode 100644 poi/src/main/java/org/apache/poi/ss/formula/functions/DCountA.java create mode 100644 poi/src/test/java/org/apache/poi/ss/formula/functions/TestDCountA.java diff --git a/poi/src/main/java/org/apache/poi/ss/formula/eval/FunctionEval.java b/poi/src/main/java/org/apache/poi/ss/formula/eval/FunctionEval.java index 32750a6b0e..aa03d7e4f2 100644 --- a/poi/src/main/java/org/apache/poi/ss/formula/eval/FunctionEval.java +++ b/poi/src/main/java/org/apache/poi/ss/formula/eval/FunctionEval.java @@ -210,7 +210,7 @@ public final class FunctionEval { retval[196] = new DStarRunner(DStarRunner.DStarAlgorithmEnum.DVARP); retval[197] = NumericFunction.TRUNC; retval[198] = LogicalFunction.ISLOGICAL; - // 199: DCOUNTA + retval[199] = new DStarRunner(DStarRunner.DStarAlgorithmEnum.DCOUNTA); //204: USDOLLAR (YEN in BIFF3) //205: FINDB diff --git a/poi/src/main/java/org/apache/poi/ss/formula/functions/DCount.java b/poi/src/main/java/org/apache/poi/ss/formula/functions/DCount.java index c2ea0ebeb8..47010a36ba 100644 --- a/poi/src/main/java/org/apache/poi/ss/formula/functions/DCount.java +++ b/poi/src/main/java/org/apache/poi/ss/formula/functions/DCount.java @@ -23,7 +23,7 @@ import org.apache.poi.ss.formula.eval.ValueEval; /** * Implementation of the DCount function: - * Counts the value of a column in an area with given conditions. + * Counts the number of numeric cells in a column in an area with given conditions. */ public final class DCount implements IDStarAlgorithm { private long count; diff --git a/poi/src/main/java/org/apache/poi/ss/formula/functions/DCountA.java b/poi/src/main/java/org/apache/poi/ss/formula/functions/DCountA.java new file mode 100644 index 0000000000..6b9cc27aee --- /dev/null +++ b/poi/src/main/java/org/apache/poi/ss/formula/functions/DCountA.java @@ -0,0 +1,49 @@ +/* ==================================================================== + Licensed to the Apache Software Foundation (ASF) under one or more + contributor license agreements. See the NOTICE file distributed with + this work for additional information regarding copyright ownership. + The ASF licenses this file to You under the Apache License, Version 2.0 + (the "License"); you may not use this file except in compliance with + the License. You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, software + distributed under the License is distributed on an "AS IS" BASIS, + WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + See the License for the specific language governing permissions and + limitations under the License. +==================================================================== */ + +package org.apache.poi.ss.formula.functions; + +import org.apache.poi.ss.formula.eval.BlankEval; +import org.apache.poi.ss.formula.eval.NumberEval; +import org.apache.poi.ss.formula.eval.NumericValueEval; +import org.apache.poi.ss.formula.eval.ValueEval; + +/** + * Implementation of the DCountA function: + * Counts the number of non-blank cells in a column in an area with given conditions. + */ +public final class DCountA implements IDStarAlgorithm { + private long count; + + @Override + public boolean processMatch(ValueEval eval) { + if (!(eval instanceof BlankEval)) { + count++; + } + return true; + } + + @Override + public ValueEval getResult() { + return new NumberEval(count); + } + + @Override + public boolean allowEmptyMatchField() { + return true; + } +} diff --git a/poi/src/main/java/org/apache/poi/ss/formula/functions/DStarRunner.java b/poi/src/main/java/org/apache/poi/ss/formula/functions/DStarRunner.java index 5a3fbfbdea..1ab9c54d29 100644 --- a/poi/src/main/java/org/apache/poi/ss/formula/functions/DStarRunner.java +++ b/poi/src/main/java/org/apache/poi/ss/formula/functions/DStarRunner.java @@ -59,6 +59,8 @@ public final class DStarRunner implements Function3Arg { DSUM(DSum::new), /** @see DCount */ DCOUNT(DCount::new), + /** @see DCountA */ + DCOUNTA(DCountA::new), /** @see DAverage */ DAVERAGE(DAverage::new), /** @see DStdev */ diff --git a/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDCount.java b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDCount.java index 31888498ee..a75d1ca22c 100644 --- a/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDCount.java +++ b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDCount.java @@ -43,6 +43,9 @@ public class TestDCount { assertDouble(fe, cell, "DCOUNT(A5:E11, \"Age\", A1:A2)", 2); assertDouble(fe, cell, "DCOUNT(A5:E11, \"Age\", A1:F2)", 1); assertDouble(fe, cell, "DCOUNT(A5:E11, 3, A1:F2)", 1); + assertDouble(fe, cell, "DCOUNT(A5:E11, 2, A1:F3)", 4); + assertDouble(fe, cell, "DCOUNT(A5:E11, 3, A1:F3)", 3); + assertDouble(fe, cell, "DCOUNT(A5:E11, 5, A1:F3)", 3); } } @@ -58,7 +61,7 @@ public class TestDCount { addRow(sheet, 6, "Pear", 12, 12, 10, 96); addRow(sheet, 7, "Cherry", 13, 14, 9, 105); addRow(sheet, 8, "Apple", 14, null, 10, 75); - addRow(sheet, 9, "Pear", 9, 8, 8, 77); + addRow(sheet, 9, "Pear", 9, 8, 8, "$77"); addRow(sheet, 10, "Apple", 12, 11, 6, 45); return wb; } diff --git a/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDCountA.java b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDCountA.java new file mode 100644 index 0000000000..85ee0ff510 --- /dev/null +++ b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDCountA.java @@ -0,0 +1,66 @@ + +/* ==================================================================== + Licensed to the Apache Software Foundation (ASF) under one or more + contributor license agreements. See the NOTICE file distributed with + this work for additional information regarding copyright ownership. + The ASF licenses this file to You under the Apache License, Version 2.0 + (the "License"); you may not use this file except in compliance with + the License. You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, software + distributed under the License is distributed on an "AS IS" BASIS, + WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + See the License for the specific language governing permissions and + limitations under the License. +==================================================================== */ +package org.apache.poi.ss.formula.functions; + +import org.apache.poi.hssf.usermodel.HSSFCell; +import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator; +import org.apache.poi.hssf.usermodel.HSSFSheet; +import org.apache.poi.hssf.usermodel.HSSFWorkbook; +import org.junit.jupiter.api.Test; + +import java.io.IOException; + +import static org.apache.poi.ss.util.Utils.addRow; +import static org.apache.poi.ss.util.Utils.assertDouble; + +/** + * Testcase for function DCOUNTA() + */ +public class TestDCountA { + + //https://support.microsoft.com/en-us/office/dcounta-function-00232a6d-5a66-4a01-a25b-c1653fda1244 + @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, "DCOUNTA(A4:E10, \"Profit\", A1:F2)", 1); + assertDouble(fe, cell, "DCOUNTA(A4:E10, 5, A1:F2)", 1); + assertDouble(fe, cell, "DCOUNTA(A4:E10, , A1:F2)", 1); + assertDouble(fe, cell, "DCOUNTA(A4:E10, \"Profit\", A1:F2)", 1); + assertDouble(fe, cell, "DCOUNTA(A4:E10, \"Profit\", A1:F3)", 3); + assertDouble(fe, cell, "DCOUNTA(A4:E10, \"Age\", A1:F3)", 2); + } + } + + private HSSFWorkbook initWorkbook1() { + HSSFWorkbook wb = new HSSFWorkbook(); + HSSFSheet sheet = wb.createSheet(); + addRow(sheet, 0, "Tree", "Height", "Age", "Yield", "Profit", "Height"); + addRow(sheet, 1, "=Apple", ">10", null, null, null, "<16"); + addRow(sheet, 2, "=Pear"); + addRow(sheet, 3, "Tree", "Height", "Age", "Yield", "Profit"); + addRow(sheet, 4, "Apple", 18, 20, 14, 105); + addRow(sheet, 5, "Pear", 12, 12, 10, 96); + addRow(sheet, 6, "Cherry", 13, 14, 9, 105); + addRow(sheet, 7, "Apple", 14, null, 10, 75); + addRow(sheet, 8, "Pear", 9, 8, 8, "$77"); + addRow(sheet, 9, "Apple", 8, 9, 6, 45); + return wb; + } +} -- 2.39.5