Mailing-List: contact dev-help@poi.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "POI Developers List" <dev@poi.apache.org>
From: bugzilla@apache.org
To: dev@poi.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 +0000
Message-ID: <bug-60571-47293@https.bz.apache.org/bugzilla/>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Auto-Submitted: auto-generated
MIME-Version: 1.0
archived-at: Wed, 11 Jan 2017 00:05:38 -0000

https://bz.apache.org/bugzilla/show_bug.cgi?id=3D60571

            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=3D34608&action=3Dedit
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.=20

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), a=
nd
replace the "$" with the currency symbols. I've replaced them both ways, as
supported by microsoft excel:=20
"JPY", "GBP", \J\P\Y, \G\B\P

I add the format to the cellStyle, and then the cell, which has the raw val=
ue
set already:=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20
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:=
=20=20=20=20
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20
creationhelper.createDataFormat().getFormat(displayMask);*

Once exported, the numbers aren't formatted as such; they use $ as the curr=
ency
symbol, which is in my locale.=20

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 nu=
mber
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.

--=20
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