From c926c47091185d92fe7d26fbfd3e3236376570f5 Mon Sep 17 00:00:00 2001 From: PJ Fanning Date: Thu, 26 May 2022 11:07:41 +0000 Subject: [PATCH] support DVARP and DSTDEVP git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1901280 13f79535-47bb-0310-9956-ffa450edef68 --- .../poi/ss/formula/eval/FunctionEval.java | 4 +- .../poi/ss/formula/functions/DStarRunner.java | 4 ++ .../poi/ss/formula/functions/DStdevp.java | 53 +++++++++++++++++++ .../poi/ss/formula/functions/DVarp.java | 53 +++++++++++++++++++ .../poi/ss/formula/functions/TestDStdev.java | 12 ++++- .../poi/ss/formula/functions/TestDVar.java | 12 ++++- 6 files changed, 134 insertions(+), 4 deletions(-) create mode 100644 poi/src/main/java/org/apache/poi/ss/formula/functions/DStdevp.java create mode 100644 poi/src/main/java/org/apache/poi/ss/formula/functions/DVarp.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 992ad35c62..32750a6b0e 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 @@ -206,8 +206,8 @@ public final class FunctionEval { retval[193] = AggregateFunction.STDEVP; retval[194] = AggregateFunction.VARP; - // 195: DSTDEVP - // 196: DVARP + retval[195] = new DStarRunner(DStarRunner.DStarAlgorithmEnum.DSTDEVP); + retval[196] = new DStarRunner(DStarRunner.DStarAlgorithmEnum.DVARP); retval[197] = NumericFunction.TRUNC; retval[198] = LogicalFunction.ISLOGICAL; // 199: DCOUNTA 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 e829216873..5a3fbfbdea 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 @@ -63,8 +63,12 @@ public final class DStarRunner implements Function3Arg { DAVERAGE(DAverage::new), /** @see DStdev */ DSTDEV(DStdev::new), + /** @see DStdevp */ + DSTDEVP(DStdevp::new), /** @see DVar */ DVAR(DVar::new), + /** @see DVarp */ + DVARP(DVarp::new), /** @see DProduct */ DPRODUCT(DProduct::new), ; diff --git a/poi/src/main/java/org/apache/poi/ss/formula/functions/DStdevp.java b/poi/src/main/java/org/apache/poi/ss/formula/functions/DStdevp.java new file mode 100644 index 0000000000..08688e0254 --- /dev/null +++ b/poi/src/main/java/org/apache/poi/ss/formula/functions/DStdevp.java @@ -0,0 +1,53 @@ +/* ==================================================================== + 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.NumberEval; +import org.apache.poi.ss.formula.eval.NumericValueEval; +import org.apache.poi.ss.formula.eval.ValueEval; +import org.apache.poi.ss.util.NumberToTextConverter; + +import java.math.BigDecimal; +import java.util.ArrayList; + +/** + * Implementation of the DStdevp function: + * Gets the standard deviation value of a column in an area with given conditions. + */ +public final class DStdevp implements IDStarAlgorithm { + private final ArrayList values = new ArrayList<>(); + + @Override + public boolean processMatch(ValueEval eval) { + if (eval instanceof NumericValueEval) { + values.add((NumericValueEval) eval); + } + return true; + } + + @Override + public ValueEval getResult() { + final double[] array = new double[values.size()]; + int pos = 0; + for (NumericValueEval d : values) { + array[pos++] = d.getNumberValue(); + } + final double stdev = StatsLib.stdevp(array); + return new NumberEval(new BigDecimal(NumberToTextConverter.toText(stdev)).doubleValue()); + } +} diff --git a/poi/src/main/java/org/apache/poi/ss/formula/functions/DVarp.java b/poi/src/main/java/org/apache/poi/ss/formula/functions/DVarp.java new file mode 100644 index 0000000000..ae3ad770bb --- /dev/null +++ b/poi/src/main/java/org/apache/poi/ss/formula/functions/DVarp.java @@ -0,0 +1,53 @@ +/* ==================================================================== + 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.NumberEval; +import org.apache.poi.ss.formula.eval.NumericValueEval; +import org.apache.poi.ss.formula.eval.ValueEval; +import org.apache.poi.ss.util.NumberToTextConverter; + +import java.math.BigDecimal; +import java.util.ArrayList; + +/** + * Implementation of the DVarp function: + * Gets the variance value of a column in an area with given conditions. + */ +public final class DVarp implements IDStarAlgorithm { + private final ArrayList values = new ArrayList<>(); + + @Override + public boolean processMatch(ValueEval eval) { + if (eval instanceof NumericValueEval) { + values.add((NumericValueEval) eval); + } + return true; + } + + @Override + public ValueEval getResult() { + final double[] array = new double[values.size()]; + int pos = 0; + for (NumericValueEval d : values) { + array[pos++] = d.getNumberValue(); + } + final double var = StatsLib.varp(array); + return new NumberEval(new BigDecimal(NumberToTextConverter.toText(var)).doubleValue()); + } +} diff --git a/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDStdev.java b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDStdev.java index 48393dc980..df4eb99d5c 100644 --- a/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDStdev.java +++ b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDStdev.java @@ -29,7 +29,7 @@ import static org.apache.poi.ss.util.Utils.addRow; import static org.apache.poi.ss.util.Utils.assertDouble; /** - * Testcase for function DSTDEV() + * Testcase for function DSTDEV() and DSTDEVP() */ public class TestDStdev { @@ -43,6 +43,16 @@ public class TestDStdev { } } + //https://support.microsoft.com/en-us/office/dstdevp-function-04b78995-da03-4813-bbd9-d74fd0f5d94b + @Test + void testDSTDEVPMicrosoftExample1() throws IOException { + try (HSSFWorkbook wb = initWorkbook1()) { + HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb); + HSSFCell cell = wb.getSheetAt(0).getRow(0).createCell(12); + assertDouble(fe, cell, "DSTDEVP(A5:E11, \"Yield\", A1:A3)", 2.65329983228432, 0.0000000001); + } + } + private HSSFWorkbook initWorkbook1() { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet(); diff --git a/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDVar.java b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDVar.java index 4915173a43..6c8a86bf28 100644 --- a/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDVar.java +++ b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDVar.java @@ -29,7 +29,7 @@ import static org.apache.poi.ss.util.Utils.addRow; import static org.apache.poi.ss.util.Utils.assertDouble; /** - * Testcase for function DVAR() + * Testcase for function DVAR() and DVARP() */ public class TestDVar { @@ -43,6 +43,16 @@ public class TestDVar { } } + //https://support.microsoft.com/en-us/office/dvarp-function-eb0ba387-9cb7-45c8-81e9-0394912502fc + @Test + void testDVARPMicrosoftExample1() throws IOException { + try (HSSFWorkbook wb = initWorkbook1()) { + HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb); + HSSFCell cell = wb.getSheetAt(0).getRow(0).createCell(12); + assertDouble(fe, cell, "DVARP(A4:E10, \"Yield\", A1:A3)", 7.04, 0.0000000001); + } + } + private HSSFWorkbook initWorkbook1() { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet(); -- 2.39.5