Bug 55265 - Unable to set cell format to #'##0
Summary: Unable to set cell format to #'##0
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 3.9-FINAL
Hardware: PC All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on: 52592
  Show dependency tree
Reported: 2013-07-15 06:57 UTC by Marc
Modified: 2015-11-04 18:24 UTC (History)
1 user (show)

Excel cell format including examples (42.00 KB, image/png)
2013-07-15 06:57 UTC, Marc

Note You need to log in before you can comment on or make changes to this bug.
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.