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 330815c00d Fraction formatting: follow-ups to #1473 (#1485)
330815c00d is described below
commit 330815c00d462cbbabe826ed2d063db83548d746
Author: PJ Fanning <[email protected]>
AuthorDate: Mon Oct 26 17:17:39 2026 +0000
Fraction formatting: follow-ups to #1473 (#1485)
* Fraction formatting: follow-ups to #1473
- CellFormat shows an improper fraction whose numerator does not fit in
an int, e.g. "?/?" shows 3e9+0.5 as 6000000001/2, instead of throwing,
so callers of CellFormat other than DataFormatter (e.g. ToHtml) do not
fail
- DataFormatter no longer tries a CellFormat on a probe value or on the
first cell's value, so the cached format does not depend on which value
comes first or which section the probe lands in; a value the CellFormat
fails on uses the fallback, which is created when it is first needed
- a CellFormat failure is logged at warn the first time for a format,
and at debug after that
- the fallback for a fraction with text allows as many denominator
digits as CellFormat does, from one constant,
SimpleFraction.MAX_DENOMINATOR_DIGITS
- SimpleFraction: one overflow check for the numerator and denominator
Co-Authored-By: Claude Opus 5.5 <[email protected]>
* CellNumberFormatter: show a fraction's whole number above Long.MAX_VALUE
The whole number was cast to a long, so "# ?/?" showed 1e20 as
9223372036854775807 (printed as 9223372036854776000).
Co-Authored-By: Claude Opus 5.5 <[email protected]>
---------
Co-authored-by: Claude Opus 5.5 <[email protected]>
---
.../apache/poi/ss/format/CellNumberFormatter.java | 25 ++++++----
.../org/apache/poi/ss/format/SimpleFraction.java | 16 ++++--
.../org/apache/poi/ss/usermodel/DataFormatter.java | 57 +++++++++++++++-------
.../apache/poi/ss/usermodel/FractionFormat.java | 13 ++++-
.../org/apache/poi/ss/format/TestCellFormat.java | 14 ++++--
.../apache/poi/ss/usermodel/TestDataFormatter.java | 12 ++---
.../poi/ss/usermodel/TestFractionFormat.java | 7 +++
7 files changed, 100 insertions(+), 44 deletions(-)
diff --git
a/poi/src/main/java/org/apache/poi/ss/format/CellNumberFormatter.java
b/poi/src/main/java/org/apache/poi/ss/format/CellNumberFormatter.java
index 00a190d743..f84da27c5e 100644
--- a/poi/src/main/java/org/apache/poi/ss/format/CellNumberFormatter.java
+++ b/poi/src/main/java/org/apache/poi/ss/format/CellNumberFormatter.java
@@ -17,6 +17,7 @@
package org.apache.poi.ss.format;
import java.math.BigDecimal;
+import java.math.BigInteger;
import java.text.DecimalFormat;
import java.text.DecimalFormatSymbols;
import java.text.FieldPosition;
@@ -62,8 +63,6 @@ public class CellNumberFormatter extends CellFormatter {
private final List<Special> denominatorSpecials = new ArrayList<>();
private final List<Special> exponentSpecials = new ArrayList<>();
private final List<Special> exponentDigitSpecials = new ArrayList<>();
- /** 10^9 - 1 is the largest all-nines denominator that fits in an int */
- private static final int MAX_DENOMINATOR_DIGITS = 9;
private final int maxDenominator;
/** the denominator given as a number, e.g. 4 for "# ?/4", or 0 */
private final int fixedDenominator;
@@ -337,7 +336,7 @@ public class CellNumberFormatter extends CellFormatter {
}
private static int maxValue(List<Special> s) {
- return Math.toIntExact(Math.round(Math.pow(10, Math.min(s.size(),
MAX_DENOMINATOR_DIGITS)) - 1));
+ return Math.toIntExact(Math.round(Math.pow(10, Math.min(s.size(),
SimpleFraction.MAX_DENOMINATOR_DIGITS)) - 1));
}
private List<Special> specialsFor(int pos, int takeFirst) {
@@ -475,8 +474,8 @@ public class CellNumberFormatter extends CellFormatter {
value = 0;
} else {
fractional = value % 1.0;
- //noinspection SillyAssignment
- value = (long) value;
+ // not a cast to long, which shows a value above
Long.MAX_VALUE as 9223372036854775807
+ value = Math.floor(value);
}
}
@@ -663,9 +662,13 @@ public class CellNumberFormatter extends CellFormatter {
@SuppressWarnings("unchecked")
private void writeFraction(double value, StringBuffer result,
double fractional, StringBuffer output, Set<CellNumberStringMod>
mods) {
- // the numerator is an int, so an improper fraction of a huge value
cannot be shown
- if (Math.abs(fractional) * Math.max(fixedDenominator, 1) >=
Integer.MAX_VALUE) {
- throw new IllegalArgumentException("Value is too large to format
as a fraction: " + fractional);
+ // the fraction is worked out with ints, so for an improper fraction
of a large value it is worked out
+ // for the part after the whole number, and the whole number is added
to the numerator when it is written
+ BigInteger wholeNumber = null;
+ if (improperFraction && fractional * Math.max(fixedDenominator, 1) >=
Integer.MAX_VALUE) {
+ double floor = Math.floor(fractional);
+ wholeNumber = new BigDecimal(floor).toBigInteger();
+ fractional -= floor;
}
// Work out the fraction first, as it can round to 0, or up to 1
@@ -749,7 +752,9 @@ public class CellNumberFormatter extends CellFormatter {
if (improperFraction) {
n = Math.toIntExact(n + Math.round(value * d));
}
- writeSingleInteger(numeratorFmt, n, output, numeratorSpecials,
mods);
+ Number numerator = wholeNumber == null ? (Number) n
+ :
wholeNumber.multiply(BigInteger.valueOf(d)).add(BigInteger.valueOf(n));
+ writeSingleInteger(numeratorFmt, numerator, output,
numeratorSpecials, mods);
if (fixedDenominator == 0) {
// a fixed denominator is already in the output
writeDenominator(d, output, mods);
@@ -812,7 +817,7 @@ public class CellNumberFormatter extends CellFormatter {
}
}
- private void writeSingleInteger(String fmt, int num, StringBuffer output,
List<Special> numSpecials, Set<CellNumberStringMod> mods) {
+ private void writeSingleInteger(String fmt, Number num, StringBuffer
output, List<Special> numSpecials, Set<CellNumberStringMod> mods) {
StringBuffer sb = new StringBuffer();
try (Formatter formatter = new Formatter(sb, locale)) {
diff --git a/poi/src/main/java/org/apache/poi/ss/format/SimpleFraction.java
b/poi/src/main/java/org/apache/poi/ss/format/SimpleFraction.java
index 31b37a45dd..9c584c9ef7 100644
--- a/poi/src/main/java/org/apache/poi/ss/format/SimpleFraction.java
+++ b/poi/src/main/java/org/apache/poi/ss/format/SimpleFraction.java
@@ -20,6 +20,12 @@ import org.apache.poi.util.MathUtil;
public class SimpleFraction {
+ /**
+ * The most denominator digits a fraction can have: 10^9 - 1 is the
largest all-nines denominator that fits in an int
+ *
+ * @since 6.0.1
+ */
+ public static final int MAX_DENOMINATOR_DIGITS = 9;
/** The denominator. */
private final int denominator;
@@ -115,11 +121,11 @@ public class SimpleFraction {
return new SimpleFraction(Math.toIntExact(p1),
Math.toIntExact(q1));
}
- if (epsilon == 0.0f && maxDenominator > 0 && Math.abs(p2) >
overflow) {
- // an improper fraction's numerator can overflow before the
denominator reaches its maximum
- return new SimpleFraction(Math.toIntExact(p1),
Math.toIntExact(q1));
- }
- if ((p2 > overflow) || (q2 > overflow)) {
+ if ((Math.abs(p2) > overflow) || (q2 > overflow)) {
+ if (epsilon == 0.0f && maxDenominator > 0) {
+ // an improper fraction's numerator can overflow before
the denominator reaches its maximum
+ return new SimpleFraction(Math.toIntExact(p1),
Math.toIntExact(q1));
+ }
throw new IllegalStateException("Overflow trying to convert
"+value+" to fraction ("+p2+"/"+q2+")");
}
diff --git a/poi/src/main/java/org/apache/poi/ss/usermodel/DataFormatter.java
b/poi/src/main/java/org/apache/poi/ss/usermodel/DataFormatter.java
index 46315ee959..9344bf640b 100644
--- a/poi/src/main/java/org/apache/poi/ss/usermodel/DataFormatter.java
+++ b/poi/src/main/java/org/apache/poi/ss/usermodel/DataFormatter.java
@@ -35,6 +35,7 @@ import java.util.HashMap;
import java.util.List;
import java.util.Locale;
import java.util.Map;
+import java.util.function.Supplier;
import java.util.function.UnaryOperator;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
@@ -43,6 +44,7 @@ import org.apache.logging.log4j.Logger;
import org.apache.poi.logging.PoiLogManager;
import org.apache.poi.ss.format.CellFormat;
import org.apache.poi.ss.format.CellFormatResult;
+import org.apache.poi.ss.format.SimpleFraction;
import org.apache.poi.ss.formula.ConditionalFormattingEvaluator;
import org.apache.poi.ss.util.DateFormatConverter;
import org.apache.poi.ss.util.NumberToTextConverter;
@@ -552,20 +554,22 @@ public class DataFormatter {
// the '/', but Excel shows the text, e.g. "#\\:#/#" shows 23.75 as
"23:3/4", and "#_#=/=#"
// shows 3.75 as "15 =/=4": let CellFormat handle any fraction with
text or spacing in it
boolean fractionWithText = formatStr.indexOf('/') >= 0 &&
hasTextInFraction(formatStr);
+ // for the fallback formats, which are created when they are first
needed
+ final String finalFormatStr = formatStr;
+ final double finalCellValue = cellValue;
if (plainFraction || fractionWithText) {
- Format fractionFormat = createFractionFormat(formatStr);
if (fractionWithText) {
try {
+ // the format is not tried on cellValue, so the cached
format does not depend on which
+ // value comes first: a value the CellFormat fails on
falls back to FractionFormat
CellFormat cellFormat = CellFormat.getInstance(locale,
formatStr);
- // probe with a fixed value, not cellValue, so the cached
format does not depend on which
- // value comes first; a format that only fails for some
values falls back per value
- cellFormat.apply(1.25);
- return new CellFormatWrapper(cellFormat, fractionFormat);
+ return new CellFormatWrapper(cellFormat, formatStr,
+ () -> createFractionFormat(finalFormatStr,
SimpleFraction.MAX_DENOMINATOR_DIGITS));
} catch (Exception e) {
LOG.atWarn().withThrowable(e).log("Formatting failed for
format {}, falling back", formatStr);
}
}
- return fractionFormat;
+ return createFractionFormat(formatStr,
FractionFormat.MAX_DENOM_POW);
}
// DecimalFormat cannot show literal text between digit placeholders,
e.g. the
@@ -576,15 +580,19 @@ public class DataFormatter {
// the pattern, so let CellFormat handle it
if (hasLiteralDecimalFormatCannotShow(formatStr) ||
hasZeroBeforeOptionalDigit(formatStr)) {
try {
+ // a format CellFormat cannot make sense of, e.g.
"#''0#0'1#10L16EE", fails when formatting,
+ // and falls back to the format that would be used without
CellFormat
CellFormat cellFormat = CellFormat.getInstance(locale,
formatStr.replace("'%'", "\\%"));
- // a format CellFormat cannot make sense of, e.g.
"#''0#0'1#10L16EE", fails here, not when formatting
- cellFormat.apply(cellValue);
- return new CellFormatWrapper(cellFormat, null);
+ return new CellFormatWrapper(cellFormat, formatStr, () ->
createNonCellFormat(finalFormatStr, finalCellValue));
} catch (Exception e) {
LOG.atWarn().withThrowable(e).log("Formatting failed for
format {}, falling back", formatStr);
}
}
+ return createNonCellFormat(formatStr, cellValue);
+ }
+ /** the format for a number format that is not a date or a fraction,
without CellFormat */
+ private Format createNonCellFormat(String formatStr, double cellValue) {
if (numPattern.matcher(formatStr).find() ||
hasUnquotedQuestionMark(formatStr)) {
return createNumberFormat(formatStr, cellValue);
}
@@ -1014,7 +1022,7 @@ public class DataFormatter {
return sb.toString();
}
- private Format createFractionFormat(String formatStr) {
+ private Format createFractionFormat(String formatStr, int
maxDenominatorDigits) {
String[] chunks = formatStr.split(";");
for (String chunk1 : chunks) {
String chunk = chunk1.replace("?", "#");
@@ -1025,11 +1033,11 @@ public class DataFormatter {
//take the first match
if (fractionMatcher.find()) {
String wholePart = (fractionMatcher.group(1) == null) ? "" :
defaultFractionWholePartFormat;
- return new FractionFormat(wholePart, fractionMatcher.group(3));
+ return new FractionFormat(wholePart, fractionMatcher.group(3),
maxDenominatorDigits);
}
}
- return new FractionFormat(defaultFractionWholePartFormat,
defaultFractionFractionPartFormat);
+ return new FractionFormat(defaultFractionWholePartFormat,
defaultFractionFractionPartFormat, maxDenominatorDigits);
}
/**
@@ -2270,11 +2278,15 @@ public class DataFormatter {
@SuppressWarnings("serial")
private final class CellFormatWrapper extends Format {
private final CellFormat cellFormat;
- /** used for a value the CellFormat fails on; null for General */
- private final Format fallback;
- private CellFormatWrapper(CellFormat cellFormat, Format fallback) {
+ private final String formatStr;
+ /** creates the format for a value the CellFormat fails on, which is
null for General */
+ private final Supplier<Format> fallbackSupplier;
+ private Format fallback;
+ private boolean failed;
+ private CellFormatWrapper(CellFormat cellFormat, String formatStr,
Supplier<Format> fallbackSupplier) {
this.cellFormat = cellFormat;
- this.fallback = fallback;
+ this.formatStr = formatStr;
+ this.fallbackSupplier = fallbackSupplier;
}
@Override
public StringBuffer format(Object obj, StringBuffer toAppendTo,
FieldPosition pos) {
@@ -2282,8 +2294,17 @@ public class DataFormatter {
try {
text = cellFormat.apply(obj).text;
} catch (RuntimeException e) {
- // the format was checked when it was created, but this value
fails
- LOG.atDebug().withThrowable(e).log("Formatting failed for
value {}, falling back", obj);
+ // a failure is a bug in CellFormat, or a format it cannot
make sense of:
+ // warn the first time, without filling the log when every
value fails
+ if (!failed) {
+ failed = true;
+ fallback = fallbackSupplier.get();
+ LOG.atWarn().withThrowable(e).log("Formatting failed for
format {} and value {}, falling back",
+ formatStr, obj);
+ } else {
+ LOG.atDebug().withThrowable(e).log("Formatting failed for
format {} and value {}, falling back",
+ formatStr, obj);
+ }
return (fallback != null ? fallback :
generalNumberFormat).format(obj, toAppendTo, pos);
}
return toAppendTo.append(emulateCSV ? text : text.trim());
diff --git a/poi/src/main/java/org/apache/poi/ss/usermodel/FractionFormat.java
b/poi/src/main/java/org/apache/poi/ss/usermodel/FractionFormat.java
index 3fbd195f33..ea2980b463 100644
--- a/poi/src/main/java/org/apache/poi/ss/usermodel/FractionFormat.java
+++ b/poi/src/main/java/org/apache/poi/ss/usermodel/FractionFormat.java
@@ -53,7 +53,7 @@ public class FractionFormat extends Format {
//but as of this writing, the numerators and denominators
//with formats of that nature on very small values were quite
//far from Excel's calculations
- private static final int MAX_DENOM_POW = 4;
+ static final int MAX_DENOM_POW = 4;
//there are two options:
//a) an exact denominator is specified in the formatString
@@ -68,6 +68,15 @@ public class FractionFormat extends Format {
* @param denomFormatString The format string for the denominator
*/
public FractionFormat(String wholePartFormatString, String
denomFormatString) {
+ this(wholePartFormatString, denomFormatString, MAX_DENOM_POW);
+ }
+
+ /**
+ * @param wholePartFormatString the format string for the whole part
+ * @param denomFormatString the format string for the denominator
+ * @param maxDenominatorDigits the most denominator digits, for a
denominator given as digit placeholders
+ */
+ FractionFormat(String wholePartFormatString, String denomFormatString, int
maxDenominatorDigits) {
this.wholePartFormatString = wholePartFormatString;
// initialize exactDenom and maxDenom
@@ -91,7 +100,7 @@ public class FractionFormat extends Format {
}
} else if (m.group(1) != null) {
int len = m.group(1).length();
- len = Math.min(len, MAX_DENOM_POW);
+ len = Math.min(len, maxDenominatorDigits);
tmpMax = MathUtil.safeDoubleToInt(Math.pow(10, len));
} else {
tmpExact = 100;
diff --git a/poi/src/test/java/org/apache/poi/ss/format/TestCellFormat.java
b/poi/src/test/java/org/apache/poi/ss/format/TestCellFormat.java
index 554de36601..6589de6555 100644
--- a/poi/src/test/java/org/apache/poi/ss/format/TestCellFormat.java
+++ b/poi/src/test/java/org/apache/poi/ss/format/TestCellFormat.java
@@ -951,12 +951,20 @@ class TestCellFormat {
@Test
void testImproperFractionTooLarge() {
- // the numerator is an int, so this cannot be shown as 6000000001/2
- assertThrows(IllegalArgumentException.class, () ->
CellFormat.getInstance("?/?").apply(3e9 + 0.5));
- assertThrows(IllegalArgumentException.class, () ->
CellFormat.getInstance("?/16").apply(2.5e8 + 0.5));
+ // the numerator does not fit in an int
+ assertEquals("6000000001/2", CellFormat.getInstance("?/?").apply(3e9 +
0.5).text);
+ assertEquals("4000000008/16",
CellFormat.getInstance("?/16").apply(2.5e8 + 0.5).text);
+ assertEquals("100000000000000000000/1",
CellFormat.getInstance("?/?").apply(1e20).text);
+ assertEquals("6000000001=/=2 ",
CellFormat.getInstance("??\\=/=??").apply(3e9 + 0.5).text);
assertEquals("3000000000 1/2", CellFormat.getInstance("#
?/?").apply(3e9 + 0.5).text);
}
+ @Test
+ void testFractionWholeNumberAboveLongMax() {
+ assertEquals("100000000000000000000 ", CellFormat.getInstance("#
?/?").apply(1e20).text);
+ assertEquals("-100000000000000000000 ", CellFormat.getInstance("#
?/?").apply(-1e20).text);
+ }
+
@Test
void testImproperFractionNumeratorOverflow() {
// the numerator overflows an int long before the denominator reaches
9 digits
diff --git
a/poi/src/test/java/org/apache/poi/ss/usermodel/TestDataFormatter.java
b/poi/src/test/java/org/apache/poi/ss/usermodel/TestDataFormatter.java
index 115b3afca2..9feac7e642 100644
--- a/poi/src/test/java/org/apache/poi/ss/usermodel/TestDataFormatter.java
+++ b/poi/src/test/java/org/apache/poi/ss/usermodel/TestDataFormatter.java
@@ -473,20 +473,20 @@ class TestDataFormatter {
}
@Test
- void testFractionWithTextFallsBackForValueTooLarge() {
+ void testFractionWithTextValueTooLargeForInt() {
DataFormatter dfUS = new DataFormatter(Locale.US);
- // CellFormat cannot show an improper fraction this large, so
FractionFormat does, without the text
+ // the numerator of the improper fraction does not fit in an int
assertEquals("13=/=4", dfUS.formatRawCellContents(3.25, -1,
"??\\=/=??"));
- assertEquals("6000000001/2", dfUS.formatRawCellContents(3e9 + 0.5, -1,
"??\\=/=??"));
+ assertEquals("6000000001=/=2", dfUS.formatRawCellContents(3e9 + 0.5,
-1, "??\\=/=??"));
}
@Test
- void testFractionWithTextFirstValueTooLarge() {
+ void testFractionWithTextFirstValueTooLargeForInt() {
DataFormatter dfUS = new DataFormatter(Locale.US);
- // a first value too large to show must not decide the cached format
for later values
- assertEquals("6000000001/2", dfUS.formatRawCellContents(3e9 + 0.5, -1,
"??\\=/=??"));
+ // the first value does not decide the cached format for later values
+ assertEquals("6000000001=/=2", dfUS.formatRawCellContents(3e9 + 0.5,
-1, "??\\=/=??"));
assertEquals("13=/=4", dfUS.formatRawCellContents(3.25, -1,
"??\\=/=??"));
}
diff --git
a/poi/src/test/java/org/apache/poi/ss/usermodel/TestFractionFormat.java
b/poi/src/test/java/org/apache/poi/ss/usermodel/TestFractionFormat.java
index 2cb8a67da9..3068fb4673 100644
--- a/poi/src/test/java/org/apache/poi/ss/usermodel/TestFractionFormat.java
+++ b/poi/src/test/java/org/apache/poi/ss/usermodel/TestFractionFormat.java
@@ -36,6 +36,13 @@ import org.junit.jupiter.api.Test;
* Largely taken from bug #54686
*/
final class TestFractionFormat {
+ @Test
+ void testMaxDenominatorDigits() {
+ // the denominator is capped at 4 digits, unless DataFormatter asks
for more
+ assertEquals("10/81", new FractionFormat("",
"#########").format(0.123456789));
+ assertEquals("13566680/109890109", new FractionFormat("", "#########",
9).format(0.123456789));
+ }
+
@Test
void testSingle() {
FractionFormat f = new FractionFormat("", "##");
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]