据我所知,Xlsxwriter可能是最适合使用千位分隔符格式化数字的包。我已经多次阅读了xlsxwriter文档,但仍然感到非常困惑,我认为其他人可能也有同样的问题,因此我在这里发表我的问题。我有一个pandas数据框DF_T_1_EQUITY_CHANGE_Summary_ADE,并且想将它们导出到Excel并使用千位分隔符格式。
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
数据框 DF_T_1_EQUITY_CHANGE_Summary_ADE 很清晰,除了第一列的 Row Labels 是对象类型,其他都是数字。 因此,我使用 xlsxwriter 将数据框写入 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()
然而,我无法获得千位分隔符。在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/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
有人能够提供一个解决方案吗,非常感激。
workbook
之前调用它,要么在调用workbook=writer.book
时重新定义。无论哪种方式,你都会失去添加的数字格式。 - James