[bug-66095] support POISSON.DIST function (POISSON already supported)

git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1901371 13f79535-47bb-0310-9956-ffa450edef68

3 files changed, 97 insertions, 6 deletions

diff --git a/poi/src/main/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java b/poi/src/main/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java
index b6830ea4fd..7fff9a889d 100644
--- a/poi/src/main/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java
+++ b/poi/src/main/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java
@@ -166,9 +166,10 @@ public final class AnalysisToolPak implements UDFFinder {
         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);
diff --git a/poi/src/main/java/org/apache/poi/ss/formula/functions/Poisson.java b/poi/src/main/java/org/apache/poi/ss/formula/functions/Poisson.java
index 8e67a6c5d4..081afd8551 100644
--- a/poi/src/main/java/org/apache/poi/ss/formula/functions/Poisson.java
+++ b/poi/src/main/java/org/apache/poi/ss/formula/functions/Poisson.java
@@ -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
diff --git a/poi/src/test/java/org/apache/poi/ss/formula/functions/TestPoissonDist.java b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestPoissonDist.java
new file mode 100644
index 0000000000..0a3ccc980e
--- /dev/null
+++ b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestPoissonDist.java
@@ -0,0 +1,77 @@
+/* ====================================================================
+   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.apache.poi.ss.formula.OperationEvaluationContext;
+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.apache.poi.ss.util.Utils.assertError;
+
+/**
+ * Tests for {@link Poisson}
+ */
+final class TestPoissonDist {
+
+    private static final OperationEvaluationContext ec = new OperationEvaluationContext(null, null, 0, 0, 0, null);
+
+    //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, 2);
+            addRow(sheet, 2, 5);
+            HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
+            HSSFCell cell = wb.getSheetAt(0).getRow(0).createCell(100);
+            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);
+        }
+    }
+
+    @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);
+        }
+    }
+
+}