Apache POI changing cell format from dot to comma-CodePudding

I'm using Apache POI to generate excel files. I'm trying to format my cells to display no decimal cases and use dot as the thousand separator. (187103,915 -> 187.103).

If I apply the format #.##0 directly into the cell, it works as intended.

So I used this code to set the format to the cell.

CellStyle cs = workbook.createCellStyle();
DataFormat df = workbook.createDataFormat();
cs.setDataFormat(df.getFormat("#.##0"));
celula.setCellStyle(cs);

But for some reason my dot is being replaced by a comma, changing my format to #,##0. When inspecting the generated excel file I can see the wrong format:

The same thing happens if I try to set some weird formats like ---#.#---:

Does anyone have any idea of why this is happening?

CodePudding user response：

Apparently this is a region/locale issue. I'm in Brazil, so we use comma as decimal separator and dot as thousand separator. As my Excel locale is defined as pt/BR, it is using the same format. Changing my format to #,##0 in code resulted in the format #.##0 in my excel file. Weird, but ok.

CodePudding user response：

What Excel uses as the decimal separator and as the thousands separator determines on the Excel locale version and on the operating system settings. It cannot be determined by the number format.

Furthermore in Excel's files all number formats are stored in en_US format always. The locale dependent changes are done in Excel's GUI only.

The number format #.##0 means one digit (only if significant), followed by the decimal separator, followed by two digits (only if significant), followed by one digit. That does not make any sense.

From your screenshots it seems you have an Excel application and/or an operating system, where the decimal separator is comma and the thousands separator is dot. So in your Excel the number format #,##0, which means one digit (only if significant), followed by the thousands separator, followed by two digits (only if significant), followed by one digit, will lead to #.##0 in your Excel GUI.

So do using cs.setDataFormat(df.getFormat("#,##0")); and it should do what you want.