带有 Pandas 数据框千位分隔符的 XlsxWriter

Question

据我所知，Xlsxwriter 可能是用千位分隔符格式化我的数字的最佳软件包。我已经多次阅读 xlsxwriter 文档，仍然很困惑，我认为其他人可能有同样的问题，因此我在这里发布我的问题。我有一个熊猫数据框 DF_T_1_EQUITY_CHANGE_Summary_ADE，我想将它们导出到 excel 并使用格式化千位分隔符。Row Labels               objectSum of EQUITY_CHANGE    float64Sum of TRUE_PROFIT      float64Sum of total_cost       float64Sum of FOREX VOL        float64Sum of BULLION VOL      float64Oil                     float64Sum of CFD VOL           objectSum of BITCOIN VOL       objectSum of DEPOSIT          float64Sum of WITHDRAW         float64Sum of IN/OUT           float64dtype: object数据框 DF_T_1_EQUITY_CHANGE_Summary_ADE 很清楚，除了第一列行标签是对象，其他都是数字。因此，我使用 xlsxwriter 将数据框写入 excel：import xlsxwriter num_fmt = workbook.add_format({'num_format': '#,###'}) #set the separator I wantwriter = pd.ExcelWriter('ADE_CN.xlsx', engine='xlsxwriter')DF_T_1_EQUITY_CHANGE_Summary_ADE.to_excel(writer, sheet_name='Sheet1')workbook=writer.bookworksheet = writer.sheets['Sheet1']worksheet.set_column('C:M', None, num_fmt)writer.save()但是，我没有得到千位分隔符，excel中的结果如下：    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/OUT0   ADE A BOOK USD  778.17  517.36  375.9   37.79   0.33    0   0   0   1555.95 0   1555.951   ADE B BOOK USD  6525.51 403.01  529.65  35.43   14.3    0   0   0   500 -2712.48    -2212.482   ADE A BOOK AUD  537.7   189.63  147 12.25   0   0   0   0   0   0   03   ADE B BOOK AUD  -22235.71   7363.14 224.18  2.69    9.16    0.2 0   0   5000    -103    4897有人可以提供解决方案，非常感谢。

Answer 1

它应该工作。在获得对工作簿对象的引用后，您需要add_format()稍后在代码中移动它。这是一个例子：

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

输出：