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