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
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2018-10-29 18:42 UTC by Sven
Modified: 2018-10-29 18:42 UTC (History)
0 users


Attachments
File with user-defined formats (4.37 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2018-10-29 18:42 UTC, Sven 		Details
View All Add an attachment (proposed patch, testcase, etc.)

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 6.1.2.1 (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 + '"'))