|Summary:||Unable to set cell format to #'##0|
|Component:||XSSF||Assignee:||POI Developers List <dev>|
|Bug Depends on:||52592|
|Attachments:||Excel cell format including examples|
Description Marc 2013-07-15 06:57:04 UTC
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?
Comment 1 Nick Burch 2013-07-15 08:23:45 UTC
If you set that format in Excel, then read the format back in POI, what does POI think was actually written to the file?
Comment 2 Marc 2013-07-15 09:26:22 UTC
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
Comment 3 Nick Burch 2013-07-17 10:49:58 UTC
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?
Comment 4 Marc 2013-07-17 11:20:47 UTC
* 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
Comment 5 Evgeniy Buyanov 2013-07-26 08:05:34 UTC
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