Apache POI, force currency format with two decimal points

Question

I try to force Apache POI to produce two digits currency format.

I managed to produce style formatting:

cellStyle.setDataFormat(wb.createDataFormat().getFormat("$#,##0.00"))

However, once executed in Excel, I receive the following error which I would like to get rid off:

error: some number formats may have been lost,

numbers appear to have from 2 decimal points up to 5.

I tried to find legend of apache POI formatting, no success though.

I digged through one of my projects where I used a similar format. This is what it looks like: _($* #,##0.00_);_($* (#,##0.00);_($* \"-\"??_);_(@_) — XtremeBaumer
@XtremeBaumer is it for two decimal points? Seems fairly long. Thing is I need to have flexibility of currencies resolved dynamically, however I think String.format(...) would do the job. — krzakov
It is for 2 decimal points. Its the same as in the second answer on the link shared by m4gic — XtremeBaumer
That is a Currency format converted to a custom one. Should behave exactly the same — XtremeBaumer

XtremeBaumer XtremeBaumer · Accepted Answer · 2018-08-29T14:21:40

Converting comments to an answer. Keeping comments as they might be easier to follow through

First of all, following format should be used for currencies

_($* #,##0.00_);_($* (#,##0.00);_($* \"-\"??_);_(@_)

(this format is takes straight from excel so its working 100%).

Now that we have the format, lets have a look at the currency issue that has been encountered. The following code shows all currencies with their symbol/sign:

for (Currency c : Currency.getAvailableCurrencies()) {
    System.out.println(c.getCurrencyCode() + " : " + c.getSymbol());
}

From this we can see, that only USD has a "real" symbol, while all other currencies in that class only have their currency code as symbol. This seems to be the actual issue here. Using this code:

String format = "_(<ccy>* #,##0.00_);_(<ccy>* (#,##0.00);_(<ccy>* \\\"-\\\"??_);_(@_)";
style.setDataFormat(wb.createDataFormat().getFormat(format.replace("<ccy>", "€")));

we can see, that it does work with the actual symbols, but fails if the symbols are replaced by their currency codes. This caused OP to check excel and he found that excel itself offers a currency format for BGN, but after all, apache-poi is not excel.

This provided format will work with most currency as long as the proper symbol is provided. I checked a few currency symbold from this site and found that not all currency symbols are supported. Дин. works while CHF and RD$ both fail. This might be changed in the future.

Note that I tested it with apache-poi 3.15. This might already work better in 3.17

Apache POI, force currency format with two decimal points

1 Answers