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:21 UTC
(fesod) branch fix/dataformatter-trailing-commas created (now 96507883b)
This is an automated email from the ASF dual-hosted git repository.
pjfanning pushed a change to branch fix/dataformatter-trailing-commas
in repository https://gitbox.apache.org/repos/asf/fesod.git
at 96507883b fix: DataFormatter trailing-comma scaling and literals between digits
This branch includes the following new commits:
new 96507883b fix: DataFormatter trailing-comma scaling and literals between digits
The 1 revisions listed above as "new" are entirely new to this
repository and will be described in separate emails. The revisions
listed as "add" were already present in the repository and have only
been added to this reference.
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@fesod.apache.org
For additional commands, e-mail: commits-help@fesod.apache.org
(fesod) 01/01: fix: DataFormatter trailing-comma scaling and literals between digits
Posted by fa...@apache.org.
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