Xlsxwriter with pandas, use dot as thousand separator

Question

I would like for my excel numbers to come out having a . separator for thousands in the excel output example.xlsx. So I do not want to change the pandas numbers.

So instead of (my current result if you run the code)

1,000
123,123,123

I would like to see this

1.000
123.123.123

import pandas as pd


df = pd.DataFrame({"a": [1000.12, 123123123.23]})
writer = pd.ExcelWriter('example.xlsx', engine='xlsxwriter')

# Convert the dataframe to an XlsxWriter Excel object.
df.to_excel(writer, sheet_name='Sheet1', index=False)

# Get the xlsxwriter workbook and worksheet objects.
workbook  = writer.book
worksheet = writer.sheets['Sheet1']

# Set a currency number format for a column.
FORMAT = '#,###'
num_format = workbook.add_format({'num_format': FORMAT})
worksheet.set_column('A:A', None, num_format)

# Close the Pandas Excel writer and output the Excel file.
writer.save()

Answer 1

The approach OP is following is equal to what jmcnamara suggests (the creator of XlsxWriter), and after testing, there isn't a problem there.

Checked XlsxWriter's documentation and they warn about the differences that can happen between locales

In the US locale (and some others) where the number “grouping/thousands” separator is “,” and the “decimal” point is “.”

In other locales these values may be reversed or different. They are generally set in the “Region” settings of Windows or Mac OS. Excel handles this by storing the number format in the file format in the US locale, in this case #,##0.00, but renders it according to the regional settings of the host OS.

Considering that the format that appears in Excel depends on the Excel options, in order to change the Thousands separator from a , to a ., one can

1. Click File > Options

2. Then, on the Advanced, in the Editing options untick the Use system separators.

3. Finally, change the Thousands separator to . and press OK.

With that one can already see the desired output in the Excel file

which is the desired output

1.000
123.123.123