Created attachment 30593 [details] Excel cell format including examples To set a cell to a numeric format showing a single quote every 3 digits (as usual in Europe), the cell format should be set to #'##0: cellStyle.setDataFormat(format.getFormat("#'##0")); But opening the Excel shows the format like this: #\'##0 This results in displaying a single quote even if there's only a value of zero. Please remove the escaping backslash. Or is there another option to set this format correctly?
If you set that format in Excel, then read the format back in POI, what does POI think was actually written to the file?
System.out.println("CELL col=" + cell.getColumnIndex() + " VALUE=" + value); System.out.println("CELL col=" + cell.getColumnIndex() + " FORMAT=" + cell.getCellStyle().getDataFormat()); gives CELL col=0 VALUE=NUMERIC value=1.0 CELL col=0 FORMAT=3
Two more things to check: * If you set the format code with id 3, does it show right? * If you open your excel-generated file in a copy of excel in a US locale, do you still see the same formatting, or does it magically change to a US style one?
* If I set the cell format to 3, everything is OK. Numbers are shown as 0 or 1'000 or 1'000'000. Currently I implemented this: cell.setDataFormat((short) 3); And this works as it should. * I use a native US English Windows environment (OS Windows 8 and Office 2013). Changing the Windows format (in Control Panel > Region) from "German (Switzerland)" to "United States" gives following results: - using cell.setDataFormat((short) 3): the Excel sheet format "magically" changes from 1'000 to 1,000 (US locale). All numbers are correct (0, 1'000, 1'000'000). - using cellStyle.setDataFormat(format.getFormat("#'##0")): nothing changes. The numbers are always in a wrong format like '0, 1'000 or 1000'000
related bug https://issues.apache.org/bugzilla/show_bug.cgi?id=52592 Error in the current and previous version of DataFormatter.java in private String cleanFormatForNumber(String formatStr) see http://docs.oracle.com/javase/1.4.2/docs/api/java/text/MessageFormat.html single quotes need to be doubled
Turned out to not be an escaping issue after all, but instead a need to use a custom Decimal Format Symbols for this case. Fixed in r1712605.