Bug 60571 - Custom number formats with custom currency symbols not applied to cells
Summary: Custom number formats with custom currency symbols not applied to cells
Status: RESOLVED WORKSFORME
Alias: None
Product: POI
Classification: Unclassified
Component: SS Common (show other bugs)
Version: 3.15-FINAL
Hardware: PC Mac OS X 10.1
: P2 enhancement (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2017-01-11 00:05 UTC by Jonny
Modified: 2017-02-10 02:06 UTC (History)
0 users



Attachments
excel export using backslashes (8.78 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2017-01-11 00:05 UTC, Jonny
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Jonny 2017-01-11 00:05:27 UTC
Created attachment 34608 [details]
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.
Comment 1 Jonny 2017-01-16 18:06:29 UTC
I'm increasing the severity as the solution to this, or at least a response, is needed as soon as possible. Thanks.
Comment 2 Mark Murphy 2017-01-16 18:57:52 UTC
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.
Comment 3 Jonny 2017-01-16 19:29:13 UTC
Ok, thank you for your response!
Comment 4 Jonny 2017-01-24 23:03:43 UTC
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.
Comment 5 Greg Woolsey 2017-01-25 00:28:02 UTC
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.
Comment 6 Jonny 2017-01-25 18:25:38 UTC
(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?
Comment 7 Greg Woolsey 2017-01-25 19:23:52 UTC
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.
Comment 8 Jonny 2017-01-25 22:29:39 UTC
(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?
Comment 9 Javen O'Neal 2017-01-25 22:55:01 UTC
(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.
Comment 10 Jonny 2017-01-26 01:01:02 UTC
(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!
Comment 11 Jonny 2017-02-10 00:41:25 UTC
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!
Comment 12 Javen O'Neal 2017-02-10 02:06:56 UTC
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