Pandas导出Excel时同时设置数字格式与背景色失效问题求助

在Pandas中处理数据后导出至Excel，尝试同时设置数字格式与背景色时出现冲突：单独设置数字格式可正常生效，但添加背景色设置后，数字格式失效。

测试代码 #

仅设置数字格式（正常生效） #

df = pd.DataFrame({
    'Name': ['John', 'Isac'],
    'Value': [100000, 300000000]
})

with pd.ExcelWriter('test.xlsx', engine='xlsxwriter') as writer:
    df.to_excel(writer, sheet_name='test', index=False)
    
    workbook = writer.book
    worksheet = writer.sheets['test']
    
    format_with_commas = workbook.add_format({'num_format':'#,##0'})
    worksheet.set_column('B:B', 15, format_with_commas)

添加背景色后数字格式失效 #

df = pd.DataFrame({
    'Name': ['John', 'Isac'],
    'Value': [100000, 300000000]
})

styled_df = df.style.set_properties(**{'background-color':'yellow', 'color':'black'})

with pd.ExcelWriter('test.xlsx', engine='xlsxwriter') as writer:
    styled_df.to_excel(writer, sheet_name='test', index=False)
    
    workbook = writer.book
    worksheet = writer.sheets['test']
    
    format_with_commas = workbook.add_format({'num_format':'#,##0'})
    worksheet.set_column('B:B', 15, format_with_commas)

问题原因 #

当使用df.style生成样式化DataFrame导出时，Pandas会为每个单元格单独添加格式（包括背景色、字体颜色）。而XlsxWriter的格式优先级规则为：单元格单独设置的格式优先级高于列级格式。后续通过worksheet.set_column()设置的列数字格式会被单元格已有的背景色格式覆盖，导致数字格式不生效。

解决方法 #

方法1：通过Pandas Style同时设置所有样式 #

直接在style对象中同时定义背景色和数字格式，一次性导出完成样式设置：

df = pd.DataFrame({
    'Name': ['John', 'Isac'],
    'Value': [100000, 300000000]
})

# 同时设置背景色、字体颜色和数字格式
styled_df = df.style \
    .set_properties(**{'background-color': 'yellow', 'color': 'black'}) \
    .format({'Value': '{:,}'})  # 对Value列设置千分位数字格式

with pd.ExcelWriter('test.xlsx', engine='xlsxwriter') as writer:
    styled_df.to_excel(writer, sheet_name='test', index=False)

方法2：使用XlsxWriter创建组合格式 #

不依赖Pandas Style，直接通过XlsxWriter创建包含背景色和数字格式的统一格式对象，再应用到目标列：

df = pd.DataFrame({
    'Name': ['John', 'Isac'],
    'Value': [100000, 300000000]
})

with pd.ExcelWriter('test.xlsx', engine='xlsxwriter') as writer:
    df.to_excel(writer, sheet_name='test', index=False)
    
    workbook = writer.book
    worksheet = writer.sheets['test']
    
    # 创建同时包含背景色、字体颜色和数字格式的组合格式
    combined_format = workbook.add_format({
        'num_format': '#,##0',
        'bg_color': 'yellow',
        'font_color': 'black'
    })
    
    # 将组合格式应用到B列
    worksheet.set_column('B:B', 15, combined_format)

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