pjfanning opened a new issue, #1397:
URL: https://github.com/apache/poi/issues/1397
`DataFormatter` / `DateUtil.isADateFormat` mishandle several date and number
formats that are common in Chinese-locale workbooks. Apache Fesod (a POI-based
fork of EasyExcel) already works around most of these in its copy of
`DataFormatter`.
Reproduced on current trunk (after #1396) with `new
DataFormatter(Locale.CHINA).formatRawCellContents(45000.6, 200, fmt)` (45000.6
= 2023-03-15 14:24). `Locale.US` gives the same results.
| # | Format | Current result | Expected (approx.) |
|---|---|---|---|
| 1 | `yyyy"年"m"月"d"日"` | `2023"年"3"月"15"日"` (quote marks kept) |
`2023年3月15日` |
| 1 | `d"-"mmm` | `15"-"Mar` | `15-Mar` |
| 2 | `h"时"mm"分"ss"秒"`, `h时mm分ss秒` | `45000.6` (`isADateFormat` is false) |
`14时24分00秒` |
| 2 | `yyyy"年"m"月"d"日" h"时"mm"分"` | `45000.6` (`isADateFormat` is false) |
`2023年3月15日 14时24分` |
| 3 | `上午/下午h:mm`, `yyyy/m/d 上午/下午h:mm`, `上午/下午h"时"mm"分"` | `45000.6`
(`isADateFormat` is false) | `下午2:24` etc. |
| 4 | `[DBNum1]yyyy"年"m"月"d"日"` (with or without `[$-804]`) |
`IllegalArgumentException: Cannot format given Object as a Number` | a
formatted date |
| 5 | `[DBNum2]0.00` | `[DBNum2]45000.60` (tag kept in output) | `45000.60` |
### Notes
1. `createDateFormat` replaces `""` with `'` but never converts `"literal"`
into Java quoting, so the quote marks reach `SimpleDateFormat` as literals.
Simply removing the quotes isn't enough, because quoted text with letters (e.g.
`d" de "mmmm`) would turn into pattern letters. The fix is to convert `"..."`
into `'...'`.
2. `date_ptrn3b` in `DateUtil` already allows 年月日 but not 时分秒 (or Japanese
時).
3. `amPmPattern` only matches the Latin `AM/PM` / `A/P` markers. `上午/下午` is
Excel's Chinese AM/PM marker; it should map to Java's `a`. It typically comes
*before* the time, so `isADateFormat` needs to allow it there too.
4. `[DBNum1-3]` (Chinese/Japanese numeral styles) is not stripped. In the
date case, `createDateFormat` fails, the fallback is a number format, and
`performDateFormatting` then passes it a `Date`, which causes the exception.
Two things to fix:
- strip `[DBNumN]` the same way colour codes are stripped. Arabic digits
are an acceptable approximation of the numerals Excel shows.
- make sure the date-format fallback never hands a `Date` to a number
format.
5. Same `[DBNumN]` stripping as in 4.
### Reference
Fesod's handling (Apache-licensed):
-
`fesod-sheet/src/main/java/org/apache/fesod/sheet/metadata/format/DataFormatter.java`:
`amPmPattern` includes `上午/下午`, `colorPattern` includes `[DBNum1-3]`, and
`createDateFormat` strips `"`
-
`fesod-sheet/src/main/java/org/apache/fesod/sheet/util/ExcelDateFormatDetector.java`:
年月日时分秒 counted as date markers
Fesod's 年月日时分秒 check is looser than it should be: `0.00"m/秒"` would count as
a date. Extending `date_ptrn3b` is a tighter fix.
--
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: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
