https://bz.apache.org/bugzilla/show_bug.cgi?id=60571
Bug ID: 60571
Summary: Custom number formats with custom currency symbols not
applied to cells
Product: POI
Version: 3.15-FINAL
Hardware: PC
OS: Mac OS X 10.1
Status: NEW
Severity: normal
Priority: P2
Component: SS Common
Assignee: dev@poi.apache.org
Reporter: jlwin...@us.ibm.com
Target Milestone: ---
Created attachment 34608
--> https://bz.apache.org/bugzilla/attachment.cgi?id=34608&action=edit
excel export using backslashes
I'm using Apache POI in java to export an excel file with a custom currency
format. I'm using Microsoft Excel for Mac 2011, and open office on the side for
comparison.
When exporting our currency values, they can contain alphabetic currency
symbols: GBP for UK Pounds, JPY for yen as an example. But these currency
symbols can be customized by our users before exporting.
In our excel export code, I edit the built-in formats in
org.apache.poi.ss.usermodel.BuiltinFormats (6 for yen, and 8 for pounds), and
replace the "$" with the currency symbols. I've replaced them both ways, as
supported by microsoft excel:
"JPY", "GBP", \J\P\Y, \G\B\P
I add the format to the cellStyle, and then the cell, which has the raw value
set already:
cellStyle.setDataFormat(dataFormat.getFormat("\"JPY\"#,##0_);[Red](\"JPY\"#,##0)"));
or
cellStyle.setDataFormat(dataFormat.getFormat("\J\P\Y#,##0_);[Red](\J\P\Y#,##0)"));
and
cellStyle.setDataFormat(dataFormat.getFormat("\"GBP\"#,##0.00_);[Red](\"GBP\"#,##0.00)"));
or
cellStyle.setDataFormat(dataFormat.getFormat("\G\B\P#,##0.00_);[Red](\G\B\P#,##0.00)"));
*note that I have also used CreationHelper to get the format, same results:
creationhelper.createDataFormat().getFormat(displayMask);*
Once exported, the numbers aren't formatted as such; they use $ as the currency
symbol, which is in my locale.
I get the following results when exporting with the backslash. Also, the
positive format for JPY doesn't have the "J" on it, yet the negative format is
fine:
$55,555,555.56 United Kingdom Pounds
PY 54,684,654,685 Japan Yen
(JPY 55,555,555) Japan Yen
If I format the GBP cell value, I see the GBP custom format as such:
\G\BP #,##0.00_);[Red](\G\BP #,##0.00)
and if I apply it to the cell, I get the number format I wanted upon first
opening the excel file:
GBP 55,555,555.56 United Kingdom Pounds
When using the quotation around the currency symbol abbreviation, I get a
"content is unreadable" error, but it can be repaired. The results are:
$55,555,555.56 United Kingdom Pounds
54684654685 Japan Yen
-55555555 Japan Yen
If I look at the custom format list, the JPY number format isn't there. For
GBP, it is:
"GBP"#,##0.00_);[Red]("GBP"#,##0.00)
and when I apply it to the cell, I get the desired result:
GBP55,555,555.56 United Kingdom Pounds
Any help on this would be greatly appreciated. I'm fine with setting the number
formats using backslashes. The custom formats seem to be generated, except the
"J" in JPY is cut off. But the number format isn't applied to the cell, in
which I hopefully did correctly using Apache POI. Thank you.
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscr...@poi.apache.org
For additional commands, e-mail: dev-h...@poi.apache.org
