Bug 55265

Summary: Unable to set cell format to #'##0
Product: POI Reporter: Marc <marc.wagner>
Component: XSSFAssignee: POI Developers List <dev>
Severity: normal CC: apache
Priority: P2    
Version: 3.9-FINAL   
Target Milestone: ---   
Hardware: PC   
OS: All   
Bug Depends on: 52592    
Bug Blocks:    
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:


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());


CELL col=0 VALUE=NUMERIC value=1.0
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
Comment 6 Nick Burch 2015-11-04 18:24:08 UTC
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.