XlsxWriter with Pandas dataframe thousand separator

To my little knowledge, Xlsxwriter may be the best package to format my numbers with thousand separator. I have read xlsxwriter documents many times, still very confusing, I think others may have the same problem, thus I post my question here. I have a pandas dataframe DF_T_1_EQUITY_CHANGE_Summary_ADE, and I want to export them to excel and with the formating thousand separator.

Row Labels               object
Sum of EQUITY_CHANGE    float64
Sum of TRUE_PROFIT      float64
Sum of total_cost       float64
Sum of FOREX VOL        float64
Sum of BULLION VOL      float64
Oil                     float64
Sum of CFD VOL           object
Sum of BITCOIN VOL       object
Sum of DEPOSIT          float64
Sum of WITHDRAW         float64
Sum of IN/OUT           float64
dtype: object

the dataframe DF_T_1_EQUITY_CHANGE_Summary_ADE is clear except the first column Row Labels is object, others are all numbers. So, I use xlsxwriter to write the dataframe into excel:

import xlsxwriter 
num_fmt = workbook.add_format({'num_format': '#,###'}) #set the separator I want
writer = pd.ExcelWriter('ADE_CN.xlsx', engine='xlsxwriter')
DF_T_1_EQUITY_CHANGE_Summary_ADE.to_excel(writer, sheet_name='Sheet1')
workbook=writer.book
worksheet = writer.sheets['Sheet1']
worksheet.set_column('C:M', None, num_fmt)
writer.save()

However, I dont get the thousand separator, the result in the excel is below:

    Row Labels  Sum of EQUITY_CHANGE    Sum of TRUE_PROFIT  Sum of total_cost   Sum of FOREX VOL    Sum of BULLION VOL  Oil Sum of CFD VOL  Sum of BITCOIN VOL  Sum of DEPOSIT  Sum of WITHDRAW Sum of IN/OUT
0   ADE A BOOK USD  778.17  517.36  375.9   37.79   0.33    0   0   0   1555.95 0   1555.95
1   ADE B BOOK USD  6525.51 403.01  529.65  35.43   14.3    0   0   0   500 -2712.48    -2212.48
2   ADE A BOOK AUD  537.7   189.63  147 12.25   0   0   0   0   0   0   0
3   ADE B BOOK AUD  -22235.71   7363.14 224.18  2.69    9.16    0.2 0   0   5000    -103    4897

Can someone provide a solution, much appreciated.

Solution

It should work. You need to move the add_format() a bit later in your code, after you get a reference to the workbook object. Here is an example:

import pandas as pd


# Create a Pandas dataframe from some data.
df = pd.DataFrame({'Data': [1234.56, 234.56, 5678.92]})

# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter('pandas.xlsx', engine='xlsxwriter')

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

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

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

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

Output: