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 15:01:25 UTC
(poi) branch trunk updated: Report integer arguments beyond the int range as an error value, not an IllegalArgumentException (#1288)
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 82afe2ba7c Report integer arguments beyond the int range as an error value, not an IllegalArgumentException (#1288)
82afe2ba7c is described below
commit 82afe2ba7c2232297f04f40f15ad35ed0752fd79
Author: PJ Fanning <pj...@users.noreply.github.com>
AuthorDate: Fri Sep 18 16:01:19 2026 +0100
Report integer arguments beyond the int range as an error value, not an IllegalArgumentException (#1288)
Functions taking an integer argument (CHAR, CHOOSE, DATE, DAY/MONTH/YEAR/HOUR/...,
LEFT/RIGHT/MID/FIND/SEARCH/REPT, LARGE/SMALL/RANK, ROMAN, FIXED, DOLLAR, SUBTOTAL,
TIME, EDATE/EOMONTH/WORKDAY*/YEARFRAC, ADDRESS, POISSON, ...) converted it with
MathUtil.safeDoubleToInt, whose IllegalArgumentException escaped the evaluator for
values such as 1E10. Excel reports an error value.
New OperandResolver.coerceDoubleToInt(double) truncates toward zero and throws
EvaluationException(#NUM!) for NaN or out-of-range values; coerceValueToInt uses it,
as do the direct safeDoubleToInt callers in the formula package. The text functions
and CHOOSE translate it to #VALUE!, as Excel does for a count, position or index.
COMPLEX now renders its parts with NumberToTextConverter, so COMPLEX(1E10,1) is
"10000000000+i" rather than an exception (and 0.1+0.2 is "0.3", as in Excel).
Co-authored-by: Claude Opus 5 (1M context) <no...@anthropic.com>
---
.../apache/poi/ss/formula/atp/WorkdayFunction.java | 4 +-
.../poi/ss/formula/atp/WorkdayIntlFunction.java | 6 +-
.../poi/ss/formula/atp/YearFracCalculator.java | 5 +-
.../poi/ss/formula/eval/OperandResolver.java | 18 +++-
.../apache/poi/ss/formula/functions/Address.java | 7 +-
.../ss/formula/functions/AggregateFunction.java | 8 +-
.../formula/functions/CalendarFieldFunction.java | 3 +-
.../apache/poi/ss/formula/functions/Choose.java | 7 +-
.../apache/poi/ss/formula/functions/Complex.java | 18 +---
.../apache/poi/ss/formula/functions/DateFunc.java | 8 +-
.../org/apache/poi/ss/formula/functions/EDate.java | 4 +-
.../apache/poi/ss/formula/functions/EOMonth.java | 4 +-
.../poi/ss/formula/functions/NumericFunction.java | 8 +-
.../apache/poi/ss/formula/functions/Poisson.java | 5 +-
.../poi/ss/formula/functions/TextFunction.java | 7 +-
.../poi/ss/formula/eval/TestOperandResolver.java | 16 ++++
.../ss/formula/functions/TestIntegerArguments.java | 102 +++++++++++++++++++++
17 files changed, 178 insertions(+), 52 deletions(-)
diff --git a/poi/src/main/java/org/apache/poi/ss/formula/atp/WorkdayFunction.java b/poi/src/main/java/org/apache/poi/ss/formula/atp/WorkdayFunction.java
index 17e409cabb..ab92378384 100644
--- a/poi/src/main/java/org/apache/poi/ss/formula/atp/WorkdayFunction.java
+++ b/poi/src/main/java/org/apache/poi/ss/formula/atp/WorkdayFunction.java
@@ -20,11 +20,11 @@ package org.apache.poi.ss.formula.atp;
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.OperandResolver;
import org.apache.poi.ss.formula.eval.NumberEval;
import org.apache.poi.ss.formula.eval.ValueEval;
import org.apache.poi.ss.formula.functions.FreeRefFunction;
import org.apache.poi.ss.usermodel.DateUtil;
-import org.apache.poi.util.MathUtil;
/**
* Implementation of Excel 'Analysis ToolPak' function WORKDAY()<br>
@@ -65,7 +65,7 @@ final class WorkdayFunction implements FreeRefFunction {
double[] holidays;
try {
start = this.evaluator.evaluateDateArg(args[0], srcCellRow, srcCellCol);
- days = MathUtil.safeDoubleToInt(
+ days = OperandResolver.coerceDoubleToInt(
Math.floor(this.evaluator.evaluateNumberArg(args[1], srcCellRow, srcCellCol)));
ValueEval holidaysCell = args.length == 3 ? args[2] : null;
holidays = this.evaluator.evaluateDatesArg(holidaysCell, srcCellRow, srcCellCol);
diff --git a/poi/src/main/java/org/apache/poi/ss/formula/atp/WorkdayIntlFunction.java b/poi/src/main/java/org/apache/poi/ss/formula/atp/WorkdayIntlFunction.java
index 29e79a66d7..00fc43ab8f 100644
--- a/poi/src/main/java/org/apache/poi/ss/formula/atp/WorkdayIntlFunction.java
+++ b/poi/src/main/java/org/apache/poi/ss/formula/atp/WorkdayIntlFunction.java
@@ -21,11 +21,11 @@ import org.apache.poi.ss.formula.OperationEvaluationContext;
import org.apache.poi.ss.formula.eval.BlankEval;
import org.apache.poi.ss.formula.eval.ErrorEval;
import org.apache.poi.ss.formula.eval.EvaluationException;
+import org.apache.poi.ss.formula.eval.OperandResolver;
import org.apache.poi.ss.formula.eval.NumberEval;
import org.apache.poi.ss.formula.eval.ValueEval;
import org.apache.poi.ss.formula.functions.FreeRefFunction;
import org.apache.poi.ss.usermodel.DateUtil;
-import org.apache.poi.util.MathUtil;
/**
* Implementation of Excel 'Analysis ToolPak' function WORKDAY.INTL()<br>
@@ -69,11 +69,11 @@ final class WorkdayIntlFunction implements FreeRefFunction {
double[] holidays;
try {
start = this.evaluator.evaluateDateArg(args[0], srcCellRow, srcCellCol);
- days = MathUtil.safeDoubleToInt(
+ days = OperandResolver.coerceDoubleToInt(
Math.floor(this.evaluator.evaluateNumberArg(args[1], srcCellRow, srcCellCol)));
if (args.length >= 3) {
if (args[2] != BlankEval.instance) {
- weekendType = MathUtil.safeDoubleToInt(
+ weekendType = OperandResolver.coerceDoubleToInt(
this.evaluator.evaluateNumberArg(args[2], srcCellRow, srcCellCol));
}
if (!WorkdayCalculator.instance.getValidWeekendTypes().contains(weekendType)) {
diff --git a/poi/src/main/java/org/apache/poi/ss/formula/atp/YearFracCalculator.java b/poi/src/main/java/org/apache/poi/ss/formula/atp/YearFracCalculator.java
index b614c463e3..708d4b2a59 100644
--- a/poi/src/main/java/org/apache/poi/ss/formula/atp/YearFracCalculator.java
+++ b/poi/src/main/java/org/apache/poi/ss/formula/atp/YearFracCalculator.java
@@ -21,6 +21,7 @@ import java.util.Calendar;
import org.apache.poi.ss.formula.eval.ErrorEval;
import org.apache.poi.ss.formula.eval.EvaluationException;
+import org.apache.poi.ss.formula.eval.OperandResolver;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.util.Internal;
import org.apache.poi.util.LocaleUtil;
@@ -61,8 +62,8 @@ final class YearFracCalculator {
// common logic for all bases
// truncate day values
- int startDateVal = MathUtil.safeDoubleToInt(Math.floor(pStartDateVal));
- int endDateVal = MathUtil.safeDoubleToInt(Math.floor(pEndDateVal));
+ int startDateVal = OperandResolver.coerceDoubleToInt(Math.floor(pStartDateVal));
+ int endDateVal = OperandResolver.coerceDoubleToInt(Math.floor(pEndDateVal));
if (startDateVal == endDateVal) {
// when dates are equal, result is zero
return 0;
diff --git a/poi/src/main/java/org/apache/poi/ss/formula/eval/OperandResolver.java b/poi/src/main/java/org/apache/poi/ss/formula/eval/OperandResolver.java
index 709758fe24..cc4c6be022 100644
--- a/poi/src/main/java/org/apache/poi/ss/formula/eval/OperandResolver.java
+++ b/poi/src/main/java/org/apache/poi/ss/formula/eval/OperandResolver.java
@@ -230,6 +230,7 @@ public final class OperandResolver {
* <b>not</b>:<br>
* {@code return (int)d; // wrong - rounds toward zero}
*
+ * @throws EvaluationException (#NUM!) if the value does not fit an int
*/
public static int coerceValueToInt(ValueEval ev) throws EvaluationException {
if (ev == BlankEval.instance) {
@@ -238,7 +239,22 @@ public final class OperandResolver {
double d = coerceValueToDouble(ev);
// Note - the standard java type conversion from double to int truncates toward zero.
// but Math.floor() truncates toward negative infinity
- return MathUtil.safeDoubleToInt(Math.floor(d));
+ return coerceDoubleToInt(Math.floor(d));
+ }
+
+ /**
+ * Converts a double to an int, truncating toward zero, for use as an integer argument of a function.
+ * Unlike {@link MathUtil#safeDoubleToInt(double)} a value that does not fit an int is reported the
+ * way Excel reports an unusable argument, as an error value rather than an exception.
+ *
+ * @throws EvaluationException (#NUM!) if the value is NaN, infinite or outside the int range
+ * @since 6.0.0
+ */
+ public static int coerceDoubleToInt(double d) throws EvaluationException {
+ if (Double.isNaN(d) || d > Integer.MAX_VALUE || d < Integer.MIN_VALUE) {
+ throw new EvaluationException(ErrorEval.NUM_ERROR);
+ }
+ return (int) d;
}
/**
diff --git a/poi/src/main/java/org/apache/poi/ss/formula/functions/Address.java b/poi/src/main/java/org/apache/poi/ss/formula/functions/Address.java
index 43b82a42a0..ecddfae5ab 100644
--- a/poi/src/main/java/org/apache/poi/ss/formula/functions/Address.java
+++ b/poi/src/main/java/org/apache/poi/ss/formula/functions/Address.java
@@ -19,7 +19,6 @@ package org.apache.poi.ss.formula.functions;
import org.apache.poi.ss.formula.SheetNameFormatter;
import org.apache.poi.ss.formula.eval.*;
import org.apache.poi.ss.util.CellReference;
-import org.apache.poi.util.MathUtil;
/**
* Creates a text reference as text, given specified row and column numbers.
@@ -38,15 +37,15 @@ public class Address implements Function {
try {
boolean pAbsRow, pAbsCol;
- int row = MathUtil.safeDoubleToInt(NumericFunction.singleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex));
- int col = MathUtil.safeDoubleToInt(NumericFunction.singleOperandEvaluate(args[1], srcRowIndex, srcColumnIndex));
+ int row = OperandResolver.coerceDoubleToInt(NumericFunction.singleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex));
+ int col = OperandResolver.coerceDoubleToInt(NumericFunction.singleOperandEvaluate(args[1], srcRowIndex, srcColumnIndex));
if (row < 1 || col < 1) {
return ErrorEval.VALUE_INVALID;
}
int refType;
if (args.length > 2 && args[2] != MissingArgEval.instance) {
- refType = MathUtil.safeDoubleToInt(NumericFunction.singleOperandEvaluate(args[2], srcRowIndex, srcColumnIndex));
+ refType = OperandResolver.coerceDoubleToInt(NumericFunction.singleOperandEvaluate(args[2], srcRowIndex, srcColumnIndex));
} else {
refType = REF_ABSOLUTE; // this is also the default if parameter is not given
}
diff --git a/poi/src/main/java/org/apache/poi/ss/formula/functions/AggregateFunction.java b/poi/src/main/java/org/apache/poi/ss/formula/functions/AggregateFunction.java
index 78c190dbb7..a42328e883 100644
--- a/poi/src/main/java/org/apache/poi/ss/formula/functions/AggregateFunction.java
+++ b/poi/src/main/java/org/apache/poi/ss/formula/functions/AggregateFunction.java
@@ -23,7 +23,6 @@ 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 org.apache.poi.util.MathUtil;
public abstract class AggregateFunction extends MultiOperandNumericFunction {
@@ -48,11 +47,10 @@ public abstract class AggregateFunction extends MultiOperandNumericFunction {
// values between 0.0 and 1.0 result in #NUM!
return ErrorEval.NUM_ERROR;
}
- // all other values are rounded up to the next integer
- int k = MathUtil.safeDoubleToInt(Math.ceil(dn));
-
double result;
try {
+ // all other values are rounded up to the next integer
+ int k = OperandResolver.coerceDoubleToInt(Math.ceil(dn));
double[] ds = ValueCollector.collectValues(arg0);
if (k > ds.length) {
return ErrorEval.NUM_ERROR;
@@ -117,7 +115,7 @@ public abstract class AggregateFunction extends MultiOperandNumericFunction {
} else if (Double.compare(n, N) == 0) {
result = StatsLib.kthLargest(ds, 1);
} else {
- int k = MathUtil.safeDoubleToInt(n);
+ int k = OperandResolver.coerceDoubleToInt(n);
double d = n - k;
result = StatsLib.kthSmallest(ds, k) + d
* (StatsLib.kthSmallest(ds, k + 1) - StatsLib.kthSmallest(ds, k));
diff --git a/poi/src/main/java/org/apache/poi/ss/formula/functions/CalendarFieldFunction.java b/poi/src/main/java/org/apache/poi/ss/formula/functions/CalendarFieldFunction.java
index fdd5655b42..1686961e76 100644
--- a/poi/src/main/java/org/apache/poi/ss/formula/functions/CalendarFieldFunction.java
+++ b/poi/src/main/java/org/apache/poi/ss/formula/functions/CalendarFieldFunction.java
@@ -25,7 +25,6 @@ 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 org.apache.poi.ss.usermodel.DateUtil;
-import org.apache.poi.util.MathUtil;
/**
* Implementation of Excel functions Date parsing functions:
@@ -63,7 +62,7 @@ public final class CalendarFieldFunction extends Fixed1ArgFunction {
private int getCalField(double serialDate) {
// For some reason, a date of 0 in Excel gets shown
// as the non-existent 1900-01-00
- if (MathUtil.safeDoubleToInt(serialDate) == 0) {
+ if ((int) serialDate == 0) {
switch (_dateFieldId) {
case Calendar.YEAR -> { return 1900; }
case Calendar.MONTH -> { return 1; }
diff --git a/poi/src/main/java/org/apache/poi/ss/formula/functions/Choose.java b/poi/src/main/java/org/apache/poi/ss/formula/functions/Choose.java
index 4f6e75e39d..6c93c26b1c 100644
--- a/poi/src/main/java/org/apache/poi/ss/formula/functions/Choose.java
+++ b/poi/src/main/java/org/apache/poi/ss/formula/functions/Choose.java
@@ -49,6 +49,11 @@ public final class Choose implements Function {
public static int evaluateFirstArg(ValueEval arg0, int srcRowIndex, int srcColumnIndex)
throws EvaluationException {
ValueEval ev = OperandResolver.getSingleValue(arg0, srcRowIndex, srcColumnIndex);
- return OperandResolver.coerceValueToInt(ev);
+ try {
+ return OperandResolver.coerceValueToInt(ev);
+ } catch (EvaluationException e) {
+ // an index beyond the int range is out of range of the choices: #VALUE!
+ throw EvaluationException.invalidValue();
+ }
}
}
diff --git a/poi/src/main/java/org/apache/poi/ss/formula/functions/Complex.java b/poi/src/main/java/org/apache/poi/ss/formula/functions/Complex.java
index b5639f4617..ee63080bb0 100644
--- a/poi/src/main/java/org/apache/poi/ss/formula/functions/Complex.java
+++ b/poi/src/main/java/org/apache/poi/ss/formula/functions/Complex.java
@@ -23,9 +23,9 @@ 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.OperandResolver;
+import org.apache.poi.ss.util.NumberToTextConverter;
import org.apache.poi.ss.formula.eval.StringEval;
import org.apache.poi.ss.formula.eval.ValueEval;
-import org.apache.poi.util.MathUtil;
/**
* Implementation for Excel COMPLEX () function.
@@ -109,11 +109,7 @@ public class Complex extends Var2or3ArgFunction implements FreeRefFunction {
StringBuilder strb = new StringBuilder();
if (realNum != 0) {
- if (isDoubleAnInt(realNum)) {
- strb.append(MathUtil.safeDoubleToInt(realNum));
- } else {
- strb.append(realNum);
- }
+ strb.append(NumberToTextConverter.toText(realNum));
}
if (realINum != 0) {
if (strb.length() != 0) {
@@ -123,11 +119,7 @@ public class Complex extends Var2or3ArgFunction implements FreeRefFunction {
}
if (realINum != 1 && realINum != -1) {
- if (isDoubleAnInt(realINum)) {
- strb.append(MathUtil.safeDoubleToInt(realINum));
- } else {
- strb.append(realINum);
- }
+ strb.append(NumberToTextConverter.toText(realINum));
}
strb.append(suffixValue);
@@ -136,10 +128,6 @@ public class Complex extends Var2or3ArgFunction implements FreeRefFunction {
return new StringEval(strb.toString());
}
- private boolean isDoubleAnInt(double number) {
- return (number == Math.floor(number)) && !Double.isInfinite(number);
- }
-
@Override
public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) {
if (args.length == 2) {
diff --git a/poi/src/main/java/org/apache/poi/ss/formula/functions/DateFunc.java b/poi/src/main/java/org/apache/poi/ss/formula/functions/DateFunc.java
index 5d22f4fc26..59dbd0fe90 100644
--- a/poi/src/main/java/org/apache/poi/ss/formula/functions/DateFunc.java
+++ b/poi/src/main/java/org/apache/poi/ss/formula/functions/DateFunc.java
@@ -21,11 +21,11 @@ import java.util.Calendar;
import org.apache.poi.ss.formula.eval.ErrorEval;
import org.apache.poi.ss.formula.eval.EvaluationException;
+import org.apache.poi.ss.formula.eval.OperandResolver;
import org.apache.poi.ss.formula.eval.NumberEval;
import org.apache.poi.ss.formula.eval.ValueEval;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.util.LocaleUtil;
-import org.apache.poi.util.MathUtil;
/**
@@ -44,7 +44,7 @@ public final class DateFunc extends Fixed3ArgFunction {
double d0 = NumericFunction.singleOperandEvaluate(arg0, srcRowIndex, srcColumnIndex);
double d1 = NumericFunction.singleOperandEvaluate(arg1, srcRowIndex, srcColumnIndex);
double d2 = NumericFunction.singleOperandEvaluate(arg2, srcRowIndex, srcColumnIndex);
- result = evaluate(getYear(d0), MathUtil.safeDoubleToInt(d1 - 1), MathUtil.safeDoubleToInt(d2));
+ result = evaluate(getYear(d0), OperandResolver.coerceDoubleToInt(d1 - 1), OperandResolver.coerceDoubleToInt(d2));
NumericFunction.checkValue(result);
} catch (EvaluationException e) {
return e.getErrorEval();
@@ -101,8 +101,8 @@ public final class DateFunc extends Fixed3ArgFunction {
return DateUtil.getExcelDate(c.getTime(), use1904windowing);
}
- private static int getYear(double d) {
- int year = MathUtil.safeDoubleToInt(d);
+ private static int getYear(double d) throws EvaluationException {
+ int year = OperandResolver.coerceDoubleToInt(d);
if (year < 0) {
return -1;
diff --git a/poi/src/main/java/org/apache/poi/ss/formula/functions/EDate.java b/poi/src/main/java/org/apache/poi/ss/formula/functions/EDate.java
index c0bc4e2d61..a4c12290a7 100644
--- a/poi/src/main/java/org/apache/poi/ss/formula/functions/EDate.java
+++ b/poi/src/main/java/org/apache/poi/ss/formula/functions/EDate.java
@@ -24,12 +24,12 @@ import org.apache.poi.ss.formula.OperationEvaluationContext;
import org.apache.poi.ss.formula.eval.BlankEval;
import org.apache.poi.ss.formula.eval.ErrorEval;
import org.apache.poi.ss.formula.eval.EvaluationException;
+import org.apache.poi.ss.formula.eval.OperandResolver;
import org.apache.poi.ss.formula.eval.NumberEval;
import org.apache.poi.ss.formula.eval.RefEval;
import org.apache.poi.ss.formula.eval.ValueEval;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.util.LocaleUtil;
-import org.apache.poi.util.MathUtil;
/**
* Implementation for Excel EDATE () function.
@@ -43,7 +43,7 @@ public class EDate implements FreeRefFunction {
}
try {
double startDateAsNumber = getValue(args[0]);
- int offsetInMonthAsNumber = MathUtil.safeDoubleToInt(getValue(args[1]));
+ int offsetInMonthAsNumber = OperandResolver.coerceDoubleToInt(getValue(args[1]));
if (startDateAsNumber < 0 || startDateAsNumber >= DateUtil.MAX_EXCEL_DATE_SERIAL + 1) {
return ErrorEval.NUM_ERROR;
diff --git a/poi/src/main/java/org/apache/poi/ss/formula/functions/EOMonth.java b/poi/src/main/java/org/apache/poi/ss/formula/functions/EOMonth.java
index fe2b48c31e..11a0c05dcd 100644
--- a/poi/src/main/java/org/apache/poi/ss/formula/functions/EOMonth.java
+++ b/poi/src/main/java/org/apache/poi/ss/formula/functions/EOMonth.java
@@ -23,11 +23,11 @@ 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.OperandResolver;
import org.apache.poi.ss.formula.eval.NumberEval;
import org.apache.poi.ss.formula.eval.ValueEval;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.util.LocaleUtil;
-import org.apache.poi.util.MathUtil;
/**
* Implementation for the Excel EOMONTH() function.
@@ -54,7 +54,7 @@ public class EOMonth implements FreeRefFunction {
try {
double startDateAsNumber = NumericFunction.singleOperandEvaluate(args[0], ec.getRowIndex(), ec.getColumnIndex());
- int months = MathUtil.safeDoubleToInt(
+ int months = OperandResolver.coerceDoubleToInt(
NumericFunction.singleOperandEvaluate(args[1], ec.getRowIndex(), ec.getColumnIndex()));
if (startDateAsNumber < 0 || startDateAsNumber >= DateUtil.MAX_EXCEL_DATE_SERIAL + 1) {
diff --git a/poi/src/main/java/org/apache/poi/ss/formula/functions/NumericFunction.java b/poi/src/main/java/org/apache/poi/ss/formula/functions/NumericFunction.java
index 8407e3ce9c..c8eaa0ae61 100644
--- a/poi/src/main/java/org/apache/poi/ss/formula/functions/NumericFunction.java
+++ b/poi/src/main/java/org/apache/poi/ss/formula/functions/NumericFunction.java
@@ -22,7 +22,6 @@ import static org.apache.poi.ss.formula.eval.ErrorEval.VALUE_INVALID;
import org.apache.poi.ss.formula.eval.*;
import org.apache.poi.ss.util.ExcelArithmetic;
import org.apache.poi.util.LocaleUtil;
-import org.apache.poi.util.MathUtil;
import org.apache.poi.util.StringUtil;
import java.math.BigDecimal;
@@ -92,12 +91,11 @@ public abstract class NumericFunction implements Function {
double val = singleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex);
double d1 = args.length == 1 ? 2.0 : singleOperandEvaluate(args[1], srcRowIndex, srcColumnIndex);
- // second arg converts to int by truncating toward zero
- int nPlaces = MathUtil.safeDoubleToInt(d1);
-
- if (nPlaces > 127) {
+ if (d1 > 127) {
return VALUE_INVALID;
}
+ // second arg converts to int by truncating toward zero
+ int nPlaces = OperandResolver.coerceDoubleToInt(d1);
if (nPlaces < 0) {
BigDecimal divisor = BigDecimal.valueOf(Math.pow(10, -nPlaces));
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 4e1abb008a..e25f751f11 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
@@ -24,7 +24,6 @@ import org.apache.poi.ss.formula.eval.EvaluationException;
import org.apache.poi.ss.formula.eval.OperandResolver;
import org.apache.poi.ss.formula.eval.NumberEval;
import org.apache.poi.ss.formula.eval.ValueEval;
-import org.apache.poi.util.MathUtil;
public class Poisson implements FreeRefFunction {
@@ -91,8 +90,8 @@ public class Poisson implements FreeRefFunction {
boolean cumulative = cumulativeArg != null && cumulativeArg;
PoissonDistribution poissonDistribution = new PoissonDistribution(mean);
double result = cumulative ?
- poissonDistribution.cumulativeProbability( MathUtil.safeDoubleToInt(x)) :
- poissonDistribution.probability(MathUtil.safeDoubleToInt(x));
+ poissonDistribution.cumulativeProbability( OperandResolver.coerceDoubleToInt(x)) :
+ poissonDistribution.probability(OperandResolver.coerceDoubleToInt(x));
// check the result
NumericFunction.checkValue(result);
diff --git a/poi/src/main/java/org/apache/poi/ss/formula/functions/TextFunction.java b/poi/src/main/java/org/apache/poi/ss/formula/functions/TextFunction.java
index c5a6be88d8..2d98026837 100644
--- a/poi/src/main/java/org/apache/poi/ss/formula/functions/TextFunction.java
+++ b/poi/src/main/java/org/apache/poi/ss/formula/functions/TextFunction.java
@@ -43,7 +43,12 @@ public abstract class TextFunction implements Function {
protected static int evaluateIntArg(ValueEval arg, int srcCellRow, int srcCellCol) throws EvaluationException {
ValueEval ve = OperandResolver.getSingleValue(arg, srcCellRow, srcCellCol);
- return OperandResolver.coerceValueToInt(ve);
+ try {
+ return OperandResolver.coerceValueToInt(ve);
+ } catch (EvaluationException e) {
+ // a count or position beyond the int range is #VALUE! in the text functions
+ throw EvaluationException.invalidValue();
+ }
}
protected static double evaluateDoubleArg(ValueEval arg, int srcCellRow, int srcCellCol) throws EvaluationException {
diff --git a/poi/src/test/java/org/apache/poi/ss/formula/eval/TestOperandResolver.java b/poi/src/test/java/org/apache/poi/ss/formula/eval/TestOperandResolver.java
index 17b0a033d6..db03115cb3 100644
--- a/poi/src/test/java/org/apache/poi/ss/formula/eval/TestOperandResolver.java
+++ b/poi/src/test/java/org/apache/poi/ss/formula/eval/TestOperandResolver.java
@@ -20,6 +20,7 @@ package org.apache.poi.ss.formula.eval;
import static org.junit.jupiter.api.Assertions.assertEquals;
import static org.junit.jupiter.api.Assertions.assertNotNull;
import static org.junit.jupiter.api.Assertions.assertNull;
+import static org.junit.jupiter.api.Assertions.assertThrows;
import static org.junit.jupiter.api.Assertions.fail;
import java.util.LinkedHashMap;
@@ -139,4 +140,19 @@ final class TestOperandResolver {
// the largest finite double is still fine
assertEquals(Double.MAX_VALUE, OperandResolver.parseDouble("1.7976931348623157E308"), 0);
}
+
+ @Test
+ void testCoerceDoubleToInt() throws EvaluationException {
+ assertEquals(2, OperandResolver.coerceDoubleToInt(2.9));
+ assertEquals(-2, OperandResolver.coerceDoubleToInt(-2.9));
+ assertEquals(Integer.MAX_VALUE, OperandResolver.coerceDoubleToInt(Integer.MAX_VALUE));
+ assertEquals(Integer.MIN_VALUE, OperandResolver.coerceDoubleToInt(Integer.MIN_VALUE));
+ for (double d : new double[]{1E10, -1E10, 2147483648.0, -2147483649.0,
+ Double.POSITIVE_INFINITY, Double.NEGATIVE_INFINITY, Double.NaN}) {
+ EvaluationException e = assertThrows(EvaluationException.class, () -> OperandResolver.coerceDoubleToInt(d));
+ assertEquals(ErrorEval.NUM_ERROR, e.getErrorEval());
+ }
+ EvaluationException e = assertThrows(EvaluationException.class, () -> OperandResolver.coerceValueToInt(new NumberEval(1E10)));
+ assertEquals(ErrorEval.NUM_ERROR, e.getErrorEval());
+ }
}
diff --git a/poi/src/test/java/org/apache/poi/ss/formula/functions/TestIntegerArguments.java b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestIntegerArguments.java
new file mode 100644
index 0000000000..e0d064de26
--- /dev/null
+++ b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestIntegerArguments.java
@@ -0,0 +1,102 @@
+/* ====================================================================
+ 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.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;
+import static org.apache.poi.ss.util.Utils.assertString;
+
+/**
+ * Integer arguments beyond the int range used to escape the evaluator as an
+ * IllegalArgumentException from MathUtil.safeDoubleToInt; Excel reports an error value.
+ */
+final class TestIntegerArguments {
+
+ @Test
+ void testNumericAndDateFunctions() throws IOException {
+ try (HSSFWorkbook wb = new HSSFWorkbook()) {
+ HSSFCell cell = wb.createSheet().createRow(0).createCell(0);
+ HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
+ assertError(fe, cell, "DAY(1E308)", FormulaError.NUM);
+ assertError(fe, cell, "MONTH(1E10)", FormulaError.NUM);
+ assertError(fe, cell, "YEAR(1E10)", FormulaError.NUM);
+ assertError(fe, cell, "HOUR(1E10)", FormulaError.NUM);
+ assertError(fe, cell, "DATE(1E10,1,1)", FormulaError.NUM);
+ assertError(fe, cell, "DATE(2020,1E10,1)", FormulaError.NUM);
+ assertError(fe, cell, "DATE(2020,1,1E10)", FormulaError.NUM);
+ assertError(fe, cell, "EDATE(1,1E10)", FormulaError.NUM);
+ assertError(fe, cell, "EOMONTH(1,1E10)", FormulaError.NUM);
+ assertError(fe, cell, "YEARFRAC(1E10,1)", FormulaError.NUM);
+ assertError(fe, cell, "TIME(1E10,0,0)", FormulaError.NUM);
+ assertError(fe, cell, "LARGE({1,2,3},1E10)", FormulaError.NUM);
+ assertError(fe, cell, "SMALL({1,2,3},-1E10)", FormulaError.NUM);
+ assertError(fe, cell, "RANK(1,{1,2,3},1E10)", FormulaError.NUM);
+ assertError(fe, cell, "POISSON(1E10,1,TRUE)", FormulaError.NUM);
+ assertError(fe, cell, "ADDRESS(1E10,1)", FormulaError.NUM);
+ assertError(fe, cell, "ADDRESS(1,1,1E10)", FormulaError.NUM);
+ assertError(fe, cell, "SUBTOTAL(1E10,{1,2})", FormulaError.NUM);
+ assertError(fe, cell, "FIXED(1,1E10)", FormulaError.NUM);
+ assertError(fe, cell, "DOLLAR(1,1E10)", FormulaError.VALUE);
+ assertError(fe, cell, "DOLLAR(1,-1E10)", FormulaError.NUM);
+ // the same functions still work at the edge of the int range
+ assertDouble(fe, cell, "LARGE({1,2,3},2.5)", 1, 0);
+ }
+ }
+
+ @Test
+ void testTextAndLookupFunctions() throws IOException {
+ try (HSSFWorkbook wb = new HSSFWorkbook()) {
+ HSSFCell cell = wb.createSheet().createRow(0).createCell(0);
+ HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
+ assertError(fe, cell, "CHAR(1E308)", FormulaError.VALUE);
+ assertError(fe, cell, "LEFT(\"abc\",1E10)", FormulaError.VALUE);
+ assertError(fe, cell, "RIGHT(\"abc\",1E10)", FormulaError.VALUE);
+ assertError(fe, cell, "MID(\"abc\",1E10,1)", FormulaError.VALUE);
+ assertError(fe, cell, "MID(\"abc\",1,1E10)", FormulaError.VALUE);
+ assertError(fe, cell, "FIND(\"a\",\"abc\",1E10)", FormulaError.VALUE);
+ assertError(fe, cell, "SEARCH(\"a\",\"abc\",1E10)", FormulaError.VALUE);
+ assertError(fe, cell, "REPLACE(\"abc\",1E10,1,\"x\")", FormulaError.VALUE);
+ assertError(fe, cell, "SUBSTITUTE(\"abc\",\"a\",\"x\",1E10)", FormulaError.VALUE);
+ assertError(fe, cell, "REPT(\"x\",1E10)", FormulaError.VALUE);
+ assertError(fe, cell, "CHOOSE(1E10,1,2)", FormulaError.VALUE);
+ assertError(fe, cell, "CHOOSE(-1E10,1,2)", FormulaError.VALUE);
+ assertError(fe, cell, "ROMAN(1E10)", FormulaError.VALUE);
+ assertString(fe, cell, "LEFT(\"abc\",2147483647)", "abc");
+ }
+ }
+
+ @Test
+ void testComplexRendersLargeParts() throws IOException {
+ try (HSSFWorkbook wb = new HSSFWorkbook()) {
+ HSSFCell cell = wb.createSheet().createRow(0).createCell(0);
+ HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
+ assertString(fe, cell, "COMPLEX(1E10,1)", "10000000000+i");
+ assertString(fe, cell, "COMPLEX(3,-1E10)", "3-10000000000i");
+ assertString(fe, cell, "COMPLEX(1E21,2E21)", "1E+21+2E+21i");
+ assertString(fe, cell, "COMPLEX(0.1+0.2,0.5)", "0.3+0.5i");
+ }
+ }
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org