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 bd15f5bc11 DataFormatter: currency strings without a locale, and dots
in literal text (#1420)
bd15f5bc11 is described below
commit bd15f5bc11c1433725d031642bdfaf64f4c2ce72
Author: PJ Fanning <[email protected]>
AuthorDate: Fri Oct 16 22:11:38 2026 +0100
DataFormatter: currency strings without a locale, and dots in literal text
(#1420)
Bug 70146: the language info of a currency string is optional, e.g.
"#,##0.00\ [$USD]" or "[$HKD $]#,##0", but DataFormatter only recognised
[$<symbol>-<locale>], so it showed "12,345.00 [$USD]". The symbol is now
escaped character by character, so that letters and dots in it (e.g. EUR,
Bs.) are not taken as part of the pattern or as date letters.
Bug 54686: a literal dot after the digits, e.g. in "#,##0.000000000\"тг.\"":
- DataFormatter passed it to DecimalFormat unquoted, so a second decimal
point made the pattern invalid and the value fell back to General
("0.001"). Literal characters that DecimalFormat treats as special
(. , % E etc.) are now quoted, which also stops a quoted ',' scaling and
a quoted '%' multiplying the value.
- CellNumberFormatter (used for 3+ section formats such as accounting)
replaced every '.' and ',' in the format with the locale's separators,
so in a comma-decimal locale "тг." was shown as "тг,". Only the decimal
point is localised now; grouping commas are already removed by then, so
any other '.' or ',' is literal text.
Co-authored-by: Claude Opus 5.5 <[email protected]>
---
.../apache/poi/xssf/usermodel/TestXSSFBugs.java | 13 ++++
.../apache/poi/ss/format/CellNumberFormatter.java | 41 ++++++-----
.../org/apache/poi/ss/usermodel/DataFormatter.java | 76 ++++++++++++++++++---
.../apache/poi/ss/usermodel/TestDataFormatter.java | 51 ++++++++++++++
test-data/spreadsheet/bug70146.xlsx | Bin 0 -> 5617 bytes
5 files changed, 153 insertions(+), 28 deletions(-)
diff --git
a/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFBugs.java
b/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFBugs.java
index 7a541072ec..23b507dcce 100644
--- a/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFBugs.java
+++ b/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFBugs.java
@@ -4087,6 +4087,19 @@ public final class TestXSSFBugs extends
BaseTestBugzillaIssues {
}
}
+ @Test
+ void testBug70146() throws Exception {
+ try (XSSFWorkbook wb = openSampleWorkbook("bug70146.xlsx")) {
+ XSSFRow row = wb.getSheetAt(0).getRow(0);
+ DataFormatter dataFormatter = new DataFormatter(Locale.US);
+ // currency strings without the language info, e.g. [$USD]
+ assertEquals("#,##0.00\\ [$USD];\\-#,##0.00\\ [$USD]",
row.getCell(0).getCellStyle().getDataFormatString());
+ assertEquals("12,345.00 USD",
dataFormatter.formatCellValue(row.getCell(0)));
+ assertEquals("[$HKD $]#,##0",
row.getCell(1).getCellStyle().getDataFormatString());
+ assertEquals("HKD $12,345",
dataFormatter.formatCellValue(row.getCell(1)));
+ }
+ }
+
@Test
void testInvalidCellRef() throws Exception {
// https://github.com/pjfanning/excel-streaming-reader/pull/390
diff --git
a/poi/src/main/java/org/apache/poi/ss/format/CellNumberFormatter.java
b/poi/src/main/java/org/apache/poi/ss/format/CellNumberFormatter.java
index 15ff66fbbc..5595e63a75 100644
--- a/poi/src/main/java/org/apache/poi/ss/format/CellNumberFormatter.java
+++ b/poi/src/main/java/org/apache/poi/ss/format/CellNumberFormatter.java
@@ -47,6 +47,8 @@ public class CellNumberFormatter extends CellFormatter {
private final String printfFmt;
private final double scale;
private final Special decimalPoint;
+ /** where the decimal point is in {@link #desc}, shown as the locale's
decimal separator, or -1 */
+ private final int decimalPointPos;
private final Special slash;
private final Special exponent;
private final Special numerator;
@@ -194,6 +196,18 @@ public class CellNumberFormatter extends CellFormatter {
afterFractional = null;
}
+ // the decimal point moves left by one for each comma before it, which
is removed below
+ int pointPos = -1;
+ if (ph.getDecimalPoint() != null) {
+ pointPos = ph.getDecimalPoint().pos;
+ for (Special s : specials) {
+ if (s.ch == ',' && s.pos < ph.getDecimalPoint().pos) {
+ pointPos--;
+ }
+ }
+ }
+ decimalPointPos = pointPos;
+
double[] scaleByRef = {ph.getScale()};
showGroupingSeparator = interpretIntegerCommas(descBuf, specials,
decimalPoint, integerEnd(), fractionalEnd(), scaleByRef);
if (exponent == null) {
@@ -739,25 +753,18 @@ public class CellNumberFormatter extends CellFormatter {
}
}
+ /**
+ * Shows the decimal point as the locale's decimal separator. The grouping
commas have
+ * already been removed from the format, so any other '.' or ',' is
literal text, which Excel
+ * shows as it is, e.g. the dot in {@code 0.00"x."}
+ */
private String localiseFormat(String format) {
- DecimalFormatSymbols dfs = getDecimalFormatSymbols();
- if(format.contains(",") && dfs.getGroupingSeparator() != ',') {
- if(format.contains(".") && dfs.getDecimalSeparator() != '.') {
- format = replaceLast(format, "\\.", "[DECIMAL_SEPARATOR]");
- format = format.replace(',', dfs.getGroupingSeparator())
- .replace("[DECIMAL_SEPARATOR]",
Character.toString(dfs.getDecimalSeparator()));
- } else {
- format = format.replace(',', dfs.getGroupingSeparator());
- }
- } else if(format.contains(".") && dfs.getDecimalSeparator() != '.') {
- format = format.replace('.', dfs.getDecimalSeparator());
+ char decimalSeparator =
getDecimalFormatSymbols().getDecimalSeparator();
+ if (decimalPointPos < 0 || decimalPointPos >= format.length() ||
decimalSeparator == '.'
+ || format.charAt(decimalPointPos) != '.') {
+ return format;
}
- return format;
- }
-
-
- private static String replaceLast(String text, String regex, String
replacement) {
- return text.replaceFirst("(?s)(.*)" + regex, "$1" + replacement);
+ return format.substring(0, decimalPointPos) + decimalSeparator +
format.substring(decimalPointPos + 1);
}
private static boolean hasChar(char ch, List<Special> numSpecials) {
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 c40609fe9d..1badd7f58b 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
@@ -140,10 +140,10 @@ public class DataFormatter {
private static final Pattern rangeConditionalPattern =
Pattern.compile("\\[\\s*(<>|<=|>=|<|>|=)\\s*-?[0-9]*\\.?[0-9]+\\s*]");
/**
- * A regex to find locale patterns like [$$-1009] and [$?-452].
+ * A regex to find locale patterns like [$$-1009] and [$?-452], or a
currency string without a locale like [$USD].
* Note that we don't currently process these into locales
*/
- private static final Pattern localePatternGroup =
Pattern.compile("(\\[\\$[^-\\]]*-[0-9A-Z]+])");
+ private static final Pattern localePatternGroup =
Pattern.compile("(\\[\\$[^-\\]]*(?:-[0-9A-Za-z]+)?])");
/**
* A regex to match the colour formatting's rules.
@@ -506,20 +506,25 @@ public class DataFormatter {
formatStr = dbNumPattern.matcher(formatStr).replaceAll("");
}
- // Strip off the locale information, we use an instance-wide locale
for everything
+ // Strip off the locale information, we use an instance-wide locale
for everything.
+ // The locale is optional, e.g. "[$USD]" is just the currency string
if (formatStr != null) {
Matcher m = localePatternGroup.matcher(formatStr);
+ StringBuilder sb = new StringBuilder(formatStr.length());
while (m.find()) {
String match = m.group();
- String symbol = match.substring(match.indexOf('$') + 1,
match.indexOf('-'));
- if (symbol.indexOf('$') > -1) {
- symbol = symbol.substring(0, symbol.indexOf('$')) +
- '\\' +
- symbol.substring(symbol.indexOf('$'));
+ int dash = match.indexOf('-');
+ String symbol = match.substring(2, dash < 0 ? match.length() -
1 : dash);
+ // escaped, so that letters and dots in it, e.g. "EUR" or
"Bs.", are not taken as part of the pattern
+ // (quoted text is not used, as date letters in it still make
it count as a date format)
+ StringBuilder escaped = new StringBuilder(symbol.length() * 2);
+ for (int i = 0; i < symbol.length(); i++) {
+ escaped.append('\\').append(symbol.charAt(i));
}
- formatStr = m.replaceAll(symbol);
- m = localePatternGroup.matcher(formatStr);
+ m.appendReplacement(sb,
Matcher.quoteReplacement(escaped.toString()));
}
+ m.appendTail(sb);
+ formatStr = sb.toString();
}
// Check for special cases
@@ -1047,6 +1052,55 @@ public class DataFormatter {
return sb.toString();
}
+ /** characters that DecimalFormat does not show as they are, unless they
are quoted */
+ private static final String DECIMAL_FORMAT_SPECIALS = "0#.,;%‰E¤";
+
+ /**
+ * Quotes the characters in Excel's {@code "quoted text"} and {@code
\}-escaped characters
+ * that DecimalFormat would otherwise treat as part of the pattern, e.g.
the dot in {@code 0.00"x."}:
+ * a second decimal point makes the pattern invalid, or is shown as the
locale's decimal separator.
+ */
+ private static String quoteSpecialLiterals(String formatStr) {
+ if (formatStr.indexOf('"') < 0 && formatStr.indexOf('\\') < 0) {
+ return formatStr;
+ }
+ StringBuilder sb = new StringBuilder(formatStr.length() + 8);
+ boolean inExcelQuotes = false;
+ boolean inPatternQuotes = false;
+ for (int i = 0; i < formatStr.length(); i++) {
+ char c = formatStr.charAt(i);
+ boolean literal = inExcelQuotes;
+ if (c == '"') {
+ inExcelQuotes = !inExcelQuotes;
+ sb.append(c);
+ continue;
+ } else if (!inExcelQuotes && (c == '_' || c == '*') && i <
formatStr.length() - 1) {
+ // spacing and padding: the operand is removed later
+ if (inPatternQuotes) {
+ sb.append('\'');
+ inPatternQuotes = false;
+ }
+ sb.append(c).append(formatStr.charAt(++i));
+ continue;
+ } else if (!inExcelQuotes && c == '\\' && i < formatStr.length() -
1) {
+ sb.append(c);
+ c = formatStr.charAt(++i);
+ literal = true;
+ }
+ boolean special = literal && DECIMAL_FORMAT_SPECIALS.indexOf(c) >=
0;
+ if (special != inPatternQuotes) {
+ sb.append('\'');
+ inPatternQuotes = special;
+ }
+ // a quote character is written as two, in and out of
DecimalFormat's quoted text
+ sb.append(literal && c == '\'' ? "''" : String.valueOf(c));
+ }
+ if (inPatternQuotes) {
+ sb.append('\'');
+ }
+ return sb.toString();
+ }
+
private static class InternalDecimalFormatWithScale extends Format {
private static final Pattern endsWithCommas = Pattern.compile("(,+)$");
@@ -1371,7 +1425,7 @@ public class DataFormatter {
}
private Format createNumberFormat(String formatStr, double cellValue) {
- String format = cleanFormatForNumber(formatStr);
+ String format = cleanFormatForNumber(quoteSpecialLiterals(formatStr));
DecimalFormatSymbols symbols = decimalSymbols;
// Do we need to change the grouping character?
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 ca994aa6f7..7555d42df1 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
@@ -960,6 +960,57 @@ class TestDataFormatter {
assertEquals("5 Hi!", dfUS.formatRawCellContents(5, -1, "0\" Hi!\""));
}
+ /**
+ * Bug 54686: text after the digits that has characters DecimalFormat
treats as special,
+ * e.g. a currency abbreviation that ends with a dot
+ */
+ @Test
+ void testFormatWithSpecialCharactersInLiteral() {
+ String currency = "#,##0.000000000\"тг.\"";
+ String accounting = "_-* #,##0.000000000\"тг.\"_-;\\-*
#,##0.000000000\"тг.\"_-;_-* \"-\"?????????\"тг.\"_-;_-@_-";
+
+ DataFormatter dfUS = new DataFormatter(Locale.US);
+ assertEquals("0.00x.", dfUS.formatRawCellContents(0.001, -1,
"0.00\"x.\""));
+ assertEquals("1.5.", dfUS.formatRawCellContents(1.5, -1, "0.0\\."));
+ assertEquals("0.001000000тг.", dfUS.formatRawCellContents(0.001, -1,
currency));
+ assertEquals("0.001000000тг.", dfUS.formatRawCellContents(0.001, -1,
accounting));
+ // a quoted comma does not scale the value, a quoted percent sign does
not multiply it
+ assertEquals("1.5 ,", dfUS.formatRawCellContents(1.5, -1, "0.0\"
,\""));
+ assertEquals("5 %", dfUS.formatRawCellContents(5, -1, "0\" %\""));
+ assertEquals("5's", dfUS.formatRawCellContents(5, -1, "0\"'s\""));
+
+ // the quoted dot is not the decimal separator, which is a comma in
Kazakhstan
+ DataFormatter dfKZ = new DataFormatter(Locale.forLanguageTag("kk-KZ"));
+ assertEquals("0,00x.", dfKZ.formatRawCellContents(0.001, -1,
"0.00\"x.\""));
+ assertEquals("1,5.", dfKZ.formatRawCellContents(1.5, -1, "0.0\\."));
+ assertEquals("0,001000000тг.", dfKZ.formatRawCellContents(0.001, -1,
currency));
+ assertEquals("0,001000000тг.", dfKZ.formatRawCellContents(0.001, -1,
accounting));
+ }
+
+ /**
+ * Bug 70146: the language info of a currency string is optional, e.g.
[$USD]
+ */
+ @Test
+ void testCurrencyStringWithoutLanguageInfo() {
+ DataFormatter dfUS = new DataFormatter(Locale.US);
+ // the formats of the bug's attachment
+ assertEquals("12,345.00 USD", dfUS.formatRawCellContents(12345, -1,
"#,##0.00\\ [$USD];\\-#,##0.00\\ [$USD]"));
+ assertEquals("-12,345.00 USD", dfUS.formatRawCellContents(-12345, -1,
"#,##0.00\\ [$USD];\\-#,##0.00\\ [$USD]"));
+ assertEquals("HKD $12,345", dfUS.formatRawCellContents(12345, -1,
"[$HKD $]#,##0"));
+ assertEquals("12,345.00 USD", dfUS.formatRawCellContents(12345, -1,
"#,##0.00 [$USD]"));
+ // currency strings with date letters are not a date format
+ assertEquals("USD 5", dfUS.formatRawCellContents(5, -1, "[$USD] 0"));
+ assertEquals("EUR 12,345.00", dfUS.formatRawCellContents(12345, -1,
"[$EUR] #,##0.00"));
+ assertEquals("USD 5", dfUS.formatRawCellContents(5, -1, "[$USD]
0;[$EUR] -0"));
+ assertEquals("EUR -5", dfUS.formatRawCellContents(-5, -1, "[$USD]
0;[$EUR] -0"));
+ // with the language info
+ assertEquals("$12,345.00", dfUS.formatRawCellContents(12345, -1,
"[$$-1009]#,##0.00"));
+ assertEquals("Bs. 12,345.00", dfUS.formatRawCellContents(12345, -1,
"[$Bs.-400A] #,##0.00"));
+ assertEquals("12,345.00 €", dfUS.formatRawCellContents(12345, -1,
"#,##0.00 [$€-407]"));
+ // with 3 or more sections, CellFormat formats it
+ assertEquals("12,345.00 USD", dfUS.formatRawCellContents(12345, -1,
"#,##0.00 [$USD];-#,##0.00 [$USD];0 [$USD]"));
+ }
+
/**
* Chinese date and time formats. Most of these are the zh-CN built-in
number formats
* from ECMA-376 Part 1, 18.8.30 (numFmt), e.g. ids 31 to 35.
diff --git a/test-data/spreadsheet/bug70146.xlsx
b/test-data/spreadsheet/bug70146.xlsx
new file mode 100644
index 0000000000..70f31dcbe7
Binary files /dev/null and b/test-data/spreadsheet/bug70146.xlsx differ
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]