Bug 62865 - Dot in user-defined number format is removed
Summary: Dot in user-defined number format is removed
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: 2020-03-07 16:23 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 + '"'))
Comment 1 Dominik Stadler 2020-03-07 16:23:12 UTC
This works for me in the current version of POI (4.1.3-SNAPSHOT), verified with the following snippet:

        CellFormat cf = CellFormat.getInstance("\"ca. \"0");
        assertEquals("ca. 5", cf.apply((double) 5).text);

Please reopen this issue and attach a self-sufficient test-case which shows the problem if this still does not work for you.

Please note that entering a format in a German/European Excel might actually store the format differently in the Excel file itself as Excel "converts" this to US-format internally for multi-national support,