[bug-66097] support FLOOR.PRECISE function

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

5 files changed, 154 insertions, 0 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 399762db81..5dc84f1462 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
@@ -114,6 +114,7 @@ public final class AnalysisToolPak implements UDFFinder {
         r(m, "ERFC", null);
         r(m, "FACTDOUBLE", FactDouble.instance);
         r(m, "FLOOR.MATH", FloorMath.instance);
+        r(m, "FLOOR.PRECISE", FloorPrecise.instance);
         r(m, "FVSCHEDULE", null);
         r(m, "GCD", Gcd.instance);
         r(m, "GESTEP", null);
diff --git a/poi/src/main/java/org/apache/poi/ss/formula/functions/Days.java b/poi/src/main/java/org/apache/poi/ss/formula/functions/Days.java
index cce8180890..34fbf133c4 100644
--- a/poi/src/main/java/org/apache/poi/ss/formula/functions/Days.java
+++ b/poi/src/main/java/org/apache/poi/ss/formula/functions/Days.java
@@ -21,6 +21,7 @@ import java.time.temporal.ChronoUnit;
 import java.util.Date;
 
 import org.apache.poi.ss.formula.OperationEvaluationContext;
+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.OperandResolver;
@@ -54,6 +55,9 @@ public class Days implements FreeRefFunction {
 
     @Override
     public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) {
+        if (args.length != 2) {
+            return ErrorEval.VALUE_INVALID;
+        }
         return evaluate(ec.getRowIndex(), ec.getColumnIndex(), args[0], args[1]);
     }
 
diff --git a/poi/src/main/java/org/apache/poi/ss/formula/functions/FloorPrecise.java b/poi/src/main/java/org/apache/poi/ss/formula/functions/FloorPrecise.java
new file mode 100644
index 0000000000..83b19ea8b7
--- /dev/null
+++ b/poi/src/main/java/org/apache/poi/ss/formula/functions/FloorPrecise.java
@@ -0,0 +1,73 @@
+/* ====================================================================
+   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.OperationEvaluationContext;
+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.OperandResolver;
+import org.apache.poi.ss.formula.eval.ValueEval;
+
+import java.math.RoundingMode;
+
+import static org.apache.poi.ss.formula.functions.MathX.scaledRoundUsingBigDecimal;
+
+/**
+ * Implementation for Excel FLOOR.PRECISE() function.
+ * <ul>
+ *   <li>https://support.microsoft.com/en-us/office/floor-precise-function-f769b468-1452-4617-8dc3-02f842a0702e</li>
+ * </ul>
+ */
+public final class FloorPrecise implements FreeRefFunction {
+
+    public static final FloorPrecise instance = new FloorPrecise();
+
+    private FloorPrecise() {}
+
+    @Override
+    public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) {
+        if (args.length == 0) {
+            return ErrorEval.VALUE_INVALID;
+        }
+        try {
+            Double xval = evaluateValue(args[0], ec.getRowIndex(), ec.getColumnIndex());
+            if (xval == null) {
+                return ErrorEval.VALUE_INVALID;
+            }
+            double multiplier = 1.0;
+            if (args.length > 1) {
+                Double arg1Val = evaluateValue(args[1], ec.getRowIndex(), ec.getColumnIndex());
+                multiplier = arg1Val != null ? Math.abs(arg1Val.doubleValue()) : 1.0;
+            }
+            if (multiplier != 1.0) {
+                RoundingMode mode = multiplier < 0.0 ? RoundingMode.CEILING : RoundingMode.FLOOR;
+                return new NumberEval(scaledRoundUsingBigDecimal(xval, multiplier, mode));
+            }
+            return new NumberEval(Math.floor(xval));
+        } catch (EvaluationException evaluationException) {
+            return evaluationException.getErrorEval();
+        }
+    }
+
+    private static Double evaluateValue(ValueEval arg, int srcRowIndex, int srcColumnIndex) throws EvaluationException {
+        ValueEval veText = OperandResolver.getSingleValue(arg, srcRowIndex, srcColumnIndex);
+        String strText1 = OperandResolver.coerceValueToString(veText);
+        return OperandResolver.parseDouble(strText1);
+    }
+}
\ No newline at end of file
diff --git a/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDays.java b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDays.java
index 6acfb2b76a..c3b804995a 100644
--- a/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDays.java
+++ b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDays.java
@@ -56,6 +56,7 @@ public class TestDays {
             HSSFCell cell = wb.getSheetAt(0).getRow(0).createCell(12);
             assertError(fe, cell, "DAYS(\"15-XYZ\",\"1-FEB-2021\")", FormulaError.VALUE);
             assertError(fe, cell, "DAYS(\"15-MAR-2021\",\"1-XYZ\")", FormulaError.VALUE);
+            assertError(fe, cell, "DAYS(\"15-MAR-2021\")", FormulaError.VALUE);
         }
     }
 
diff --git a/poi/src/test/java/org/apache/poi/ss/formula/functions/TestFloorPrecise.java b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestFloorPrecise.java
new file mode 100644
index 0000000000..c23a63e07e
--- /dev/null
+++ b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestFloorPrecise.java
@@ -0,0 +1,75 @@
+/* ====================================================================
+   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.HSSFRow;
+import org.apache.poi.hssf.usermodel.HSSFSheet;
+import org.apache.poi.hssf.usermodel.HSSFWorkbook;
+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.assertDouble;
+import static org.apache.poi.ss.util.Utils.assertError;
+
+/**
+ * Tests for {@link FloorPrecise}
+ */
+final class TestFloorPrecise {
+
+    //https://support.microsoft.com/en-us/office/floor-precise-function-f769b468-1452-4617-8dc3-02f842a0702e
+    @Test
+    void testMicrosoftExamples() throws IOException {
+        try (HSSFWorkbook wb = new HSSFWorkbook()) {
+            HSSFSheet sheet = wb.createSheet();
+            HSSFRow row = sheet.createRow(0);
+            HSSFCell cell = row.createCell(0);
+            HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
+            assertDouble(fe, cell, "FLOOR.PRECISE(-3.2,-1)", -4, 0.00000000000001);
+            assertDouble(fe, cell, "FLOOR.PRECISE(3.2,1)", 3, 0.00000000000001);
+            assertDouble(fe, cell, "FLOOR.PRECISE(-3.2,1)", -4, 0.00000000000001);
+            assertDouble(fe, cell, "FLOOR.PRECISE(3.2,-1)", 3, 0.00000000000001);
+            assertDouble(fe, cell, "FLOOR.PRECISE(3.2)", 3, 0.00000000000001);
+        }
+    }
+
+    @Test
+    void testInvalid() throws IOException {
+        try (HSSFWorkbook wb = new HSSFWorkbook()) {
+            HSSFSheet sheet = wb.createSheet();
+            HSSFRow row = sheet.createRow(0);
+            HSSFCell cell = row.createCell(0);
+            HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
+            assertError(fe, cell, "FLOOR.PRECISE()", FormulaError.VALUE);
+        }
+    }
+
+    @Test
+    void testNumError() throws IOException {
+        try (HSSFWorkbook wb = new HSSFWorkbook()) {
+            HSSFSheet sheet = wb.createSheet();
+            HSSFRow row = sheet.createRow(0);
+            HSSFCell cell = row.createCell(0);
+            HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
+            assertError(fe, cell, "FLOOR.PRECISE(\"abc\")", FormulaError.VALUE);
+        }
+    }
+}