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/10/16 18:32:48 UTC
(poi) branch trunk updated: DataFormatter: show text in fraction formats, as Excel does (#1412)
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 73a83940eb DataFormatter: show text in fraction formats, as Excel does (#1412)
73a83940eb is described below
commit 73a83940eb5b98f059cb76e58d2763b1a2ce8dd7
Author: PJ Fanning <pj...@users.noreply.github.com>
AuthorDate: Fri Oct 16 19:32:43 2026 +0100
DataFormatter: show text in fraction formats, as Excel does (#1412)
Excel shows quoted and escaped text in a fraction format:
NumberFormatTests.xlsx has TEXT(23.75, "|#\:#/#|") = "|23:3/4|" and
TEXT(3.75, "|#_#/#|") = "|15 /4|". DataFormatter stripped the text,
because FractionFormat does not handle it. Now that CellFormat formats
fractions as Excel does (#1406), any fraction format with text or
spacing in it is formatted by CellFormat.
This is faster too (about 6us rather than 13us a value), and handles
denominators of more than 4 digits, which FractionFormat limits: the
Tika-1132 column of 54686_fraction_formats.xls ("#\ ??/?????????") now
matches Excel, so TestFractionFormat checks it as well.
TestDataFormatterAgainstExcel now matches Excel for all 569 cases.
TestDataFormatter.testFractions expected the text to be stripped; its
expectations now show the text, e.g. "#\"FRED\" #/#" shows 321.321 as
"321FRED 1/3".
Co-authored-by: Claude Opus 5.5 <no...@anthropic.com>
---
.../poi/ss/tests/usermodel/TestDataFormatterAgainstExcel.java | 5 +----
.../main/java/org/apache/poi/ss/usermodel/DataFormatter.java | 7 +++----
.../java/org/apache/poi/ss/usermodel/TestDataFormatter.java | 11 ++++++-----
.../java/org/apache/poi/ss/usermodel/TestFractionFormat.java | 4 ++--
4 files changed, 12 insertions(+), 15 deletions(-)
diff --git a/poi-ooxml/src/test/java/org/apache/poi/ss/tests/usermodel/TestDataFormatterAgainstExcel.java b/poi-ooxml/src/test/java/org/apache/poi/ss/tests/usermodel/TestDataFormatterAgainstExcel.java
index 7aa01a3679..a5206bfb2f 100644
--- a/poi-ooxml/src/test/java/org/apache/poi/ss/tests/usermodel/TestDataFormatterAgainstExcel.java
+++ b/poi-ooxml/src/test/java/org/apache/poi/ss/tests/usermodel/TestDataFormatterAgainstExcel.java
@@ -67,10 +67,7 @@ class TestDataFormatterAgainstExcel {
/**
* Formats where DataFormatter does not match Excel for any of the sample values.
*/
- private static final Set<String> UNSUPPORTED_FORMATS = Set.of(
- // text and spacing in a fraction are stripped (as TestDataFormatter.testFractions expects)
- "|#\\:#/#|", "|#-#-#\\:#/#|", "|#\\:? ?#/#|", "|#\\:? ?0#/000", "|#_#/#|"
- );
+ private static final Set<String> UNSUPPORTED_FORMATS = Set.of();
/**
* Format and value combinations where DataFormatter does not match Excel,
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 3d35ccc0d7..c40609fe9d 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
@@ -542,8 +542,9 @@ public class DataFormatter {
// Excel supports fractions in format strings, which Java doesn't
boolean plainFraction = formatStr.contains("#/") || formatStr.contains("?/");
// FractionFormat strips any text in a fraction, and does not recognise one with text around
- // the '/', e.g. "#_#=/=#" which Excel shows 3.75 as "15 =/=4": let CellFormat handle those
- boolean fractionWithText = !plainFraction && formatStr.indexOf('/') >= 0 && hasTextInFraction(formatStr);
+ // 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);
if (plainFraction || fractionWithText) {
if (fractionWithText) {
try {
@@ -566,8 +567,6 @@ public class DataFormatter {
}
}
- // Strip custom text in quotes and escaped characters for now as it can cause performance problems in fractions.
- //String strippedFormatStr = formatStr.replaceAll("\\\\ ", " ").replaceAll("\\\\.", "").replaceAll("\"[^\"]*\"", " ").replaceAll("\\?", "#");
return new FractionFormat(defaultFractionWholePartFormat, defaultFractionFractionPartFormat);
}
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 bf17d2fbf2..ca994aa6f7 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
@@ -336,16 +336,17 @@ class TestDataFormatter {
assertEquals("-ve", dfUS.formatRawCellContents(-1, -1, "-ve;-ve;zero;xxx"));
assertEquals("zero", dfUS.formatRawCellContents(0, -1, "zero;-ve;zero;xxx"));
- // Custom formats - check text is stripped, including multiple spaces
+ // Custom formats - quoted and escaped text is shown, as Excel does, e.g. NumberFormatTests.xlsx
+ // has TEXT(23.75, "|#\:#/#|") = "|23:3/4|"; multiple unquoted spaces are collapsed
assertEquals("321 1/3", dfUS.formatRawCellContents(321.321, -1, "# #/#"));
- assertEquals("321 1/3", dfUS.formatRawCellContents(321.321, -1, "#\" \" #/#"));
- assertEquals("321 1/3", dfUS.formatRawCellContents(321.321, -1, "#\"FRED\" #/#"));
+ assertEquals("321 1/3", dfUS.formatRawCellContents(321.321, -1, "#\" \" #/#"));
+ assertEquals("321FRED 1/3", dfUS.formatRawCellContents(321.321, -1, "#\"FRED\" #/#"));
assertEquals("321 1/3", dfUS.formatRawCellContents(321.321, -1, "#\\ #/#"));
- assertEquals("321 1/3", dfUS.formatRawCellContents(321.321, -1, "# \\q#/#"));
+ assertEquals("321 q1/3", dfUS.formatRawCellContents(321.321, -1, "# \\q#/#"));
// Cases that were very slow
assertEquals("321 1/3", dfUS.formatRawCellContents(321.321, -1, "0\" \"?/?;?/?")); // 0" "?/?;?/? - length of -ve part was used
- assertEquals("321 1/3", dfUS.formatRawCellContents(321.321, -1, "0 \"#\"\\#\\#?/?")); // 0 "#"\#\#?/? - length of text was used
+ assertEquals("321 ###1/3", dfUS.formatRawCellContents(321.321, -1, "0 \"#\"\\#\\#?/?")); // 0 "#"\#\#?/? - length of text was used
assertEquals("321 295/919", dfUS.formatRawCellContents(321.321, -1, "# #/###"));
assertEquals("321 321/1000", dfUS.formatRawCellContents(321.321, -1, "# #/####")); // Code limits to #### as that is as slow as we want to get
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 7a45018569..2cb8a67da9 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
@@ -93,8 +93,8 @@ final class TestFractionFormat {
for (int i = 1; i < sheet.getLastRowNum() && truthLine != null; i++) {
Row r = sheet.getRow(i);
String[] truths = truthLine.split("\t");
- // Intentionally ignore the last column (tika-1132), for now
- for (short j = 3; j < 12; j++) {
+ // the last column (tika-1132) has a 9 digit denominator
+ for (short j = 3; j <= 12; j++) {
Cell cell = r.getCell(j, MissingCellPolicy.CREATE_NULL_AS_BLANK);
String formatted = clean(formatter.formatCellValue(cell, evaluator));
if (truths.length <= j) {
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org