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/17 08:39:26 UTC
(poi) branch trunk updated: DataFormatter: format literal text after trailing commas with CellFormat (#1426)
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 598e21ca4e DataFormatter: format literal text after trailing commas with CellFormat (#1426)
598e21ca4e is described below
commit 598e21ca4ee6e9f34d79a0c7411f8e6c802512c6
Author: PJ Fanning <pj...@users.noreply.github.com>
AuthorDate: Sat Oct 17 09:39:20 2026 +0100
DataFormatter: format literal text after trailing commas with CellFormat (#1426)
* DataFormatter: format literal text after trailing commas with CellFormat
Formats with quoted or escaped text after trailing scaling commas, such as
0.0,"δΈ‡" or #,##0,,"M", fell back to the raw value because the stripped
pattern is not valid for DecimalFormat, and a quoted comma in #,##0.0,","
was treated as another scaling comma. Route these sections to CellFormat,
like formats with literal text between digit placeholders.
Reported in apache/fesod#1232, whose DataFormatter was ported from POI.
Co-Authored-By: Claude Opus 5.5 <no...@anthropic.com>
* Note that the trailing-comma literal cases were checked with Excel
Co-Authored-By: Claude Opus 5.5 <no...@anthropic.com>
---------
Co-authored-by: Claude Opus 5.5 <no...@anthropic.com>
---
.../org/apache/poi/ss/usermodel/DataFormatter.java | 21 +++++++++++++++++----
.../apache/poi/ss/usermodel/TestDataFormatter.java | 17 +++++++++++++++++
2 files changed, 34 insertions(+), 4 deletions(-)
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 6c208b2b70..61a3b13335 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
@@ -579,11 +579,12 @@ public class DataFormatter {
}
// DecimalFormat cannot show literal text between digit placeholders, e.g. the
- // escaped dot in "0\\.0," which displays 12345 as "1.2", so let CellFormat handle it
+ // escaped dot in "0\\.0," which displays 12345 as "1.2", or after trailing scaling
+ // commas, e.g. "#,##0,,\"M\"", so let CellFormat handle it
// Excel shows a '0' placeholder before '#' or '?' placeholders only in its own position,
// e.g. "60#########" shows 12345678 as "6012345678", but DecimalFormat does not accept
// the pattern, so let CellFormat handle it
- if (hasLiteralBetweenDigits(formatStr) || hasZeroBeforeOptionalDigit(formatStr)) {
+ if (hasLiteralDecimalFormatCannotShow(formatStr) || hasZeroBeforeOptionalDigit(formatStr)) {
try {
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
@@ -942,17 +943,22 @@ public class DataFormatter {
/**
* Whether any section of the number format has quoted or escaped text with a digit
- * placeholder both before and after it, e.g. {@code 0\.0} or {@code 0"-"00}.
+ * placeholder both before and after it, e.g. {@code 0\.0} or {@code 0"-"00}, or
+ * after trailing scaling commas, e.g. {@code 0.0,"K"} or {@code #,##0.0,","}.
*/
- private static boolean hasLiteralBetweenDigits(String formatStr) {
+ private static boolean hasLiteralDecimalFormatCannotShow(String formatStr) {
boolean seenDigit = false;
boolean literalAfterDigit = false;
+ boolean commaAfterDigit = false;
for (int i = 0; i < formatStr.length(); i++) {
char c = formatStr.charAt(i);
switch (c) {
case '"' -> {
int end = formatStr.indexOf('"', i + 1);
i = end < 0 ? formatStr.length() : end;
+ if (commaAfterDigit) {
+ return true;
+ }
literalAfterDigit |= seenDigit;
}
case '[' -> {
@@ -961,18 +967,25 @@ public class DataFormatter {
}
case '\\' -> {
i++;
+ if (commaAfterDigit) {
+ return true;
+ }
literalAfterDigit |= seenDigit;
}
+ case ',' -> commaAfterDigit |= seenDigit;
case '_', '*' -> i++;
case ';' -> {
seenDigit = false;
literalAfterDigit = false;
+ commaAfterDigit = false;
}
case '0', '#', '?' -> {
if (literalAfterDigit) {
return true;
}
seenDigit = true;
+ // a comma followed by a digit placeholder is a grouping separator, not a scaler
+ commaAfterDigit = false;
}
default -> {
// other characters don't affect the result
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 eac1c66e5a..a3f5792753 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
@@ -945,6 +945,23 @@ class TestDataFormatter {
}
}
+ /**
+ * Literal text after trailing scaling commas, which DecimalFormat cannot show.
+ * The expected values were checked with Excel's TEXT function.
+ */
+ @Test
+ void testFormatWithLiteralAfterTrailingCommas() {
+ DataFormatter dfUS = new DataFormatter(Locale.US);
+ assertEquals("1234.6\u4e07", dfUS.formatRawCellContents(1234567, -1, "0.0,\"\u4e07\""));
+ assertEquals("1M", dfUS.formatRawCellContents(1234567, -1, "#,##0,,\"M\""));
+ // a quoted comma is literal text, not a trailing comma
+ assertEquals("1,234.6,", dfUS.formatRawCellContents(1234567, -1, "#,##0.0,\",\""));
+ assertEquals("1,234.6K", dfUS.formatRawCellContents(1234567, -1, "#,##0.0,\\K"));
+ assertEquals("-1,235K", dfUS.formatRawCellContents(-1234567, -1, "#,##0,\"K\""));
+ // grouping commas are not trailing commas
+ assertEquals("1,234,567 units", dfUS.formatRawCellContents(1234567, -1, "#,##0\" units\""));
+ }
+
/**
* Bug 61638: a '0' placeholder before '#' placeholders, which DecimalFormat does not accept.
* The expected values were checked with Excel's TEXT function.
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org