From 5b62dcd379c76bf09e798c7476f3fa9657225bb7 Mon Sep 17 00:00:00 2001
From: Dominik Stadler <centic@apache.org>
Date: Wed, 26 Dec 2018 13:28:17 +0000
Subject: [PATCH] Bug 62839: Adjust MathX.floor() to handle negative values the
 same way as Excel/LibreOffice

git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1849764 13f79535-47bb-0310-9956-ffa450edef68
---
 .../poi/ss/formula/functions/MathX.java       | 57 +++++++------------
 .../poi/ss/formula/functions/TestMathX.java   |  6 ++
 .../poi/ss/usermodel/TestDataFormatter.java   | 13 +++++
 3 files changed, 40 insertions(+), 36 deletions(-)

diff --git a/src/java/org/apache/poi/ss/formula/functions/MathX.java b/src/java/org/apache/poi/ss/formula/functions/MathX.java
index 9cd0f2ad06..c88923faf6 100644
--- a/src/java/org/apache/poi/ss/formula/functions/MathX.java
+++ b/src/java/org/apache/poi/ss/formula/functions/MathX.java
@@ -125,22 +125,21 @@ final class MathX {
     public static double average(double[] values) {
         double ave = 0;
         double sum = 0;
-        for (int i=0, iSize=values.length; i<iSize; i++) {
-            sum += values[i];
+        for (double value : values) {
+            sum += value;
         }
         ave = sum / values.length;
         return ave;
     }
 
-
     /**
      * sum of all values
      * @param values
      */
     public static double sum(double[] values) {
         double sum = 0;
-        for (int i=0, iSize=values.length; i<iSize; i++) {
-            sum += values[i];
+        for (double value : values) {
+            sum += value;
         }
         return sum;
     }
@@ -151,8 +150,8 @@ final class MathX {
      */
     public static double sumsq(double[] values) {
         double sumsq = 0;
-        for (int i=0, iSize=values.length; i<iSize; i++) {
-            sumsq += values[i]*values[i];
+        for (double value : values) {
+            sumsq += value * value;
         }
         return sumsq;
     }
@@ -166,8 +165,8 @@ final class MathX {
         double product = 0;
         if (values!=null && values.length > 0) {
             product = 1;
-            for (int i=0, iSize=values.length; i<iSize; i++) {
-                product *= values[i];
+            for (double value : values) {
+                product *= value;
             }
         }
         return product;
@@ -180,8 +179,8 @@ final class MathX {
      */
     public static double min(double[] values) {
         double min = Double.POSITIVE_INFINITY;
-        for (int i=0, iSize=values.length; i<iSize; i++) {
-            min = Math.min(min, values[i]);
+        for (double value : values) {
+            min = Math.min(min, value);
         }
         return min;
     }
@@ -193,8 +192,8 @@ final class MathX {
      */
     public static double max(double[] values) {
         double max = Double.NEGATIVE_INFINITY;
-        for (int i=0, iSize=values.length; i<iSize; i++) {
-            max = Math.max(max, values[i]);
+        for (double value : values) {
+            max = Math.max(max, value);
         }
         return max;
     }
@@ -215,16 +214,11 @@ final class MathX {
      * @param s
      */
     public static double floor(double n, double s) {
-        double f;
-
-        if ((n<0 && s>0) || (n>0 && s<0) || (s==0 && n!=0)) {
-            f = Double.NaN;
-        }
-        else {
-            f = (n==0 || s==0) ? 0 : Math.floor(n/s) * s;
+        if (s==0 && n!=0) {
+            return Double.NaN;
+        } else {
+            return (n==0 || s==0) ? 0 : Math.floor(n/s) * s;
         }
-
-        return f;
     }
 
     /**
@@ -243,15 +237,11 @@ final class MathX {
      * @param s
      */
     public static double ceiling(double n, double s) {
-        double c;
-
         if (n>0 && s<0) {
-            c = Double.NaN;
+            return Double.NaN;
         } else {
-            c = (n == 0 || s == 0) ? 0 : Math.ceil(n/s) * s;
+            return (n == 0 || s == 0) ? 0 : Math.ceil(n/s) * s;
         }
-
-        return c;
     }
 
     /**
@@ -300,19 +290,15 @@ final class MathX {
      * @param d
      */
     public static double mod(double n, double d) {
-        double result = 0;
-
         if (d == 0) {
-            result = Double.NaN;
+            return Double.NaN;
         }
         else if (sign(n) == sign(d)) {
-            result = n % d;
+            return n % d;
         }
         else {
-            result = ((n % d) + d) % d;
+            return ((n % d) + d) % d;
         }
-
-        return result;
     }
 
     /**
@@ -396,5 +382,4 @@ final class MathX {
 
         return d;
     }
-
 }
diff --git a/src/testcases/org/apache/poi/ss/formula/functions/TestMathX.java b/src/testcases/org/apache/poi/ss/formula/functions/TestMathX.java
index 6c842106ff..57d05e5fec 100644
--- a/src/testcases/org/apache/poi/ss/formula/functions/TestMathX.java
+++ b/src/testcases/org/apache/poi/ss/formula/functions/TestMathX.java
@@ -27,6 +27,7 @@ import org.apache.poi.ss.formula.functions.XYNumericFunction.Accumulator;
  * @author Amol S. Deshmukh &lt; amolweb at ya hoo dot com &gt;
  *  
  */
+@SuppressWarnings("ConstantConditions")
 public class TestMathX extends AbstractNumericTestCase {
 
     public void testAcosh() {
@@ -1000,5 +1001,10 @@ public class TestMathX extends AbstractNumericTestCase {
         assertEquals("floor ", 1.5, MathX.floor(d, s));
         d = 0.234; s = 0.01;
         assertEquals("floor ", 0.23, MathX.floor(d, s));
+
+        // see bug 62839
+        d = -123;
+        s = 10;
+        assertEquals("floor ", -130, MathX.floor(d, s));
     }
 }
diff --git a/src/testcases/org/apache/poi/ss/usermodel/TestDataFormatter.java b/src/testcases/org/apache/poi/ss/usermodel/TestDataFormatter.java
index b642de340e..a4aa5f4ea7 100644
--- a/src/testcases/org/apache/poi/ss/usermodel/TestDataFormatter.java
+++ b/src/testcases/org/apache/poi/ss/usermodel/TestDataFormatter.java
@@ -925,4 +925,17 @@ public class TestDataFormatter {
         assertEquals("4,33 " + euro, df.formatRawCellContents(4.33, 178, formatString));
         assertEquals("1.234,33 " + euro, df.formatRawCellContents(1234.33, 178, formatString));
     }
+
+    @Test
+    public void testBug62839() {
+        Workbook wb = new HSSFWorkbook();
+        Sheet sheet = wb.createSheet();
+        Row row = sheet.createRow(0);
+        Cell cell = row.createCell(0);
+        cell.setCellFormula("FLOOR(-123,10)");
+        DataFormatter df = new DataFormatter(Locale.GERMANY);
+
+        String value = df.formatCellValue(cell, wb.getCreationHelper().createFormulaEvaluator());
+        assertEquals("-130", value);
+    }
 }
\ No newline at end of file
-- 
2.39.5