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 2026/09/18 11:43:52 UTC
(poi) branch trunk updated: QUOTIENT, FACT and ROUND/ROUNDUP/ROUNDDOWN/TRUNC: no IllegalArgumentException for arguments outside the int range (#1278)
This is an automated email from the ASF dual-hosted git repository.
pjfanning pushed a commit to branch trunk
in repository https://gitbox.apache.org/repos/asf/poi.git
The following commit(s) were added to refs/heads/trunk by this push:
new 92a57d296f QUOTIENT, FACT and ROUND/ROUNDUP/ROUNDDOWN/TRUNC: no IllegalArgumentException for arguments outside the int range (#1278)
92a57d296f is described below
commit 92a57d296f67f196e54aeb8f0945b5e9a0c6d4dd
Author: PJ Fanning <pj...@users.noreply.github.com>
AuthorDate: Fri Sep 18 12:43:47 2026 +0100
QUOTIENT, FACT and ROUND/ROUNDUP/ROUNDDOWN/TRUNC: no IllegalArgumentException for arguments outside the int range (#1278)
These functions converted a double argument with MathUtil.safeDoubleToInt,
which throws IllegalArgumentException for values outside the int range. The
exception escaped the evaluator instead of becoming an error value:
* QUOTIENT(5E9,2) threw; Excel returns 2500000000. The quotient is now
truncated towards zero as a double, and an infinite quotient is #NUM!.
* FACT(1E10) threw; Excel returns #NUM!. factorial(double) now handles the
range check itself (>= 171 is infinity, hence #NUM!; <= -1 is NaN).
* ROUND(1.5,1E10), TRUNC(1.5,-1E10) etc. threw. Digit counts within the int
range but in the hundreds of millions were worse: BigDecimal.setScale built
a number with that many digits and ROUND(1.5,100000000) ran for minutes.
Digit counts are now clamped to +/-400, beyond which rounding a double
(15 significant digits, decimal exponent within +/-324) cannot change it.
Co-authored-by: Claude Opus 5 (1M context) <no...@anthropic.com>
---
.../org/apache/poi/ss/formula/functions/MathX.java | 33 ++++++++++++++----
.../apache/poi/ss/formula/functions/Quotient.java | 9 +++--
.../apache/poi/ss/formula/functions/TestMathX.java | 19 ++++++++++
.../ss/formula/functions/TestNumericFunction.java | 40 ++++++++++++++++++++++
.../poi/ss/formula/functions/TestQuotient.java | 10 ++++++
5 files changed, 103 insertions(+), 8 deletions(-)
diff --git a/poi/src/main/java/org/apache/poi/ss/formula/functions/MathX.java b/poi/src/main/java/org/apache/poi/ss/formula/functions/MathX.java
index 9d90dd8be3..4573e60fe9 100644
--- a/poi/src/main/java/org/apache/poi/ss/formula/functions/MathX.java
+++ b/poi/src/main/java/org/apache/poi/ss/formula/functions/MathX.java
@@ -20,7 +20,6 @@ package org.apache.poi.ss.formula.functions;
import org.apache.poi.ss.util.ExcelArithmetic;
import org.apache.poi.ss.util.NumberToTextConverter;
import org.apache.poi.util.Internal;
-import org.apache.poi.util.MathUtil;
import java.math.BigDecimal;
import java.math.MathContext;
@@ -58,7 +57,7 @@ final class MathX {
}
public static double round(double n, double p) {
- return round(n, MathUtil.safeDoubleToInt(p));
+ return round(n, clampDigits(p));
}
@@ -83,7 +82,7 @@ final class MathX {
}
public static double roundUp(double n, double p) {
- return roundUp(n, MathUtil.safeDoubleToInt(p));
+ return roundUp(n, clampDigits(p));
}
@@ -107,7 +106,19 @@ final class MathX {
}
public static double roundDown(double n, double p) {
- return roundDown(n, MathUtil.safeDoubleToInt(p));
+ return roundDown(n, clampDigits(p));
+ }
+
+ /**
+ * A double has at most 15 significant digits and a decimal exponent within +/-324, so rounding
+ * to more than this many digits either side of the decimal point can never change the value.
+ * Larger digit counts are clamped to it: they would make {@link java.math.BigDecimal#setScale}
+ * build a number with billions of digits.
+ */
+ private static final int MAX_ROUNDING_DIGITS = 400;
+
+ private static int clampDigits(double p) {
+ return (int) Math.max(-MAX_ROUNDING_DIGITS, Math.min(MAX_ROUNDING_DIGITS, p));
}
private static double round(double n, int p, java.math.RoundingMode rounding) {
@@ -116,7 +127,7 @@ final class MathX {
}
else {
final String excelNumber = NumberToTextConverter.toText(n);
- return new java.math.BigDecimal(excelNumber).setScale(p, rounding).doubleValue();
+ return new java.math.BigDecimal(excelNumber).setScale(clampDigits(p), rounding).doubleValue();
}
}
@@ -320,7 +331,17 @@ final class MathX {
}
public static double factorial(double d) {
- return factorial(MathUtil.safeDoubleToInt(d));
+ if (Double.isNaN(d)) {
+ return Double.NaN;
+ }
+ if (d >= 171) {
+ // beyond Double.MAX_VALUE (and possibly beyond the int range)
+ return Double.POSITIVE_INFINITY;
+ }
+ if (d <= -1) {
+ return Double.NaN;
+ }
+ return factorial((int) d);
}
diff --git a/poi/src/main/java/org/apache/poi/ss/formula/functions/Quotient.java b/poi/src/main/java/org/apache/poi/ss/formula/functions/Quotient.java
index 92ec36d97d..8c2863ed3f 100644
--- a/poi/src/main/java/org/apache/poi/ss/formula/functions/Quotient.java
+++ b/poi/src/main/java/org/apache/poi/ss/formula/functions/Quotient.java
@@ -20,7 +20,6 @@ package org.apache.poi.ss.formula.functions;
import org.apache.poi.ss.formula.OperationEvaluationContext;
import org.apache.poi.ss.formula.eval.*;
-import org.apache.poi.util.MathUtil;
/**
* <p>Implementation for Excel QUOTIENT () function.
@@ -35,6 +34,7 @@ import org.apache.poi.util.MathUtil;
*
* If either enumerator/denominator is non numeric, QUOTIENT returns the #VALUE! error value.
* If denominator is equals to zero, QUOTIENT returns the #DIV/0! error value.
+ * If the quotient is too large to represent, QUOTIENT returns the #NUM! error value.
*/
public class Quotient extends Fixed2ArgFunction implements FreeRefFunction {
@@ -63,7 +63,12 @@ public class Quotient extends Fixed2ArgFunction implements FreeRefFunction {
return ErrorEval.DIV_ZERO;
}
- return new NumberEval(MathUtil.safeDoubleToInt(enumerator / denominator));
+ double quotient = enumerator / denominator;
+ if (Double.isNaN(quotient) || Double.isInfinite(quotient)) {
+ return ErrorEval.NUM_ERROR;
+ }
+ // truncate towards zero; the result is kept as a double so that quotients beyond the int range work
+ return new NumberEval(quotient < 0 ? Math.ceil(quotient) : Math.floor(quotient));
}
@Override
diff --git a/poi/src/test/java/org/apache/poi/ss/formula/functions/TestMathX.java b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestMathX.java
index b00b92b127..7914f19a2a 100644
--- a/poi/src/test/java/org/apache/poi/ss/formula/functions/TestMathX.java
+++ b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestMathX.java
@@ -20,6 +20,7 @@
*/
package org.apache.poi.ss.formula.functions;
+import static org.junit.jupiter.api.Assertions.assertEquals;
import static org.junit.jupiter.api.Assertions.assertTrue;
import org.apache.poi.ss.formula.functions.XYNumericFunction.Accumulator;
@@ -553,6 +554,24 @@ public class TestMathX extends BaseTestNumeric {
assertDouble("Factorial ", Double.POSITIVE_INFINITY, s);
}
+ @Test
+ void testFactorialDouble() {
+ // assertEquals rather than assertDouble: the latter does not really check NaN
+ assertEquals(1, MathX.factorial(0.0));
+ assertEquals(1, MathX.factorial(0.9));
+ assertEquals(1, MathX.factorial(-0.9));
+ assertEquals(120, MathX.factorial(5.5));
+ assertEquals(7.257415615307994E306, MathX.factorial(170.9));
+ assertEquals(Double.NaN, MathX.factorial(-1.0));
+ assertEquals(Double.NaN, MathX.factorial(Double.NaN));
+ assertEquals(Double.POSITIVE_INFINITY, MathX.factorial(171.0));
+ // beyond the int range: no IllegalArgumentException
+ assertEquals(Double.POSITIVE_INFINITY, MathX.factorial(1E10));
+ assertEquals(Double.POSITIVE_INFINITY, MathX.factorial(Double.POSITIVE_INFINITY));
+ assertEquals(Double.NaN, MathX.factorial(-1E10));
+ assertEquals(Double.NaN, MathX.factorial(Double.NEGATIVE_INFINITY));
+ }
+
@Test
void testSumx2my2() {
double[] xarr;
diff --git a/poi/src/test/java/org/apache/poi/ss/formula/functions/TestNumericFunction.java b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestNumericFunction.java
index 25f27f3c98..f63b414f2c 100644
--- a/poi/src/test/java/org/apache/poi/ss/formula/functions/TestNumericFunction.java
+++ b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestNumericFunction.java
@@ -222,4 +222,44 @@ final class TestNumericFunction {
assertError(fe, cell, "INT(\"abc\")", FormulaError.VALUE);
assertError(fe, cell, "INT(1/0)", FormulaError.DIV0);
}
+
+ @Test
+ void testFACTOutOfIntRange() {
+ HSSFWorkbook wb = new HSSFWorkbook();
+ HSSFCell cell = wb.createSheet().createRow(0).createCell(0);
+ HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
+ assertDouble(fe, cell, "FACT(170)", 7.257415615307994E306, 0);
+ assertDouble(fe, cell, "FACT(170.9)", 7.257415615307994E306, 0);
+ assertDouble(fe, cell, "FACT(-0.5)", 1.0, 0);
+ assertError(fe, cell, "FACT(171)", FormulaError.NUM);
+ assertError(fe, cell, "FACT(-1)", FormulaError.NUM);
+ // used to escape as an IllegalArgumentException from MathUtil.safeDoubleToInt
+ assertError(fe, cell, "FACT(1E10)", FormulaError.NUM);
+ assertError(fe, cell, "FACT(1E308)", FormulaError.NUM);
+ assertError(fe, cell, "FACT(-1E10)", FormulaError.NUM);
+ }
+
+ @Test
+ void testROUNDWithHugeDigitCount() {
+ HSSFWorkbook wb = new HSSFWorkbook();
+ HSSFCell cell = wb.createSheet().createRow(0).createCell(0);
+ HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
+ // digit counts beyond the int range used to escape as an IllegalArgumentException,
+ // and counts within it but in the hundreds of millions made BigDecimal.setScale run for minutes
+ for (String digits : new String[] {"400", "1000", "100000000", "2147483647", "1E10", "1E308"}) {
+ assertDouble(fe, cell, "ROUND(1.5," + digits + ")", 1.5, 0);
+ assertDouble(fe, cell, "ROUNDUP(1.5," + digits + ")", 1.5, 0);
+ assertDouble(fe, cell, "ROUNDDOWN(1.5," + digits + ")", 1.5, 0);
+ assertDouble(fe, cell, "TRUNC(1.5," + digits + ")", 1.5, 0);
+ assertDouble(fe, cell, "ROUND(1.5,-" + digits + ")", 0.0, 0);
+ assertDouble(fe, cell, "ROUNDDOWN(1.5,-" + digits + ")", 0.0, 0);
+ assertDouble(fe, cell, "TRUNC(1.5,-" + digits + ")", 0.0, 0);
+ assertError(fe, cell, "ROUNDUP(1.5,-" + digits + ")", FormulaError.NUM);
+ }
+ // the extremes of Excel's number range are still rounded correctly (Excel has no subnormals)
+ assertDouble(fe, cell, "ROUND(2.3E-308,400)", 2.3E-308, 0);
+ assertDouble(fe, cell, "ROUND(2.3E-308,307)", 0.0, 0);
+ assertDouble(fe, cell, "ROUND(1.5E308,-307)", 1.5E308, 0);
+ assertDouble(fe, cell, "ROUND(1.5E308,-309)", 0.0, 0);
+ }
}
diff --git a/poi/src/test/java/org/apache/poi/ss/formula/functions/TestQuotient.java b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestQuotient.java
index f742264393..bf48ac8c1f 100644
--- a/poi/src/test/java/org/apache/poi/ss/formula/functions/TestQuotient.java
+++ b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestQuotient.java
@@ -64,6 +64,16 @@ class TestQuotient {
confirmValue("Integer portion of Pi/Avogadro (0)", "3.14159", "6.02214179E+23", "0");
}
+ @Test
+ void testQuotientOutsideIntRange() {
+ // used to escape as an IllegalArgumentException from MathUtil.safeDoubleToInt
+ confirmValue("Integer portion of 5E9/2", "5000000000", "2", "2500000000");
+ confirmValue("Integer portion of -5E9/2", "-5000000000", "2", "-2500000000");
+ confirmValue("Integer portion of 1E20/3", "1E20", "3", "33333333333333300000");
+ confirmValue("Integer portion of 2^53/1", "9007199254740992", "1", "9007199254740990");
+ confirmValueError("quotient overflows a double", "1E308", "1E-10", ErrorEval.NUM_ERROR);
+ }
+
@Test
void testErrors() {
confirmValueError("numerator is nonnumeric", "ABCD", "", ErrorEval.VALUE_INVALID);
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org