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