结合pandas使用xlsxwriter设置Excel格式后导出文件损坏的问题咨询

你遇到的文件损坏提示，和你怀疑的format1 = workbook.add_format({'font_color': 'red'})这行代码没有关系，问题全部出在xlsxwriter对象的使用逻辑和代码执行顺序上，具体有三个硬伤：

• 跨工作簿实例混用对象
  pandas初始化xlsxwriter引擎的ExcelWriter时，已经在内部自动创建了一个绑定你指定输出路径的xlsxwriter.Workbook实例，所有格式创建、单元格写入操作，都必须基于这个内部实例完成，不然就会破坏xlsx文件的结构规范。但你手动写了一行workbook = xlsxwriter.Workbook('export_top200FORMATTED.xlsx')，单独开了一个完全独立、和pandas写入器没有关联的空工作簿。后续你创建的红字体格式，是挂在这个独立空工作簿上的，当你把这个外来的格式对象传给pandas关联的工作表去设置列格式时，xlsxwriter生成的文件里会出现没有归属关系的格式资源，Excel解析时就会判定文件损坏，触发修复提示。
• 代码执行顺序倒置
  你在给worksheet变量赋值之前，就提前在循环里调用worksheet.write(0, idx, val)写表头。按照你贴的代码顺序，运行到这行循环时，worksheet还没有被定义，哪怕没有触发Python解释报错，后续操作的时序也是完全混乱的，写入的内容根本不会到目标工作表里。
• 输出路径不统一
  pandas的写入器绑定的是绝对路径/Users/BTS/Documents/python_work/ABC Charts/export_top200FORMATTED.xlsx，但你手动创建的独立工作簿用的是当前代码运行目录下的相对路径同名文件，两个对象写入的根本不是同一个目标位置，最终生成的文件里自然找不到格式资源的对应引用。

核心修改思路是绝对不要手动初始化独立的xlsxwriter.Workbook实例，所有用到的工作表、工作簿对象，都直接从pandas的ExcelWriter里拿，按照正确的执行顺序调整后的代码如下：

import pandas as pd
import xlsxwriter

# 读取源清理后的数据
ca_top200_CLEANED = 'export_top200CLEANEDTEST.xlsx'
dfCAtop200_Formatted = pd.read_excel(ca_top200_CLEANED)
column_list = dfCAtop200_Formatted.columns

# 初始化Excel写入器，绑定最终输出路径
writer = pd.ExcelWriter(
    '/Users/BTS/Documents/python_work/ABC Charts/export_top200FORMATTED.xlsx', 
    engine='xlsxwriter'
)
dfCAtop200_Formatted.to_excel(writer, index=False)

# 提前从写入器里拿到已绑定好的工作簿、工作表对象，不要自己新建
workbook = writer.book
worksheet = writer.sheets['Sheet1']

# 重新写入表头，覆盖默认格式
for idx, val in enumerate(column_list):
    worksheet.write(0, idx, val)

# 基于绑定好的工作簿创建自定义格式
format1 = workbook.add_format({'font_color': 'red'})

# 给B列设置宽度和自定义格式
worksheet.set_column('B:B', 11, format1)

# 执行写入保存
writer.save()

• xlsxwriter的工作簿、格式、工作表三类对象是强绑定的，跨实例传入的格式或工作表对象，100%会生成结构损坏的xlsx文件
• 所有格式设置、单元格写入操作必须在writer.save()之前完成，保存之后再操作对象不会对最终文件产生任何效果

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