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 7a9f1acdea DataFormatter: allow overriding the format strings of 
built-in formats (#1425)
7a9f1acdea is described below

commit 7a9f1acdea52ab7ef503ab0c5d6d7a8bce15a093
Author: PJ Fanning <[email protected]>
AuthorDate: Fri Oct 16 23:14:17 2026 +0100

    DataFormatter: allow overriding the format strings of built-in formats 
(#1425)
    
    Excel does not store the format string of most built-in formats, and
    shows some of them (e.g. 37-40) differently depending on its locale.
    setBuiltinFormatOverride lets callers supply the format string for the
    workbook's locale; it applies only where the cell has the default
    (en-US) format string for the index.
    
    See https://github.com/apache/poi/issues/977
    
    Co-authored-by: Claude Opus 5.5 <[email protected]>
---
 .../org/apache/poi/ss/usermodel/DataFormatter.java | 65 +++++++++++++++++++++-
 .../apache/poi/ss/usermodel/TestDataFormatter.java | 41 ++++++++++++++
 2 files changed, 103 insertions(+), 3 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 906e5f5a68..6c208b2b70 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
@@ -222,6 +222,9 @@ public class DataFormatter {
      */
     private final Map<String,Format> formats = new HashMap<>();
 
+    /** format strings to use in place of the default (en-US) strings of 
built-in formats, keyed by format index */
+    private final Map<Integer,String> builtinFormatOverrides = new HashMap<>();
+
     /** whether CSV friendly adjustments should be made to the formatted text 
**/
     private boolean emulateCSV = false;
 
@@ -377,7 +380,7 @@ public class DataFormatter {
         }
 
         int formatIndex = numFmt.getIdx();
-        String formatStr = numFmt.getFormat();
+        String formatStr = applyBuiltinFormatOverride(formatIndex, 
numFmt.getFormat());
         if(StringUtil.isBlank(formatStr)) {
             return null;
         }
@@ -473,7 +476,7 @@ public class DataFormatter {
     public Format createFormat(Cell cell) {
 
         int formatIndex = cell.getCellStyle().getDataFormat();
-        String formatStr = cell.getCellStyle().getDataFormatString();
+        String formatStr = applyBuiltinFormatOverride(formatIndex, 
cell.getCellStyle().getDataFormatString());
         return createFormat(cell.getNumericCellValue(), formatIndex, 
formatStr);
     }
 
@@ -1640,9 +1643,11 @@ public class DataFormatter {
      *  format index and string, according to excel style rules.
      * @see #formatCellValue(Cell)
      */
-    public String formatRawCellContents(double value, int formatIndex, String 
formatString, boolean use1904Windowing) {
+    public String formatRawCellContents(double value, int formatIndex, String 
formatStringIn, boolean use1904Windowing) {
         checkForLocaleChange();
 
+        String formatString = applyBuiltinFormatOverride(formatIndex, 
formatStringIn);
+
         // Excel shows a negative elapsed time, e.g. for [h]:mm, with a minus 
sign
         if (value < 0 && isElapsedTimeFormat(formatIndex, formatString)) {
             return "-" + formatRawCellContents(-value, formatIndex, 
formatString, use1904Windowing);
@@ -1862,6 +1867,60 @@ public class DataFormatter {
         formats.put(excelFormatStr, format);
     }
 
+    /**
+     * Sets the format string to use for a built-in format, in place of the 
default (en-US) one
+     * from {@link BuiltinFormats}.
+     * <p>
+     * Excel does not store the format string of most built-in formats in a 
workbook, just the
+     * format index, and it displays some of them (for example 37-40) 
differently depending on
+     * the locale of the Excel installation. POI only knows the en-US 
versions, so for example
+     * index 40 is formatted as {@code #,##0.00_);[Red](#,##0.00)}, whereas 
German Excel shows it
+     * as <code>#,##0.00 _&#8364;;[Red]-#,##0.00 _&#8364;</code>. Use this 
method to supply the format string
+     * that matches the locale the workbook was made for.
+     * </p>
+     * <p>
+     * The override only applies when the format string of the cell is the 
default one for that
+     * index, so a format string that the workbook explicitly stores for the 
index is still used.
+     * </p>
+     *
+     * @param formatIndex index of a built-in format, see {@link 
BuiltinFormats}
+     * @param formatString the format string to use for the index, or {@code 
null} to remove an override
+     * @throws IllegalArgumentException if {@code formatIndex} is not the 
index of a built-in format
+     * @since 6.0.0
+     */
+    public void setBuiltinFormatOverride(int formatIndex, String formatString) 
{
+        if (BuiltinFormats.getBuiltinFormat(formatIndex) == null) {
+            throw new IllegalArgumentException("Not a built-in format index: " 
+ formatIndex);
+        }
+        if (formatString == null) {
+            builtinFormatOverrides.remove(formatIndex);
+        } else {
+            builtinFormatOverrides.put(formatIndex, formatString);
+        }
+    }
+
+    /**
+     * @param formatIndex index of a built-in format, see {@link 
BuiltinFormats}
+     * @return the format string set with {@link 
#setBuiltinFormatOverride(int, String)} for the index,
+     * or {@code null} if there is none
+     * @since 6.0.0
+     */
+    public String getBuiltinFormatOverride(int formatIndex) {
+        return builtinFormatOverrides.get(formatIndex);
+    }
+
+    private String applyBuiltinFormatOverride(int formatIndex, String 
formatString) {
+        if (builtinFormatOverrides.isEmpty()) {
+            return formatString;
+        }
+        String override = builtinFormatOverrides.get(formatIndex);
+        if (override != null && formatString != null
+                && 
formatString.equals(BuiltinFormats.getBuiltinFormat(formatIndex))) {
+            return override;
+        }
+        return formatString;
+    }
+
     // Some custom formats
 
     /**
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 cb3f73ecfd..eac1c66e5a 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
@@ -1477,4 +1477,45 @@ class TestDataFormatter {
             assertTrue(hashes.chars().allMatch(ch -> ch == '#'));
         }
     }
+
+    /**
+     * Excel does not store the format string of built-in format 40 and 
displays it according
+     * to its locale, so callers can set the format string that matches the 
workbook's locale.
+     * See https://github.com/apache/poi/issues/977
+     */
+    @Test
+    void testBuiltinFormatOverride() throws IOException {
+        String german40 = "#,##0.00 _\u20ac;[Red]-#,##0.00 _\u20ac";
+        DataFormatter formatter = new DataFormatter(Locale.US);
+        String default40 = BuiltinFormats.getBuiltinFormat(40);
+        assertEquals("(378.13)", formatter.formatRawCellContents(-378.13, 40, 
default40));
+        assertNull(formatter.getBuiltinFormatOverride(40));
+
+        formatter.setBuiltinFormatOverride(40, german40);
+        assertEquals(german40, formatter.getBuiltinFormatOverride(40));
+        assertEquals("-378.13 ", formatter.formatRawCellContents(-378.13, 40, 
default40));
+        assertEquals("378.13 ", formatter.formatRawCellContents(378.13, 40, 
default40));
+
+        // a format string that the workbook stores for the index is used as is
+        assertEquals("-378.1", formatter.formatRawCellContents(-378.13, 40, 
"0.0"));
+        // other indexes are not affected
+        assertEquals("(378.00)", formatter.formatRawCellContents(-378, 39, 
BuiltinFormats.getBuiltinFormat(39)));
+
+        try (Workbook wb = new HSSFWorkbook()) {
+            CellStyle style = wb.createCellStyle();
+            style.setDataFormat((short) 40);
+            Cell cell = wb.createSheet().createRow(0).createCell(0);
+            cell.setCellStyle(style);
+            cell.setCellValue(-378.13);
+            assertEquals("-378.13 ", formatter.formatCellValue(cell));
+            assertEquals("(378.13)", new 
DataFormatter(Locale.US).formatCellValue(cell));
+        }
+
+        formatter.setBuiltinFormatOverride(40, null);
+        assertNull(formatter.getBuiltinFormatOverride(40));
+        assertEquals("(378.13)", formatter.formatRawCellContents(-378.13, 40, 
default40));
+
+        assertThrows(IllegalArgumentException.class, () -> 
formatter.setBuiltinFormatOverride(164, german40));
+        assertThrows(IllegalArgumentException.class, () -> 
formatter.setBuiltinFormatOverride(-1, german40));
+    }
 }


---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]

Reply via email to