Xlsxwriter with pandas, use dot as thousand separator-CodePudding

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()

CodePudding user response：

The approach OP is following is equal to what

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

which is the desired output

1.000
123.123.123