1

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.

P3trur0
  • 3,155
  • 1
  • 13
  • 27
krzakov
  • 3,871
  • 11
  • 37
  • 52
  • 1
    Probably [this](https://stackoverflow.com/questions/319438/basic-excel-currency-format-with-apache-poi) helps. – m4gic Aug 29 '18 at 09:31
  • 1
    I digged through one of my projects where I used a similar format. This is what it looks like: `_($* #,##0.00_);_($* (#,##0.00);_($* \"-\"??_);_(@_)` – XtremeBaumer Aug 29 '18 at 09:31
  • @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 Aug 29 '18 at 09:36
  • It is for 2 decimal points. Its the same as in the second answer on the link shared by m4gic – XtremeBaumer Aug 29 '18 at 09:37
  • @XtremeBaumer excel perceives this as an `Custom` format, I need specifically `Currency` format. – krzakov Aug 29 '18 at 09:39
  • 1
    That is a `Currency` format converted to a custom one. Should behave exactly the same – XtremeBaumer Aug 29 '18 at 09:41
  • @XtremeBaumer I see. Installing bootcamp to verify that, sadly my `windows` customer is not available to verify that. – krzakov Aug 29 '18 at 09:42
  • @XtremeBaumer sadly the same problem, 'File error, Some number formats may have been lost' `cellStyle.setDataFormat(wb.createDataFormat().getFormat(String.format("_(%1$s* #,##0.00_);_(%1$s* (#,##0.00);_(%1$s* \"-\"??_);_(@_)", currency.getSymbol() )` – krzakov Aug 29 '18 at 12:31
  • Is that the very only format you use in your file? What happens with a static format? Is it a new file or a tamplate that you edit? – XtremeBaumer Aug 29 '18 at 12:44
  • Also what happens if you create an nearly empty file with that format? (1 cell with that format and some test value) – XtremeBaumer Aug 29 '18 at 12:50
  • @XtremeBaumer I think I'm one step closer, im using `Currency#getSymbol()` from `java.util.Currency` and replace the `$` sign with the value of `getSymbol()`. Since i need support for multiple currencies. It breaks as usual (testing on bulgarian currency), however If i change to dolar sign `$` it works as expected, how can i introduce multi currency here? – krzakov Aug 29 '18 at 13:01
  • Your problem is `currency.getSymbol()`. This does **not** always return the actual symbol. I tested `USD, EUR, BGN, CHF` and the symbols are `$, EUR, BGN, CHF`. This means that you need a nother way to determine the symbol. Actually, "USD" is the only currency in `java.util.Currency` with a proper symbol. In my test code I ran this `String format = "_(* #,##0.00_);_(* (#,##0.00);_(* \\\"-\\\"??_);_(@_)"; style.setDataFormat(wb.createDataFormat().getFormat(format.replace("", "€")));` and it now displays with `€` sign – XtremeBaumer Aug 29 '18 at 13:15
  • @XtremeBaumer Which doesn't mean it is wrong. If you use excel and choose currency formatting and set it Bulgarian `BGN` you would have for example cell with value `BGN 123.52` which is perfectly correct in terms of excel currency cell formatting. BGN passed from apache poi would generate error though and that's my problem. – krzakov Aug 29 '18 at 13:54
  • Bulgarian currency sign is `лв` and not `BGN` ([source](https://www.xe.com/symbols.php)). That seems to be the reason why apache poi struggels with that. If I pass `лв` as currency sign in my code, then it works fine, but if I use `BGN`, then it fails. This all makes sense because `BGN` is not the currency sign but the currency code – XtremeBaumer Aug 29 '18 at 14:00
  • I see where you are coming from (that excel accepts `BGN`), but well apache-poi is not excel after all. Unless you are able to get the actual currency sign you wont be able to solve this properly with apache poi – XtremeBaumer Aug 29 '18 at 14:01
  • @XtremeBaumer fair enough, please formulate the answer from our comments, which I would accept if you are willing to. Worth to leave it here for people which might encounter this problem in the future. – krzakov Aug 29 '18 at 14:06
  • My 2 cents for a better solution, would be to split it into 2 columns. The first column only holds the value (which you can format properly) and the second column holds the currency code (as simple text column). This way you dodge any currencies with symbols that are their currency code as well – XtremeBaumer Aug 29 '18 at 14:46

1 Answers1

2

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

XtremeBaumer
  • 6,275
  • 3
  • 19
  • 65