CODEWITHSUNDEEP
javaapache-poi
Logarith
Logarith

Reputation: 710

Apache POI Currency Data Format

I try to convert numbers into european currency style with Apache POI

HSSFDataFormat cf = workbook.createDataFormat();
currencyCellStyle = workbook.createCellStyle();
currencyCellStyle.setDataFormat(cf.getFormat("#.###,#0"));

I have for example the number 2400 and 2.4

What I want is 2400,00 and 2,40 . But POI gives me 2400,0 and 2,40. When I try to change it to 

currencyCellStyle.setDataFormat(cf.getFormat("#.###,00"));

I get the result 2400,00 and 2,400. Thats also not what I want.

Is there a possibility to get both values correct?

Thx and Greetings

Upvotes: 6

Views: 19156

Answers (3)

Philipp Zitzmann
Philipp Zitzmann

Reputation: 61

Cannot write a Comment due my reputation. But if you need Integer € with dots each 3rd Number (e.g. 24.000.123 €) the Pattern is:

#,##0\ "€";\-#,##0\ "€"

So set format:

cell.getCellStyle().setDataFormat(cf.getFormat("#,##0\\ \"€\";\\-#,##0\\ \"€\""));

Hope this will help some out.

Upvotes: 2

NevinJ
NevinJ

Reputation: 61

An update to the above approach: After manually creating excel file with the required currency formatting, the format string can be obtained under the "Custom" category (in Format Cell dialog). That way we need not read the excel via poi to get this string.

Upvotes: 3

Logarith
Logarith

Reputation: 710

Finally Gagravarr gave the right tips to solve this question.

The final solution is:

HSSFDataFormat cf = workbook.createDataFormat();
currencyCellStyle = workbook.createCellStyle();
currencyCellStyle.setDataFormat(cf.getFormat("#,##0.00\\ _€"));

The solution came up after creating an excel file manually. Then read it in by Apache Poi and extracting the format string with

 cell.getCellStyle().getDataFormatString()

The result was #,##0.00\ _€

So I used this format in the upper code snippet, which gave the correct result.

Thx

Upvotes: 14

Related Questions