使用xlsxwriter设置Excel列数字格式无效问题求助

问题原因 #

当你使用styler.to_excel()导出时，Pandas会为每个单元格生成独立的格式样式，单元格级格式的优先级高于XlsxWriter设置的列级格式，导致你后续配置的英镑货币格式、百分比格式被覆盖，无法生效。

解决方案 #

方案1：直接导出原始DataFrame（推荐，若无需保留Styler样式） #

如果Styler的样式不是必须项，直接导出原始DataFrame，再用XlsxWriter设置格式，可彻底避免格式覆盖问题：

with filedialog.asksaveasfile(mode='w', defaultextension=".xlsx", filetypes=[("Excel files", ".xlsx .xls")]) as file:
    writer = pd.ExcelWriter(file.name, engine="xlsxwriter")
    # 替换为原始DataFrame导出，而非styler对象
    df.to_excel(writer, columns=['Consultant','Dept','T/S ID','Client','Candidate','P/E Date','Charge','Pay','Split GP','Split %','Margin%','Comm %','Comm Value','Perm Salary','Placement'], index=False, sheet_name= "561 Comms")
    workbook = writer.book
    worksheet = writer.sheets['561 Comms']

    number_fmt = workbook.add_format({'num_format' : '0'})
    money_fmt = workbook.add_format({'num_format' : '£#,##0.00'})
    rate_fmt = workbook.add_format({'num_format' : '#0.00%'})

    worksheet.set_zoom(80)
    worksheet.set_column('G:I', 20, money_fmt)
    worksheet.set_column('J:L', 12, rate_fmt)
    writer.close()
except AttributeError as ae:
    print(ae)

方案2：在Styler中直接设置数字格式（保留Styler样式） #

若需要保留Styler的其他样式，直接在Styler中定义数字格式，导出时就会自带正确格式，无需后续用XlsxWriter重复配置：

# 先给Styler设置对应列的格式规则
styler = df.style.format({
    'Charge': '£{:,.2f}',
    'Pay': '£{:,.2f}',
    'Split GP': '£{:,.2f}',
    'Split %': '{:.2%}',
    'Margin%': '{:.2%}',
    'Comm %': '{:.2%}'
})

# 后续导出代码仅保留列宽和缩放设置
with filedialog.asksaveasfile(mode='w', defaultextension=".xlsx", filetypes=[("Excel files", ".xlsx .xls")]) as file:
    writer = pd.ExcelWriter(file.name, engine="xlsxwriter")
    styler.to_excel(writer, columns=['Consultant','Dept','T/S ID','Client','Candidate','P/E Date','Charge','Pay','Split GP','Split %','Margin%','Comm %','Comm Value','Perm Salary','Placement'], index=False, sheet_name= "561 Comms")
    workbook = writer.book
    worksheet = writer.sheets['561 Comms']

    worksheet.set_zoom(80)
    # 仅设置列宽，无需重复配置格式
    worksheet.set_column('G:I', 20)
    worksheet.set_column('J:L', 12)
    writer.close()
except AttributeError as ae:
    print(ae)

方案3：逐个覆盖单元格格式（适合复杂场景） #

如果必须用XlsxWriter覆盖Styler的单元格格式，需要遍历对应列的单元格逐个设置（效率较低，不推荐大表格）：

with filedialog.asksaveasfile(mode='w', defaultextension=".xlsx", filetypes=[("Excel files", ".xlsx .xls")]) as file:
    writer = pd.ExcelWriter(file.name, engine="xlsxwriter")
    styler.to_excel(writer, columns=['Consultant','Dept','T/S ID','Client','Candidate','P/E Date','Charge','Pay','Split GP','Split %','Margin%','Comm %','Comm Value','Perm Salary','Placement'], index=False, sheet_name= "561 Comms")
    workbook = writer.book
    worksheet = writer.sheets['561 Comms']

    money_fmt = workbook.add_format({'num_format' : '£#,##0.00'})
    rate_fmt = workbook.add_format({'num_format' : '#0.00%'})

    worksheet.set_zoom(80)
    worksheet.set_column('G:I', 20)
    worksheet.set_column('J:L', 12)

    # 遍历G-I列（对应Charge、Pay、Split GP）的单元格，逐个设置货币格式
    for row_num in range(1, len(df)+1):  # 从第2行开始，第1行是表头
        for col_num in range(6, 9):  # G列对应索引6，I列对应索引8
            worksheet.write(row_num, col_num, df.iloc[row_num-1, col_num], money_fmt)
    # 遍历J-L列的单元格，逐个设置百分比格式
    for row_num in range(1, len(df)+1):
        for col_num in range(9, 12):
            worksheet.write(row_num, col_num, df.iloc[row_num-1, col_num], rate_fmt)

    writer.close()
except AttributeError as ae:
    print(ae)

内容的提问来源于stack exchange，提问作者AP113BY