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/26 14:11:02 UTC
svn commit: r1901291 - in /poi/trunk/poi/src: main/java/org/apache/poi/ss/formula/atp/ test/java/org/apache/poi/ss/formula/functions/
Author: fanningpj
Date: Thu May 26 14:11:02 2022
New Revision: 1901291
URL: http://svn.apache.org/viewvc?rev=1901291&view=rev
Log:
[bug-66094] add STDEV.S, STDEV.P, VAR.S and VAR.P functions
Added:
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/Stdevp.java (with props)
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/Stdevs.java (with props)
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/Varp.java (with props)
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/Vars.java (with props)
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestStdev.java
- copied, changed from r1901290, poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestStdevp.java
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestVar.java (with props)
Removed:
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestStdevp.java
Modified:
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java
Modified: poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java?rev=1901291&r1=1901290&r2=1901291&view=diff
==============================================================================
--- poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java (original)
+++ poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java Thu May 26 14:11:02 2022
@@ -175,6 +175,8 @@ public final class AnalysisToolPak imple
r(m, "SERIESSUM", null);
r(m, "SINGLE", Single.instance);
r(m, "SQRTPI", Sqrtpi.instance);
+ r(m, "STDEV.S", Stdevs.instance);
+ r(m, "STDEV.P", Stdevp.instance);
r(m, "SUMIFS", Sumifs.instance);
r(m, "SWITCH", Switch.instance);
r(m, "TBILLEQ", null);
@@ -195,6 +197,8 @@ public final class AnalysisToolPak imple
r(m, "YIELD", null);
r(m, "YIELDDISC", null);
r(m, "YIELDMAT", null);
+ r(m, "VAR.S", Vars.instance);
+ r(m, "VAR.P", Varp.instance);
return m;
}
Added: poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/Stdevp.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/Stdevp.java?rev=1901291&view=auto
==============================================================================
--- poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/Stdevp.java (added)
+++ poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/Stdevp.java Thu May 26 14:11:02 2022
@@ -0,0 +1,16 @@
+package org.apache.poi.ss.formula.atp;
+
+import org.apache.poi.ss.formula.OperationEvaluationContext;
+import org.apache.poi.ss.formula.eval.ValueEval;
+import org.apache.poi.ss.formula.functions.AggregateFunction;
+import org.apache.poi.ss.formula.functions.FreeRefFunction;
+
+public class Stdevp implements FreeRefFunction {
+
+ public static final Stdevp instance = new Stdevp();
+
+ @Override
+ public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) {
+ return AggregateFunction.STDEVP.evaluate(args, ec.getRowIndex(), ec.getColumnIndex());
+ }
+}
Propchange: poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/Stdevp.java
------------------------------------------------------------------------------
svn:eol-style = native
Added: poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/Stdevs.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/Stdevs.java?rev=1901291&view=auto
==============================================================================
--- poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/Stdevs.java (added)
+++ poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/Stdevs.java Thu May 26 14:11:02 2022
@@ -0,0 +1,16 @@
+package org.apache.poi.ss.formula.atp;
+
+import org.apache.poi.ss.formula.OperationEvaluationContext;
+import org.apache.poi.ss.formula.eval.ValueEval;
+import org.apache.poi.ss.formula.functions.AggregateFunction;
+import org.apache.poi.ss.formula.functions.FreeRefFunction;
+
+public class Stdevs implements FreeRefFunction {
+
+ public static final Stdevs instance = new Stdevs();
+
+ @Override
+ public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) {
+ return AggregateFunction.STDEV.evaluate(args, ec.getRowIndex(), ec.getColumnIndex());
+ }
+}
Propchange: poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/Stdevs.java
------------------------------------------------------------------------------
svn:eol-style = native
Added: poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/Varp.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/Varp.java?rev=1901291&view=auto
==============================================================================
--- poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/Varp.java (added)
+++ poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/Varp.java Thu May 26 14:11:02 2022
@@ -0,0 +1,16 @@
+package org.apache.poi.ss.formula.atp;
+
+import org.apache.poi.ss.formula.OperationEvaluationContext;
+import org.apache.poi.ss.formula.eval.ValueEval;
+import org.apache.poi.ss.formula.functions.AggregateFunction;
+import org.apache.poi.ss.formula.functions.FreeRefFunction;
+
+public class Varp implements FreeRefFunction {
+
+ public static final Varp instance = new Varp();
+
+ @Override
+ public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) {
+ return AggregateFunction.VARP.evaluate(args, ec.getRowIndex(), ec.getColumnIndex());
+ }
+}
Propchange: poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/Varp.java
------------------------------------------------------------------------------
svn:eol-style = native
Added: poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/Vars.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/Vars.java?rev=1901291&view=auto
==============================================================================
--- poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/Vars.java (added)
+++ poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/Vars.java Thu May 26 14:11:02 2022
@@ -0,0 +1,16 @@
+package org.apache.poi.ss.formula.atp;
+
+import org.apache.poi.ss.formula.OperationEvaluationContext;
+import org.apache.poi.ss.formula.eval.ValueEval;
+import org.apache.poi.ss.formula.functions.AggregateFunction;
+import org.apache.poi.ss.formula.functions.FreeRefFunction;
+
+public class Vars implements FreeRefFunction {
+
+ public static final Vars instance = new Vars();
+
+ @Override
+ public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) {
+ return AggregateFunction.VAR.evaluate(args, ec.getRowIndex(), ec.getColumnIndex());
+ }
+}
Propchange: poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/Vars.java
------------------------------------------------------------------------------
svn:eol-style = native
Copied: poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestStdev.java (from r1901290, poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestStdevp.java)
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestStdev.java?p2=poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestStdev.java&p1=poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestStdevp.java&r1=1901290&r2=1901291&rev=1901291&view=diff
==============================================================================
--- poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestStdevp.java (original)
+++ poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestStdev.java Thu May 26 14:11:02 2022
@@ -29,18 +29,22 @@ import static org.apache.poi.ss.util.Uti
import static org.apache.poi.ss.util.Utils.assertDouble;
/**
- * Testcase for function STDEVP()
+ * Testcase for functions: STDEV(), STDEVP(), STDEV.S(), STDEV.P()
*/
-public class TestStdevp {
+public class TestStdev {
//https://support.microsoft.com/en-us/office/stdevp-function-1f7c1c88-1bec-4422-8242-e9f7dc8bb195
+ //https://support.microsoft.com/en-us/office/stdev-p-function-6e917c05-31a0-496f-ade7-4f4e7462f285
+ //https://support.microsoft.com/en-us/office/stdev-s-function-7d69cf97-0c1f-4acf-be27-f3e83904cc23
@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, "STDEVP(A3:A12)", 26.0545581424825, 0.00000000001);
+ assertDouble(fe, cell, "STDEV.P(A3:A12)", 26.0545581424825, 0.00000000001);
assertDouble(fe, cell, "STDEV(A3:A12)", 27.4639157198435, 0.00000000001);
+ assertDouble(fe, cell, "STDEV.S(A3:A12)", 27.4639157198435, 0.00000000001);
}
}
Added: poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestVar.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestVar.java?rev=1901291&view=auto
==============================================================================
--- poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestVar.java (added)
+++ poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestVar.java Thu May 26 14:11:02 2022
@@ -0,0 +1,67 @@
+
+/* ====================================================================
+ 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 functions: VAR.S(), VAR.P()
+ */
+public class TestVar {
+
+ //https://support.microsoft.com/en-us/office/var-s-function-913633de-136b-449d-813e-65a00b2b990b
+ //https://support.microsoft.com/en-us/office/var-p-function-73d1285c-108c-4843-ba5d-a51f90656f3a
+ @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, "VARP(A3:A12)", 678.84, 0.00000000001);
+ assertDouble(fe, cell, "VAR.P(A3:A12)", 678.84, 0.00000000001);
+ assertDouble(fe, cell, "VAR(A3:A12)", 754.27, 0.005);
+ assertDouble(fe, cell, "VAR.S(A3:A12)", 754.27, 0.005);
+ }
+ }
+
+ private HSSFWorkbook initWorkbook1() {
+ HSSFWorkbook wb = new HSSFWorkbook();
+ HSSFSheet sheet = wb.createSheet();
+ addRow(sheet, 0, "Data");
+ addRow(sheet, 1, "Strength");
+ addRow(sheet, 2, 1345);
+ addRow(sheet, 3, 1301);
+ addRow(sheet, 4, 1368);
+ addRow(sheet, 5, 1322);
+ addRow(sheet, 6, 1310);
+ addRow(sheet, 7, 1370);
+ addRow(sheet, 8, 1318);
+ addRow(sheet, 9, 1350);
+ addRow(sheet, 10, 1303);
+ addRow(sheet, 11, 1299);
+ return wb;
+ }
+}
Propchange: poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestVar.java
------------------------------------------------------------------------------
svn:eol-style = native
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org