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/28 19:45:20 UTC
svn commit: r1901371 - in /poi/trunk/poi/src: main/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java main/java/org/apache/poi/ss/formula/functions/Poisson.java test/java/org/apache/poi/ss/formula/functions/TestPoissonDist.java
Author: fanningpj
Date: Sat May 28 19:45:19 2022
New Revision: 1901371
URL: http://svn.apache.org/viewvc?rev=1901371&view=rev
Log:
[bug-66095] support POISSON.DIST function (POISSON already supported)
Added:
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestPoissonDist.java
- copied, changed from r1901195, poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestNormDist.java
Modified:
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/Poisson.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=1901371&r1=1901370&r2=1901371&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 Sat May 28 19:45:19 2022
@@ -166,9 +166,10 @@ public final class AnalysisToolPak imple
r(m, "ODDFYIELD", null);
r(m, "ODDLPRICE", null);
r(m, "ODDLYIELD", null);
- r(m, "PRICE", null);
r(m, "PERCENTRANK.EXC", PercentRankExcFunction.instance);
r(m, "PERCENTRANK.INC", PercentRankIncFunction.instance);
+ r(m, "POISSON.DIST", Poisson.instance);
+ r(m, "PRICE", null);
r(m, "PRICEDISC", null);
r(m, "PRICEMAT", null);
r(m, "QUOTIENT", Quotient.instance);
Modified: poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/Poisson.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/Poisson.java?rev=1901371&r1=1901370&r2=1901371&view=diff
==============================================================================
--- poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/Poisson.java (original)
+++ poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/Poisson.java Sat May 28 19:45:19 2022
@@ -17,25 +17,28 @@
package org.apache.poi.ss.formula.functions;
+import org.apache.poi.ss.formula.OperationEvaluationContext;
import org.apache.poi.ss.formula.eval.BoolEval;
import org.apache.poi.ss.formula.eval.ErrorEval;
import org.apache.poi.ss.formula.eval.EvaluationException;
import org.apache.poi.ss.formula.eval.NumberEval;
import org.apache.poi.ss.formula.eval.ValueEval;
-public class Poisson {
+public class Poisson implements FreeRefFunction {
- private static final double DEFAULT_RETURN_RESULT =1;
+ public static final Poisson instance = new Poisson();
+
+ private static final double DEFAULT_RETURN_RESULT = 1;
/** All long-representable factorials */
private static final long[] FACTORIALS = {
- 1L, 1L, 2L,
+ 1L, 1L, 2L,
6L, 24L, 120L,
720L, 5040L, 40320L,
362880L, 3628800L, 39916800L,
479001600L, 6227020800L, 87178291200L,
1307674368000L, 20922789888000L, 355687428096000L,
- 6402373705728000L, 121645100408832000L, 2432902008176640000L };
+ 6402373705728000L, 121645100408832000L, 2432902008176640000L };
/**
* This checks is x = 0 and the mean = 0.
@@ -77,6 +80,11 @@ public class Poisson {
return FACTORIALS[n];
}
+ @Override
+ public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) {
+ return evaluate(args, ec.getRowIndex(), ec.getColumnIndex());
+ }
+
public static ValueEval evaluate(ValueEval[] args, int srcRowIndex, int srcColumnIndex) {
if (args.length != 3) {
return ErrorEval.VALUE_INVALID;
@@ -87,7 +95,12 @@ public class Poisson {
try {
// arguments/result for this function
- double x = NumericFunction.singleOperandEvaluate(arg0, srcRowIndex, srcColumnIndex);
+ double x;
+ try {
+ x = NumericFunction.singleOperandEvaluate(arg0, srcRowIndex, srcColumnIndex);
+ } catch (EvaluationException ee) {
+ return ErrorEval.VALUE_INVALID;
+ }
double mean = NumericFunction.singleOperandEvaluate(arg1, srcRowIndex, srcColumnIndex);
// check for default result : excel implementation for 0,0
Copied: poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestPoissonDist.java (from r1901195, poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestNormDist.java)
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestPoissonDist.java?p2=poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestPoissonDist.java&p1=poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestNormDist.java&r1=1901195&r2=1901371&rev=1901371&view=diff
==============================================================================
--- poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestNormDist.java (original)
+++ poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestPoissonDist.java Sat May 28 19:45:19 2022
@@ -22,84 +22,56 @@ import org.apache.poi.hssf.usermodel.HSS
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.formula.OperationEvaluationContext;
-import org.apache.poi.ss.formula.eval.BlankEval;
-import org.apache.poi.ss.formula.eval.BoolEval;
-import org.apache.poi.ss.formula.eval.ErrorEval;
-import org.apache.poi.ss.formula.eval.NumberEval;
-import org.apache.poi.ss.formula.eval.StringEval;
-import org.apache.poi.ss.formula.eval.ValueEval;
+import org.apache.poi.ss.usermodel.FormulaError;
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;
-import static org.junit.jupiter.api.Assertions.assertEquals;
+import static org.apache.poi.ss.util.Utils.assertError;
/**
- * Tests for {@link NormDist}
+ * Tests for {@link Poisson}
*/
-final class TestNormDist {
+final class TestPoissonDist {
private static final OperationEvaluationContext ec = new OperationEvaluationContext(null, null, 0, 0, 0, null);
- @Test
- void testBasic() {
- confirmValue("42", "40", "1.5", true, 0.908788780274132);
- confirmValue("42", "40", "1.5", false, 0.109340049783996);
- }
-
- @Test
- void testInvalid() {
- confirmInvalidError("A1","B2","C2", false);
- }
-
- @Test
- void testNumError() {
- confirmNumError("42","40","0", false);
- confirmNumError("42","40","0", true);
- confirmNumError("42","40","-0.1", false);
- confirmNumError("42","40","-0.1", true);
- }
-
- //https://support.microsoft.com/en-us/office/normdist-function-126db625-c53e-4591-9a22-c9ff422d6d58
- //https://support.microsoft.com/en-us/office/norm-dist-function-edb1cc14-a21c-4e53-839d-8082074c9f8d
+ //https://support.microsoft.com/en-us/office/poisson-dist-function-8fe148ff-39a2-46cb-abf3-7772695d9636
@Test
void testMicrosoftExample1() throws IOException {
try (HSSFWorkbook wb = new HSSFWorkbook()) {
HSSFSheet sheet = wb.createSheet();
addRow(sheet, 0, "Data", "Description");
- addRow(sheet, 1, 42, "Value for which you want the distribution");
- addRow(sheet, 2, 40, "Arithmetic mean of the distribution");
- addRow(sheet, 3, 1.5, "Standard deviation of the distribution");
+ addRow(sheet, 1, 2);
+ addRow(sheet, 2, 5);
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
HSSFCell cell = wb.getSheetAt(0).getRow(0).createCell(100);
- assertDouble(fe, cell, "NORMDIST(A2,A3,A4,TRUE)", 0.908788780274132, 0.00000000000001);
- assertDouble(fe, cell, "NORM.DIST(A2,A3,A4,TRUE)", 0.908788780274132, 0.00000000000001);
+ assertDouble(fe, cell, "POISSON.DIST(A2,A3,TRUE)", 0.12465201948308113, 0.00000000000001);
+ assertDouble(fe, cell, "POISSON.DIST(A2,A3,FALSE)", 0.08422433748856833, 0.00000000000001);
+ assertDouble(fe, cell, "POISSON(A2,A3,TRUE)", 0.12465201948308113, 0.00000000000001);
+ assertDouble(fe, cell, "POISSON(A2,A3,FALSE)", 0.08422433748856833, 0.00000000000001);
+ assertDouble(fe, cell, "POISSON(2.1,5,FALSE)", 0.08422433748856833, 0.00000000000001);
+ assertDouble(fe, cell, "POISSON(2.9,5,FALSE)", 0.08422433748856833, 0.00000000000001);
}
}
- private static ValueEval invokeValue(String number1, String number2, String number3, boolean cumulative) {
- ValueEval[] args = new ValueEval[] { new StringEval(number1), new StringEval(number2), new StringEval(number3), BoolEval.valueOf(cumulative)};
- return NormDist.instance.evaluate(args, ec);
- }
-
- private static void confirmValue(String number1, String number2, String number3, boolean cumulative, double expected) {
- ValueEval result = invokeValue(number1, number2, number3, cumulative);
- assertEquals(NumberEval.class, result.getClass());
- assertEquals(expected, ((NumberEval) result).getNumberValue(), 0.00000000000001);
- }
-
- private static void confirmInvalidError(String number1, String number2, String number3, boolean cumulative) {
- ValueEval result = invokeValue(number1, number2, number3, cumulative);
- assertEquals(ErrorEval.class, result.getClass());
- assertEquals(ErrorEval.VALUE_INVALID, result);
- }
-
- private static void confirmNumError(String number1, String number2, String number3, boolean cumulative) {
- ValueEval result = invokeValue(number1, number2, number3, cumulative);
- assertEquals(ErrorEval.class, result.getClass());
- assertEquals(ErrorEval.NUM_ERROR, result);
+ @Test
+ void testInvalid() throws IOException {
+ try (HSSFWorkbook wb = new HSSFWorkbook()) {
+ HSSFSheet sheet = wb.createSheet();
+ addRow(sheet, 0, "Data", "Description");
+ addRow(sheet, 1, 2);
+ addRow(sheet, 2, 5);
+ HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
+ HSSFCell cell = wb.getSheetAt(0).getRow(0).createCell(100);
+ assertError(fe, cell, "POISSON.DIST(A2,A3)", FormulaError.VALUE);
+ assertError(fe, cell, "POISSON.DIST(\"abc\",A3,TRUE)", FormulaError.VALUE);
+ assertError(fe, cell, "POISSON.DIST(A2,\"A3\",TRUE)", FormulaError.VALUE);
+ assertError(fe, cell, "POISSON.DIST(-1,A3,TRUE)", FormulaError.NUM);
+ assertError(fe, cell, "POISSON.DIST(A2,-5,TRUE)", FormulaError.NUM);
+ }
}
}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org