You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@poi.apache.org by bu...@apache.org on 2017/01/11 00:05:27 UTC
[Bug 60571] New: Custom number formats with custom currency symbols
not applied to cells
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
[Bug 60571] Custom number formats with custom currency symbols not
applied to cells
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=60571
--- Comment #4 from Jonny <jl...@us.ibm.com> ---
Hello, so I have been working on this issue. I believe the change may be deeper
than the package I had mentioned, and I don't see the source code for them. But
more importantly, I don't quite know where the implementation for the solution
would take place.
In the excel file, I did comparisons and looked at the cell properties, by
using VBA editor. The cell properties "NumberFormat" and "NumberFormatLocal"
should be set with the custom number format somehow. They aren't set currently
with the custom format, except for the Japanese Yen, as I had mentioned; the
others are set with a standard currency format. I also manaully added the
format to a cell value, and did see this cell property update to the correct
custom number format. So I believe if we have some way to set this in Apache
POI, that will resolve the issue.
The following are some things I've looked into.
I looked in CellUtil.setCellStyleProperty(), and those are limited. Updating
this to have a setNumberFormat & setNumberFormatLocal might help. However, I've
unzipped an xlsx file, and looked at the containing xml files, and it seems
that the only place which has the custom number formats is in styles.xml. And
as I've mentioned before, the JPY number format works mostly. So I don't know
how this update would help.
Another location I looked in is in CTXF, which is used when setting the data
format for cell styles. When debugging, I looked at the xml fragment generated
(this is for Japanese Yen, but it looks the same for all the cells):
<xml-fragment numFmtId="166" fontId="0" fillId="0" borderId="0" xfId="0"
applyNumberFormat="true"
xmlns:main="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<main:alignment wrapText="true"/>
</xml-fragment>
From what I've researched, this should include the "formatCode" attribute,
which the value would be the number format.
I received that from this stackoverflow, but the solution doesn't use Apache
POI, nor do I think it still uses the formatCode attribute, which leads me to
believe this implementation may not be fruitful. But I'd appreciate an opinion
from someone else.
http://stackoverflow.com/questions/23501843/applying-number-formatting-in-openxml
This package may be with a different open source product, is that right?
org.openxmlformats.schemas.spreadsheetml.x2006.main
If so, maybe the issue resides in there and I should open a different ticket?
Thank you all for any tips or suggestions.
--
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
[Bug 60571] Custom number formats with custom currency symbols not
applied to cells
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=60571
--- Comment #3 from Jonny <jl...@us.ibm.com> ---
Ok, thank you for your response!
--
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
[Bug 60571] Custom number formats with custom currency symbols not
applied to cells
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=60571
--- Comment #7 from Greg Woolsey <gr...@gmail.com> ---
I tried to figure that out about 6 years ago, and got nowhere. I didn't prove
it couldn't be done, but I did convince myself that Excel was too tightly
integrated with Windows for that particular purpose to make it easy. Even if
you could embed something that Excel actually paid attention to, I'm not sure
it would be applied consistently either, and may cause a confusing user
experience, especially for users with different regional settings.
You might be better off formatting things explicitly and converting cells to
text, although that won't work if they are needed for formulas elsewhere.
--
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
[Bug 60571] Custom number formats with custom currency symbols not
applied to cells
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=60571
Jonny <jl...@us.ibm.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
Severity|normal |major
--- Comment #1 from Jonny <jl...@us.ibm.com> ---
I'm increasing the severity as the solution to this, or at least a response, is
needed as soon as possible. Thanks.
--
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
[Bug 60571] Custom number formats with custom currency symbols not
applied to cells
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=60571
--- Comment #8 from Jonny <jl...@us.ibm.com> ---
(In reply to Greg Woolsey from comment #7)
> I tried to figure that out about 6 years ago, and got nowhere. I didn't
> prove it couldn't be done, but I did convince myself that Excel was too
> tightly integrated with Windows for that particular purpose to make it easy.
> Even if you could embed something that Excel actually paid attention to, I'm
> not sure it would be applied consistently either, and may cause a confusing
> user experience, especially for users with different regional settings.
>
> You might be better off formatting things explicitly and converting cells to
> text, although that won't work if they are needed for formulas elsewhere.
Ah yes, there could be aggregates on these values. Thank you for your help! I
remember that for html files in excel, you can add style attribute with
mso-number-format="...". From looking at the xml, I'm not quite certain yet,
but I take it that it's not possible to use that attribute in the sheet.xml?
--
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
[Bug 60571] Custom number formats with custom currency symbols not
applied to cells
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=60571
--- Comment #9 from Javen O'Neal <on...@apache.org> ---
(In reply to Jonny from comment #8)
> I remember that for html files in excel, you can
> add style attribute with mso-number-format="..."
> I take it that it's not possible to use that attribute in the sheet.xml?
Check in the OOXML schema. If you have built POI, it's in
ooxml-lib/OpenOfficeXML-XMLSchema.zip, otherwise download it from ECMA
International Office Open XML 1st edition part 4.
I grepped through the schemas for mso and number-format and didn't see
anything. Perhaps it's called something else in the schemas and sheet.xml? It's
also possible that it was added after the 1st edition. If you find it, it's
possible to get POI to write that attribute.
--
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
[Bug 60571] Custom number formats with custom currency symbols not
applied to cells
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=60571
Javen O'Neal <on...@apache.org> changed:
What |Removed |Added
----------------------------------------------------------------------------
Severity|major |enhancement
--
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
[Bug 60571] Custom number formats with custom currency symbols not
applied to cells
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=60571
--- Comment #2 from Mark Murphy <jm...@apache.org> ---
You understand that the developers here are all volunteers, and mostly work on
the things that are important to them in their free time. If you need something
as soon as possible, it would be best to download the source and take a crack
at it yourself. Then donate your solution back to the project. Please include
examples and unit tests in your submission.
--
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
[Bug 60571] Custom number formats with custom currency symbols not
applied to cells
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=60571
--- Comment #6 from Jonny <jl...@us.ibm.com> ---
(In reply to Greg Woolsey from comment #5)
> One major complicating factor with this particular issue, that I've dealt
> with in the past, is that Excel uses the Windows Region settings for default
> number formatting, and doesn't store them (as you see) in the document
> itself. That's how two users can open the same file and see different
> grouping separators and decimal symbols, for example.
>
> These are very deeply buried in the Windows Control Panel settings, but are
> the only way to change the Excel defaults. I don't even know how different
> versions of Excel will handle having them explicitly set in the document
> XML, i.e. Excel for Mac, let alone LibreOffice.
Thank you for your response! So please correct me if I'm wrong, but are you
saying there's nothing we can write to any of the xml files in Excel which will
tell Microsoft Excel to custom format a number value?
--
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
[Bug 60571] Custom number formats with custom currency symbols not
applied to cells
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=60571
--- Comment #12 from Javen O'Neal <on...@apache.org> ---
You might also try looking at CellUtil, which will look through existing
CellStyles and create a new CellStyle only if one that matches your desired
style exists.
This is basically the same as what your HashMap is doing, but with code
maintained by POI.
If this class doesn't support data formats, send a pull request or patch.
https://poi.apache.org/apidocs/org/apache/poi/ss/util/CellUtil.html
--
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
[Bug 60571] Custom number formats with custom currency symbols not
applied to cells
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=60571
--- Comment #10 from Jonny <jl...@us.ibm.com> ---
(In reply to Javen O'Neal from comment #9)
> (In reply to Jonny from comment #8)
> > I remember that for html files in excel, you can
> > add style attribute with mso-number-format="..."
> > I take it that it's not possible to use that attribute in the sheet.xml?
>
> Check in the OOXML schema. If you have built POI, it's in
> ooxml-lib/OpenOfficeXML-XMLSchema.zip, otherwise download it from ECMA
> International Office Open XML 1st edition part 4.
>
> I grepped through the schemas for mso and number-format and didn't see
> anything. Perhaps it's called something else in the schemas and sheet.xml?
> It's also possible that it was added after the 1st edition. If you find it,
> it's possible to get POI to write that attribute.
Thank you for your response! I've looked through them, and no mention of it. I
would imagine if it went by anything, it would include number or format, but
none of those searches were fruitful. So this doesn't seem likely through xml
files, which is fine. This doesn't need to be resolved right away, but at least
now I have a legitimate reason as to why it can't be implemented right now.
Thank you all for your help!
--
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
[Bug 60571] Custom number formats with custom currency symbols not
applied to cells
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=60571
Jonny <jl...@us.ibm.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
Resolution|--- |WORKSFORME
Status|NEW |RESOLVED
--- Comment #11 from Jonny <jl...@us.ibm.com> ---
Hi All,
So this actually does work. The issue was that I was reusing CellStyles for
each currency field in the excel export. Thus, the last format would always
override the other ones.
The solution was to use a hashmap, with the display mask as the key, to store
and retrieve all the CellStyles. And if it isn't in the hashmap, make a new
CellStyle.
Sorry for the confusion, and thank you all very much for your help!
--
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
[Bug 60571] Custom number formats with custom currency symbols not
applied to cells
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=60571
--- Comment #5 from Greg Woolsey <gr...@gmail.com> ---
One major complicating factor with this particular issue, that I've dealt with
in the past, is that Excel uses the Windows Region settings for default number
formatting, and doesn't store them (as you see) in the document itself. That's
how two users can open the same file and see different grouping separators and
decimal symbols, for example.
These are very deeply buried in the Windows Control Panel settings, but are the
only way to change the Excel defaults. I don't even know how different
versions of Excel will handle having them explicitly set in the document XML,
i.e. Excel for Mac, let alone LibreOffice.
--
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