| Summary: | Unable to set cell format to #'##0 | ||
|---|---|---|---|
| Product: | POI | Reporter: | Marc <marc.wagner> |
| Component: | XSSF | Assignee: | POI Developers List <dev> |
| Status: | RESOLVED FIXED | ||
| 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 | ||
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 |
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?