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]

Reply via email to