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 e835897b1f DataFormatter: a '0' placeholder before '#' placeholders,
e.g. 60######### (#1424)
e835897b1f is described below
commit e835897b1fd2074b26877fd6cd1a9a294944d4ff
Author: PJ Fanning <[email protected]>
AuthorDate: Fri Oct 16 22:57:54 2026 +0100
DataFormatter: a '0' placeholder before '#' placeholders, e.g. 60#########
(#1424)
Bug 61638: DecimalFormat does not accept a '0' placeholder followed by '#'
or '?' placeholders, so DataFormatter fell back to General: "60#########"
showed 166609647 as "166609647" instead of "60166609647". These formats
are now formatted by CellFormat, which shows the 0 only in its own position,
as Excel does (checked with Excel's TEXT function: 12345678 shows as
"6012345678", and 5 with "0##" as "05").
A format that CellFormat cannot make sense of now falls back as before,
instead of failing when the value is formatted.
Co-authored-by: Claude Opus 5.5 <[email protected]>
---
.../org/apache/poi/ss/usermodel/DataFormatter.java | 48 +++++++++++++++++++++-
.../apache/poi/ss/usermodel/TestDataFormatter.java | 25 +++++++++++
2 files changed, 71 insertions(+), 2 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 1badd7f58b..906e5f5a68 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
@@ -577,9 +577,15 @@ 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
- if (hasLiteralBetweenDigits(formatStr)) {
+ // 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)) {
try {
- return new CellFormatWrapper(CellFormat.getInstance(locale,
formatStr.replace("'%'", "\\%")));
+ 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);
} catch (Exception e) {
LOG.atWarn().withThrowable(e).log("Formatting failed for
format {}, falling back", formatStr);
}
@@ -973,6 +979,44 @@ public class DataFormatter {
return false;
}
+ /**
+ * Whether the integer part of any section of the number format has a
{@code 0} placeholder
+ * followed by a {@code #} or {@code ?} placeholder, e.g. {@code 0##} or
{@code 60#########}.
+ */
+ private static boolean hasZeroBeforeOptionalDigit(String formatStr) {
+ boolean seenZero = false;
+ boolean inInteger = true;
+ 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;
+ }
+ case '[' -> {
+ int end = formatStr.indexOf(']', i + 1);
+ i = end < 0 ? formatStr.length() : end;
+ }
+ case '\\', '_', '*' -> i++;
+ case ';' -> {
+ seenZero = false;
+ inInteger = true;
+ }
+ case '.', 'E', 'e' -> inInteger = false;
+ case '0' -> seenZero |= inInteger;
+ case '#', '?' -> {
+ if (seenZero && inInteger) {
+ return true;
+ }
+ }
+ default -> {
+ // other characters don't affect the result
+ }
+ }
+ }
+ return false;
+ }
+
private String cleanFormatForNumber(String formatStrIn) {
// this replace is done to fix
https://bz.apache.org/bugzilla/show_bug.cgi?id=63211
String formatStr = formatStrIn.replace("\\%", "'%'");
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 7555d42df1..cb3f73ecfd 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,31 @@ class TestDataFormatter {
}
}
+ /**
+ * Bug 61638: a '0' placeholder before '#' placeholders, which
DecimalFormat does not accept.
+ * The expected values were checked with Excel's TEXT function.
+ */
+ @Test
+ void testZeroBeforeOptionalDigits() throws IOException {
+ DataFormatter dfUS = new DataFormatter(Locale.US);
+ // the 6 is literal text; the 0 is shown only in its own position
+ assertEquals("60166609647", dfUS.formatRawCellContents(166609647, -1,
"60#########"));
+ assertEquals("6012345678", dfUS.formatRawCellContents(12345678, -1,
"60#########"));
+ assertEquals("60012345678", dfUS.formatRawCellContents(12345678, -1,
"60000000000"));
+ assertEquals("05", dfUS.formatRawCellContents(5, -1, "0##"));
+ assertEquals("123", dfUS.formatRawCellContents(123, -1, "0##"));
+ assertEquals("1234", dfUS.formatRawCellContents(1234, -1, "0##"));
+
+ try (Workbook wb = new HSSFWorkbook()) {
+ Cell cell = wb.createSheet().createRow(0).createCell(0);
+ CellStyle style = wb.createCellStyle();
+
style.setDataFormat(wb.createDataFormat().getFormat("60#########"));
+ cell.setCellStyle(style);
+ cell.setCellValue(166609647);
+ assertEquals("60166609647", dfUS.formatCellValue(cell));
+ }
+ }
+
@Test
void testFormatWithExclamationEscape() {
DataFormatter dfUS = new DataFormatter(Locale.US);
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]