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 _€;[Red]-#,##0.00 _€</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]