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 2022/05/25 20:12:18 UTC
svn commit: r1901258 - in /poi/trunk/poi/src: main/java/org/apache/poi/ss/formula/eval/ main/java/org/apache/poi/ss/formula/functions/ test/java/org/apache/poi/ss/formula/functions/
Author: fanningpj
Date: Wed May 25 20:12:17 2022
New Revision: 1901258
URL: http://svn.apache.org/viewvc?rev=1901258&view=rev
Log:
add DSTDEV function support
Added:
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DStdev.java (with props)
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDStdev.java (with props)
Modified:
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/eval/FunctionEval.java
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DAverage.java
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DCount.java
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DStarRunner.java
Modified: poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/eval/FunctionEval.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/eval/FunctionEval.java?rev=1901258&r1=1901257&r2=1901258&view=diff
==============================================================================
--- poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/eval/FunctionEval.java (original)
+++ poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/eval/FunctionEval.java Wed May 25 20:12:17 2022
@@ -110,7 +110,7 @@ public final class FunctionEval {
retval[42] = new DStarRunner(DStarRunner.DStarAlgorithmEnum.DAVERAGE);
retval[43] = new DStarRunner(DStarRunner.DStarAlgorithmEnum.DMIN);
retval[44] = new DStarRunner(DStarRunner.DStarAlgorithmEnum.DMAX);
- // 45: DSTDEV
+ retval[45] = new DStarRunner(DStarRunner.DStarAlgorithmEnum.DSTDEV);
retval[46] = AggregateFunction.VAR;
// 47: DVAR
retval[48] = TextFunction.TEXT;
Modified: poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DAverage.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DAverage.java?rev=1901258&r1=1901257&r2=1901258&view=diff
==============================================================================
--- poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DAverage.java (original)
+++ poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DAverage.java Wed May 25 20:12:17 2022
@@ -29,7 +29,7 @@ import java.math.MathContext;
* Gets the average value of a column in an area with given conditions.
*/
public final class DAverage implements IDStarAlgorithm {
- private int count;
+ private long count;
private double total;
@Override
@@ -43,10 +43,14 @@ public final class DAverage implements I
@Override
public ValueEval getResult() {
- return count == 0 ? NumberEval.ZERO : new NumberEval(getAverage());
+ return count == 0 ? NumberEval.ZERO : new NumberEval(divide());
}
- private double getAverage() {
+ private double divide() {
+ return divide(total, count);
+ }
+
+ private static double divide(final double total, final long count) {
return BigDecimal.valueOf(total)
.divide(BigDecimal.valueOf(count), MathContext.DECIMAL128)
.doubleValue();
Modified: poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DCount.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DCount.java?rev=1901258&r1=1901257&r2=1901258&view=diff
==============================================================================
--- poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DCount.java (original)
+++ poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DCount.java Wed May 25 20:12:17 2022
@@ -26,7 +26,7 @@ import org.apache.poi.ss.formula.eval.Va
* Counts the value of a column in an area with given conditions.
*/
public final class DCount implements IDStarAlgorithm {
- private int count;
+ private long count;
@Override
public boolean processMatch(ValueEval eval) {
Modified: poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DStarRunner.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DStarRunner.java?rev=1901258&r1=1901257&r2=1901258&view=diff
==============================================================================
--- poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DStarRunner.java (original)
+++ poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DStarRunner.java Wed May 25 20:12:17 2022
@@ -62,6 +62,8 @@ public final class DStarRunner implement
DCOUNT(DCount::new),
/** @see DAverage */
DAVERAGE(DAverage::new),
+ /** @see DStdev */
+ DSTDEV(DStdev::new),
;
private final Supplier<IDStarAlgorithm> implSupplier;
Added: poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DStdev.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DStdev.java?rev=1901258&view=auto
==============================================================================
--- poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DStdev.java (added)
+++ poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DStdev.java Wed May 25 20:12:17 2022
@@ -0,0 +1,54 @@
+/* ====================================================================
+ 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 DStdev function:
+ * Gets the standard deviation value of a column in an area with given conditions.
+ */
+public final class DStdev implements IDStarAlgorithm {
+ private final ArrayList<Double> values = new ArrayList<>();
+
+ @Override
+ public boolean processMatch(ValueEval eval) {
+ if (eval instanceof NumericValueEval) {
+ final double val = ((NumericValueEval)eval).getNumberValue();
+ values.add(val);
+ }
+ return true;
+ }
+
+ @Override
+ public ValueEval getResult() {
+ final double[] array = new double[values.size()];
+ int pos = 0;
+ for (Double d : values) {
+ array[pos++] = d;
+ }
+ final double stdev = StatsLib.stdev(array);
+ return new NumberEval(new BigDecimal(NumberToTextConverter.toText(stdev)).doubleValue());
+ }
+}
Propchange: poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DStdev.java
------------------------------------------------------------------------------
svn:eol-style = native
Added: poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDStdev.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDStdev.java?rev=1901258&view=auto
==============================================================================
--- poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDStdev.java (added)
+++ poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDStdev.java Wed May 25 20:12:17 2022
@@ -0,0 +1,62 @@
+
+/* ====================================================================
+ 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 DSTDEV()
+ */
+public class TestDStdev {
+
+ //https://support.microsoft.com/en-us/office/dstdev-function-026b8c73-616d-4b5e-b072-241871c4ab96
+ @Test
+ void testMicrosoftExample1() throws IOException {
+ try (HSSFWorkbook wb = initWorkbook1()) {
+ HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
+ HSSFCell cell = wb.getSheetAt(0).getRow(0).createCell(12);
+ assertDouble(fe, cell, "DSTDEV(A5:E11, \"Yield\", A1:A3)", 2.96647939483827, 0.0000000001);
+ }
+ }
+
+ 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);
+ addRow(sheet, 4, "Tree", "Height", "Age", "Yield", "Profit");
+ addRow(sheet, 5, "Apple", 18, 20, 14, 105);
+ addRow(sheet, 6, "Pear", 12, 12, 10, 96);
+ addRow(sheet, 7, "Cherry", 13, 14, 9, 105);
+ addRow(sheet, 8, "Apple", 14, 15, 10, 75);
+ addRow(sheet, 9, "Pear", 9, 8, 8, 77);
+ addRow(sheet, 10, "Apple", 8, 9, 6, 45);
+ return wb;
+ }
+}
Propchange: poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDStdev.java
------------------------------------------------------------------------------
svn:eol-style = native
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org