You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@fesod.apache.org by "pjfanning (via GitHub)" <gi...@apache.org> on 2026/10/16 14:31:43 UTC

[PR] fix: DataFormatter trailing-comma scaling and literals between digits [fesod]

pjfanning opened a new pull request, #1232:
URL: https://github.com/apache/fesod/pull/1232

   Ports apache/poi#1396 to Fesod's `DataFormatter`. Fixes #1120.
   
   > [!WARNING]
   > **This PR and #1231 (the "check DataFormatter against Excel" test) will conflict. Whichever is merged second needs a rebase.**
   > #1231's test asserts that each known failure *still* differs from Excel. This PR fixes 167 of those cases, so after both are in, #1231's `UNSUPPORTED_FORMATS` / `KNOWN_FAILURES` lists need those entries removed:
   > - the trailing-comma formats `0,`, `#,,`, `0.#,` and `#,.#,`
   > - the fraction formats with escaped text, such as `|#\:#=/=#|` and `|0\:0=/=?|`
   >
   > I checked by running #1231's test on this branch: the only failures were "now matches Excel" cases, and there were no regressions. Matches go from 151 to 318 of the 569 Excel cases.
   
   > [!NOTE]
   > **Overlap with #1121.** #1121 (@nkuprins) fixes the same issue (#1120) in the same code, so only one of the two should be merged. All 14 test cases from #1121's `scalesByTrailingCommas` also pass on this branch. The two take different approaches:
   > - **#1121** keeps `DecimalFormat` and adds an extra power of ten for digits after an escaped or quoted dot.
   > - **This PR** follows POI. Any format with literal text between digit placeholders goes to POI's `CellFormat`, which also handles cases the extra-shift approach can't, e.g. `0".0"0,` → `123.05`, `0\.00,;-0\.0,` (a different literal per section), and fractions with literal text.
   >
   > Keeping the same logic as POI also makes a later switch to POI's `DataFormatter` easier.
   
   ## Changes
   
   **1. Trailing commas no longer over-scale.**
   The issue #2319 workaround also divided by ten for each character between the `.` and the `,`. It used `indexOf`, so even formats without a dot were affected. That has been removed.
   
   | Format | Value | Before | After (= Excel) |
   |---|---|---|---|
   | `0,` | 1234567 | `124` | `1235` |
   | `#,,` | 1234567 | `0` | `1` |
   | `0.#,` | 1234567 | `123.5` | `1234.6` |
   
   The division is now exact (`BigDecimal.divide(divider)`, no rounding), so the decimal places survive until formatting. `#,##0.0,` gives `1,234.6`.
   
   **2. `!` is an escape character, like `\`.**
   `0!.0,` → `0.9` and `0!!` → `5!`. A `!` inside quotes, inside brackets, or after `_` or `*` is left alone.
   
   **3. Number formats with literal text between digit placeholders go to `CellFormat`.**
   `DecimalFormat` can't express these. For #2319's format `0\.0,`, 9234 → `0.9` and 123456 → `12.3`. The format is cached as a `CellFormatWrapper`.
   
   ## Tests
   - `DataFormatterTest`: new `scalesByTrailingCommas`, `keepsLiteralTextBetweenDigits` and `treatsExclamationMarkAsEscape` tables. These are POI's #1396 cases, plus the `0,` / `#,,` / `0.#,` regressions and #2319's values.
   - `TrailingCommaFormatReadTest`: reads the #2319 workbooks (`temp/issue2319/test1.xlsx` and `test2.xlsx`, still in the test resources) end to end and asserts the formatted values. The original `Issue2319Test` was removed in #770, and it only printed results without asserting anything.
   - `./mvnw -pl fesod-sheet test -Dmaven.test.skip=false`: 1112 tests, 0 failures. `spotless:check` is clean.
   
   🤖 Generated with [Claude Code](https://claude.com/claude-code)
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@fesod.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@fesod.apache.org
For additional commands, e-mail: commits-help@fesod.apache.org


Re: [PR] fix: DataFormatter trailing-comma scaling and literals between digits [fesod]

Posted by "pjfanning (via GitHub)" <gi...@apache.org>.
pjfanning commented on code in PR #1232:
URL: https://github.com/apache/fesod/pull/1232#discussion_r4302018818


##########
fesod-sheet/src/main/java/org/apache/fesod/sheet/metadata/format/DataFormatter.java:
##########
@@ -327,12 +325,98 @@ private Format createFormat(Short dataFormat, String dataFormatString) {
             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);
         }

Review Comment:
   Thanks, good catch. Fixed in a1b6175f: the check (now `hasLiteralDecimalFormatCannotShow()`) also routes a section to `CellFormat` when quoted or escaped text follows a trailing scaling comma, i.e. a comma with no digit placeholder after it. Grouping commas such as `#,##0" units"` still use `DecimalFormat`. Your three examples are covered in `keepsLiteralTextAfterTrailingCommas`, along with `#,##0.0,\K` and a negative `#,##0,"K"`.



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@fesod.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@fesod.apache.org
For additional commands, e-mail: commits-help@fesod.apache.org


Re: [PR] fix: DataFormatter trailing-comma scaling and literals between digits [fesod]

Posted by "bengbengbalabalabeng (via GitHub)" <gi...@apache.org>.
bengbengbalabalabeng commented on code in PR #1232:
URL: https://github.com/apache/fesod/pull/1232#discussion_r4301516612


##########
fesod-sheet/src/main/java/org/apache/fesod/sheet/metadata/format/DataFormatter.java:
##########
@@ -327,12 +325,98 @@ private Format createFormat(Short dataFormat, String dataFormatString) {
             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);
         }

Review Comment:
   When a format pattern contains both **trailing commas (thousand scaling)** and **trailing literal text**, `hasLiteralBetweenDigits()` returns `false`, causing the logic to fall into `createNumberFormat()`. After `cleanFormatForNumber()` strips the quotation marks, two types of formatting errors occur:
   
   1. **Malformed Pattern Fallback**: Unquoted text characters cause `new DecimalFormat()` to throw an `IllegalArgumentException`, which falls back to `getDefaultFormat()` and degrades to outputting the raw value.
   2. **Incorrect Scaling Factor**: A literal comma inside quotes is mistakenly treated as a trailing scaling comma, causing an extra division by 1,000.
   
   | source | pattern | current | expected |
   | :--- | :--- | :--- | :--- |
   | `1234567` | `0.0,"万"` | `1234567` (fallback) | `1234.6万` |
   | `1234567` | `#,##0,,"M"` | `1234567` (fallback) | `1M` |
   | `1234567` | `#,##0.0,","` | `1.2` (divided by extra 1000) | `1,234.6,` |
   
   Could we add a check for trailing literals (e.g., `hasLiteralAfterTrailingCommas()`) alongside the `hasLiteralBetweenDigits()` branch to route these patterns to `CellFormatWrapper` as well? (Testing confirms that `CellFormat` handles all of them correctly.)



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@fesod.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@fesod.apache.org
For additional commands, e-mail: commits-help@fesod.apache.org


Re: [PR] fix: DataFormatter trailing-comma scaling and literals between digits [fesod]

Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on PR #1232:
URL: https://github.com/apache/fesod/pull/1232#issuecomment-6217804516

   ## Test Results
   1 172 tests   1 172 ✅  2m 6s ⏱️
     129 suites      0 💤
     129 files        0 ❌
   
   Results for commit 96507883.
   
   [test-results]:data:application/gzip;base64,H4sIAP810moC/2XMSw7CIBSF4a00jB0U6qXgZgzcQnJjWwyFkXHvIr5Qh/93knNhnma3sUPHhd51bMuUmpxyNInCWkEWKGOqMx/FK49bRvyzE52L9W/whuYvcDGG+JSY1+b1Xj+nD/p81m4ua7ePGJaFUgmmJfSjUoP1EgEVcnDIUUs3KAuTFnuQYCxqdr0B8wkoQg0BAAA=
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@fesod.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@fesod.apache.org
For additional commands, e-mail: commits-help@fesod.apache.org


Re: [PR] fix: DataFormatter trailing-comma scaling and literals between digits [fesod]

Posted by "nkuprins (via GitHub)" <gi...@apache.org>.
nkuprins commented on PR #1232:
URL: https://github.com/apache/fesod/pull/1232#issuecomment-6218188088

   Great PR! Thank you very much!


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@fesod.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@fesod.apache.org
For additional commands, e-mail: commits-help@fesod.apache.org


Re: [PR] fix: DataFormatter trailing-comma scaling and literals between digits [fesod]

Posted by "bengbengbalabalabeng (via GitHub)" <gi...@apache.org>.
bengbengbalabalabeng merged PR #1232:
URL: https://github.com/apache/fesod/pull/1232


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@fesod.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@fesod.apache.org
For additional commands, e-mail: commits-help@fesod.apache.org