Bug 62865 - Dot in user-defined number format is removed
Summary: Dot in user-defined number format is removed
Status: NEW
Alias: None
Product: POI
Classification: Unclassified
Component: SS Common (show other bugs)
Version: 4.0.0-FINAL
Hardware: PC Linux
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2018-10-29 18:42 UTC by Sven
Modified: 2018-10-29 18:42 UTC (History)
0 users

File with user-defined formats (4.37 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2018-10-29 18:42 UTC, Sven

Note You need to log in before you can comment on or make changes to this bug.
Description Sven 2018-10-29 18:42:00 UTC
Created attachment 36222 [details]
File with user-defined formats

When I format a numeric cell in Excel 365 or in LibreOffice (the behavior is in both the same) with user-defined format code
"ca. "0
(the whole line is the format code, not just that between the quotes), then Excel and LibreOffice show for example "ca. 5" as a result in that cell.

POI's behavior is that "new DataFormatter(Locale.US).formatCellValue(cell, formulaEvaluator)" returns "ca5.0".

It seems to me that java.text.DecimalFormat.applyPattern(String, boolean) removes that dot, because it thinks to be in phase 1 (digits-phase), instead of being in phase 0 (prefix-phase).

I think that org.apache.poi.ss.usermodel.DataFormatter.createNumberFormat(String, double) should not remove the quote-signs from formatStr, so that the call of "new InternalDecimalFormatWithScale(format, symbols)" still contains information about the prefix-phase.

Attached is a file with three cells:
- one user-formatted with no dot ("ca "0 -- POI formats that ok as "ca 5")
- one user-formatted with one dot ("ca. "0 -- POI formats that nok as "ca5.0")
- one user-formatted with two dots ("ca.. "0 -- POI formats that nok as "5" -- and java.text.DecimalFormat.applyPattern(String, boolean) throws an IllegalArgumentException("Multiple decimal separators in pattern \"" + pattern + '"'))