Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

like image 960
Logarith Avatar asked Dec 12 '12 11:12

Logarith


People also ask

How do I change Cell format in Apache POI?

To create date cell which display current date to the cell, we can use Apache POI's style feature to format the cell according to the date. The setDateformat() method is used to set date format.

What is the use of DataFormatter in selenium?

DataFormatter contains methods for formatting the value stored in a Cell. This can be useful for reports and GUI presentations when you need to display data exactly as it appears in Excel. Supported formats include currency, SSN, percentages, decimals, dates, phone numbers, zip codes, etc.

How use DataFormatter Apache POI?

DataFormatter formatter = new DataFormatter(); //creating formatter using the default locale Cell cell = sheet. getRow(i). getCell(0); String j_username = formatter. formatCellValue(cell); //Returns the formatted value of a cell as a String regardless of the cell type.


2 Answers

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

like image 107
Logarith Avatar answered Sep 21 '22 23:09

Logarith


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.

like image 27
NevinJ Avatar answered Sep 21 '22 23:09

NevinJ