pjfanning commented on issue #977:
URL: https://github.com/apache/poi/issues/977#issuecomment-6225539428
Thanks for the sample file.
The file doesn't contain a format string for index 40. Excel only writes
FORMAT records for some of the built-in formats (in this file, 5-8 and 41-44).
For the rest, including 37-40, Excel uses the version that matches the locale
of the Excel opening the file. In your file the COUNTRY record is 49 (Germany),
so your Excel shows `#.##0,00 _€;[Red]-#.##0,00 _€`, but that string isn't
stored anywhere in the file.
POI has no locale-specific built-in tables, so it uses the standard (en-US)
list from `BuiltinFormats`, where 40 is `#,##0.00_);[Red](#,##0.00)`. That
format writes negative numbers in brackets. The value itself is unchanged:
`getNumericCellValue()` returns -378.13.
The same applies to .xlsx and `XSSFSheetXMLHandler`. Excel leaves the
built-in IDs out of `<numFmts>`, and an .xlsx file has no locale information at
all, so POI has nothing to pick a locale-specific string from.
As a workaround, map the locale-dependent built-in indexes (37-40) to your
locale's format strings before formatting. Since you pass the format string to
`formatRawCellContents` yourself, that's a small check:
```java
String fmt = cell.getCellStyle().getDataFormatString();
if (cell.getCellStyle().getDataFormat() == 40) {
fmt = "#,##0.00 _€;[Red]-#,##0.00 _€";
}
String str = new DataFormatter().formatRawCellContents(
cell.getNumericCellValue(), cell.getCellStyle().getDataFormat(),
fmt);
```
In streaming mode, you can do the same in the `DataFormatter` (or a
subclass) that you pass to `XSSFSheetXMLHandler`.
--
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]
