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
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.
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.
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.
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
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With