How to set currency type to a column in POI

Question

How do I convert a column to currency type in XSSFWorkbook using poi in java ?

Here is the code I have used :

DataFormat datafrmt = wbAll.createDataFormat();
cellStyle.setDataFormat(datafrmt.getFormat("$#,##0.00")); 

But after setting the data format I tried opening the Excel sheet and checked the format of the column it shows custom type but I need the column to be a currency type. How can I achieve this?

Answer 1

I would suggest you use one of the BuiltinFormats for currency. This one seems to be the one you're looking for 7, "$#,##0.00);($#,##0.00)".

Check how Apache suggests doing it by making a Map of XSSFCellStyle in their demo. After reading it, I think you are only missing this line of code for you format to work.

XSSFCellStyle style3 = wb.createCellStyle();
style3.setAlignment(XSSFCellStyle.ALIGN_RIGHT);  // THIS LINE HERE!
style3.setDataFormat(fmt.getFormat("$#,##0.00"));
styles.put("currency", style3);`

To use the BuiltinFormats, instead of using a XSSFDataFormat instance, you directly set the short of the wanted format, like this style3.setDataFormat((short) 7);