poi-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From bugzi...@apache.org
Subject [Bug 60571] New: Custom number formats with custom currency symbols not applied to cells
Date Wed, 11 Jan 2017 00:05:27 GMT
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: jlwinger@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-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org


Mime
View raw message