You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@fesod.apache.org by fa...@apache.org on 2026/10/16 14:31:22 UTC
(fesod) 01/01: fix: DataFormatter trailing-comma scaling and literals between digits
This is an automated email from the ASF dual-hosted git repository.
pjfanning pushed a commit to branch fix/dataformatter-trailing-commas
in repository https://gitbox.apache.org/repos/asf/fesod.git
commit 96507883bf6c5c8c15ec1c96e38b5d924565abc9
Author: PJ Fanning <pj...@users.noreply.github.com>
AuthorDate: Fri Oct 16 15:31:11 2026 +0100
fix: DataFormatter trailing-comma scaling and literals between digits
Port of apache/poi#1396.
- Trailing commas scaled by an extra power of ten for each character between
the decimal point and the comma (the issue #2319 workaround), e.g. "0,"
showed 1234567 as "124" instead of "1235". Remove that and divide exactly,
so formats such as "#,##0.0," keep their decimal places.
- Excel treats '!' as an escape like '\'; rewrite it as '\' outside quotes,
brackets and '_'/'*' operands.
- Number formats with quoted or escaped text between digit placeholders, e.g.
"0\.0," (issue #2319: shows 9234 as "0.9"), cannot be expressed with
DecimalFormat, so they are now formatted by CellFormat.
Co-Authored-By: Claude Opus 5.5 <no...@anthropic.com>
---
.../fesod/sheet/metadata/format/DataFormatter.java | 120 +++++++++++++++++++--
.../sheet/metadata/format/DataFormatterTest.java | 56 ++++++++++
.../format/TrailingCommaFormatReadTest.java | 48 +++++++++
3 files changed, 214 insertions(+), 10 deletions(-)
diff --git a/fesod-sheet/src/main/java/org/apache/fesod/sheet/metadata/format/DataFormatter.java b/fesod-sheet/src/main/java/org/apache/fesod/sheet/metadata/format/DataFormatter.java
index 8627f810c..64b644025 100644
--- a/fesod-sheet/src/main/java/org/apache/fesod/sheet/metadata/format/DataFormatter.java
+++ b/fesod-sheet/src/main/java/org/apache/fesod/sheet/metadata/format/DataFormatter.java
@@ -124,8 +124,6 @@ public class DataFormatter {
*/
private static final String invalidDateTimeString;
- private static final BigDecimal TEN = new BigDecimal(10);
-
static {
StringBuilder buf = new StringBuilder();
for (int i = 0; i < 255; i++) {
@@ -204,7 +202,7 @@ public class DataFormatter {
// String[] formatBits = formatStrIn.split(";");
// int i = cellValue > 0.0 ? 0 : cellValue < 0.0 ? 1 : 2;
// String formatStr = (i < formatBits.length) ? formatBits[i] : formatBits[0];
- String formatStr = dataFormatString;
+ String formatStr = convertExclamationEscapes(dataFormatString);
// Excel supports 2+ part conditional data formats, eg positive/negative/zero,
// or (>1000),(>0),(0),(negative). As Java doesn't handle these kinds
@@ -327,12 +325,98 @@ public class DataFormatter {
return new FractionFormat(defaultFractionWholePartFormat, defaultFractionFractionPartFormat);
}
+ // 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)) {
+ try {
+ return new CellFormatWrapper(CellFormat.getInstance(locale, formatStr.replace("'%'", "\\%")));
+ } catch (Exception e) {
+ log.warn("Formatting failed for format {}, falling back", formatStr, e);
+ }
+ }
+
if (numPattern.matcher(formatStr).find()) {
return createNumberFormat(formatStr);
}
return getDefaultFormat();
}
+ /**
+ * Excel treats {@code !} like {@code \}: it displays the next character literally.
+ * Rewrite such escapes as {@code \}, which the rest of the formatting code understands.
+ * Quoted text, bracketed sections and the operands of {@code _} and {@code *} are left alone.
+ */
+ private static String convertExclamationEscapes(String formatStr) {
+ if (formatStr == null || formatStr.indexOf('!') < 0) {
+ return formatStr;
+ }
+ StringBuilder sb = new StringBuilder(formatStr.length());
+ for (int i = 0; i < formatStr.length(); i++) {
+ char c = formatStr.charAt(i);
+ if (c == '"' || c == '[') {
+ int end = formatStr.indexOf(c == '"' ? '"' : ']', i + 1);
+ end = end < 0 ? formatStr.length() - 1 : end;
+ sb.append(formatStr, i, end + 1);
+ i = end;
+ } else if ((c == '\\' || c == '_' || c == '*') && i < formatStr.length() - 1) {
+ sb.append(c).append(formatStr.charAt(++i));
+ } else if (c == '!' && i < formatStr.length() - 1) {
+ sb.append('\\').append(formatStr.charAt(++i));
+ } else {
+ sb.append(c);
+ }
+ }
+ return sb.toString();
+ }
+
+ /**
+ * Whether any section of the number format has quoted or escaped text with a digit
+ * placeholder both before and after it, e.g. {@code 0\.0} or {@code 0"-"00}.
+ */
+ private static boolean hasLiteralBetweenDigits(String formatStr) {
+ boolean seenDigit = false;
+ boolean literalAfterDigit = false;
+ 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;
+ literalAfterDigit |= seenDigit;
+ break;
+ }
+ case '[': {
+ int end = formatStr.indexOf(']', i + 1);
+ i = end < 0 ? formatStr.length() : end;
+ break;
+ }
+ case '\\':
+ i++;
+ literalAfterDigit |= seenDigit;
+ break;
+ case '_':
+ case '*':
+ i++;
+ break;
+ case ';':
+ seenDigit = false;
+ literalAfterDigit = false;
+ break;
+ case '0':
+ case '#':
+ case '?':
+ if (literalAfterDigit) {
+ return true;
+ }
+ seenDigit = true;
+ break;
+ default:
+ // other characters don't affect the result
+ }
+ }
+ return false;
+ }
+
private Format checkSpecialConverter(String dataFormatString) {
if ("00000\\-0000".equals(dataFormatString) || "00000-0000".equals(dataFormatString)) {
return new ZipPlusFourFormat();
@@ -539,17 +623,11 @@ public class DataFormatter {
setExcelStyleRoundingMode(df);
Matcher endsWithCommasMatcher = endsWithCommas.matcher(pattern);
if (endsWithCommasMatcher.find()) {
- int index_point = pattern.indexOf(".");
- int index_comma = pattern.indexOf(",");
- int cnt = index_comma - index_point - 1;
String commas = (endsWithCommasMatcher.group(1));
BigDecimal temp = BigDecimal.ONE;
for (int i = 0; i < commas.length(); ++i) {
temp = temp.multiply(ONE_THOUSAND);
}
- for (int i = 0; i < cnt; i++) {
- temp = temp.multiply(TEN);
- }
divider = temp;
} else {
divider = null;
@@ -559,7 +637,8 @@ public class DataFormatter {
private Object scaleInput(Object obj) {
if (divider != null) {
if (obj instanceof BigDecimal) {
- obj = ((BigDecimal) obj).divide(divider, RoundingMode.HALF_UP);
+ // dividing by a power of ten is exact, so keep all the digits until formatting
+ obj = ((BigDecimal) obj).divide(divider);
} else if (obj instanceof Double) {
obj = (Double) obj / divider.doubleValue();
} else {
@@ -863,6 +942,27 @@ public class DataFormatter {
}
}
+ /**
+ * Cachable wrapper around a {@link CellFormat}, used for number formats that DecimalFormat cannot handle
+ */
+ private static final class CellFormatWrapper extends Format {
+ private final CellFormat cellFormat;
+
+ private CellFormatWrapper(CellFormat cellFormat) {
+ this.cellFormat = cellFormat;
+ }
+
+ @Override
+ public StringBuffer format(Object obj, StringBuffer toAppendTo, FieldPosition pos) {
+ return toAppendTo.append(cellFormat.apply(obj).text.trim());
+ }
+
+ @Override
+ public Object parseObject(String source, ParsePosition pos) {
+ return null; // Not supported
+ }
+ }
+
/**
* Workaround until we merge {@link org.apache.poi.ss.usermodel.DataFormatter} with {@link CellFormat}. Constant,
* non-cachable wrapper around a
diff --git a/fesod-sheet/src/test/java/org/apache/fesod/sheet/metadata/format/DataFormatterTest.java b/fesod-sheet/src/test/java/org/apache/fesod/sheet/metadata/format/DataFormatterTest.java
index c2a09699d..6078e5691 100644
--- a/fesod-sheet/src/test/java/org/apache/fesod/sheet/metadata/format/DataFormatterTest.java
+++ b/fesod-sheet/src/test/java/org/apache/fesod/sheet/metadata/format/DataFormatterTest.java
@@ -171,6 +171,62 @@ class DataFormatterTest {
Assertions.assertEquals(expected, format(data, pattern));
}
+ @ParameterizedTest(name = PATTERN_AND_RESULT)
+ @CsvSource(
+ delimiter = '|',
+ value = {
+ // each trailing comma divides by 1000, and the decimal places are kept
+ " 1234567 | #,##0.0, | 1,234.6",
+ " 9234 | 0.0, | 9.2",
+ " 1234567 | 0.00,, | 1.23",
+ "-1234567 | #,##0.0, | -1,234.6",
+ " 1234567 | 0, | 1235",
+ " 1234567 | #,, | 1",
+ " 1234567 | 0.#, | 1234.6",
+ })
+ void scalesByTrailingCommas(String data, String pattern, String expected) {
+ Assertions.assertEquals(expected, format(data, pattern));
+ }
+
+ @ParameterizedTest(name = PATTERN_AND_RESULT)
+ @CsvSource(
+ delimiter = '|',
+ value = {
+ // a literal dot is not a decimal point, the digits either side of it are all integer digits
+ " 9234 | 0\\.0 | 923.4",
+ " 1234567 | 0\\.0, | 123.5",
+ " 9234 | 0\\.0, | 0.9",
+ " -9234 | 0\\.0, | -0.9",
+ " 123456 | 0\\.0, | 12.3",
+ " 1234567 | 0\".\"0, | 123.5",
+ " 1230000 | 0\\.#, | 123.0",
+ " 1234567 | 0\".0\"0, | 123.05",
+ " 1234567 | 0\\.00,;-0\\.0, | 12.35",
+ "-1234567 | 0\\.00,;-0\\.0, | -123.5",
+ })
+ void keepsLiteralTextBetweenDigits(String data, String pattern, String expected) {
+ Assertions.assertEquals(expected, format(data, pattern));
+ }
+
+ @ParameterizedTest(name = PATTERN_AND_RESULT)
+ @CsvSource(
+ delimiter = '|',
+ value = {
+ // '!' displays the next character literally, like '\'
+ " 9234 | 0!.0 | 923.4",
+ " 9234 | 0!.0, | 0.9",
+ "12345 | 0!.0000 | 1.2345",
+ "50000 | 0!.0,\"万\" | 5.0万",
+ "12345 | [Red]0!.0, | 1.2",
+ " 5 | 0!! | 5!",
+ // a '!' that is quoted or escaped is a literal
+ " 5 | 0\\! | 5!",
+ " 5 | 0\" Hi!\" | 5 Hi!",
+ })
+ void treatsExclamationMarkAsEscape(String data, String pattern, String expected) {
+ Assertions.assertEquals(expected, format(data, pattern));
+ }
+
@ParameterizedTest(name = PATTERN_AND_RESULT)
@CsvSource(
delimiter = '|',
diff --git a/fesod-sheet/src/test/java/org/apache/fesod/sheet/metadata/format/TrailingCommaFormatReadTest.java b/fesod-sheet/src/test/java/org/apache/fesod/sheet/metadata/format/TrailingCommaFormatReadTest.java
new file mode 100644
index 000000000..837568add
--- /dev/null
+++ b/fesod-sheet/src/test/java/org/apache/fesod/sheet/metadata/format/TrailingCommaFormatReadTest.java
@@ -0,0 +1,48 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+package org.apache.fesod.sheet.metadata.format;
+
+import java.util.List;
+import java.util.Map;
+import org.apache.fesod.sheet.FesodSheet;
+import org.apache.fesod.sheet.testkit.Tags;
+import org.apache.fesod.sheet.testkit.base.AbstractExcelTest;
+import org.junit.jupiter.api.Assertions;
+import org.junit.jupiter.api.Tag;
+import org.junit.jupiter.params.ParameterizedTest;
+import org.junit.jupiter.params.provider.CsvSource;
+
+/**
+ * Reads cells with the custom format {@code 0\.0,} (issue #2319): the escaped dot is literal text and the
+ * trailing comma divides by 1000, so Excel shows 9234 as {@code 0.9} and 123456 as {@code 12.3}.
+ */
+@Tag(Tags.READ)
+class TrailingCommaFormatReadTest extends AbstractExcelTest {
+
+ @ParameterizedTest
+ @CsvSource({"test1.xlsx, 9234, 0.9", "test2.xlsx, 123456, 12.3"})
+ void readsFormattedValue(String fileName, String rawValue, String formattedValue) {
+ List<Map<Integer, String>> rows =
+ FesodSheet.read(readFile("temp/issue2319/" + fileName)).sheet().doReadSync();
+ Assertions.assertEquals(1, rows.size());
+ Assertions.assertEquals(rawValue, rows.get(0).get(0));
+ Assertions.assertEquals(formattedValue, rows.get(0).get(1));
+ }
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@fesod.apache.org
For additional commands, e-mail: commits-help@fesod.apache.org