CODEWITHSUNDEEP
javaexcelapache-poi
Madalina
Madalina

Reputation: 1307

Change decimal and thousands separators in excel using Apache POI

Does anyone know if using apache-poi library you can change the decimal and thousands separators for Microsoft Excel?

I need to export in excel some data from an web application, and the numbers are formatted depending on some the user's settings. so when the data is exported the numbers should look exactly how they are in the application's page.

Thanks

Upvotes: 3

Views: 11322

Answers (3)

Mateusz Owsiański
Mateusz Owsiański

Reputation: 9

There's only formatting. It means this format is my format for formatting numeric. The comma is a symbol equals only part of thousands while the dot is part of decimal. You could use "#,##0.00" or "#,##0" does not matter because Microsoft Excel has local settings of separator applies to the application, not a file, you cannot override via API. Remember, the sheet has a predefined cell style. A cell has a reference only to style. If you change on cell, you change all cells this type.

I have the same issue with format of cell. I think I try to use the method "setVBAProject" on XSSFWorkbook.

https://social.technet.microsoft.com/Forums/office/en-US/eaa4c7f6-197a-4b33-bc5f-20896e5a7e3a/workbook-or-worksheet-specific-decimal-separator?forum=excel

Upvotes: 1

Vargan
Vargan

Reputation: 1317

You need to set your CellStyle dataFormat in this way (if you use integer and want thousand separator)

cellStyle.setDataFormat(creationHelper.createDataFormat().getFormat("#,##0")); 
cell.setCellStyle(cellStyle);

I think that you need something like that: (I didn't try it, so maybe you need to modify it a little bit) #,##0.00 please note: is very important you use comma, and not dot. If your locale is setted correcty, you will see a dot.

Upvotes: 5

Christophe Tela
Christophe Tela

Reputation: 35

Formatting in Excel is controlled through the Tools > Options > International dialogs, and is stored in local preferences, not in a file. So you can't control this through POI.

The only solution I can think of is to provide text rather than numbers. But it will prevent user from doing any calculation in Excel.

Upvotes: 1

Related Questions