pandas使用openpyxl写入Excel工作簿后文件损坏如何解决

问题原因 #

报错是因为使用openpyxl作为引擎追加工作表时，手动给writer.book赋值后没有同步已有工作表信息到writer对象的内部映射，导致写入时出现工作表定义冲突，最终生成的Excel文件结构损坏。

解决方案 #

pandas 1.4.0及以上版本可以直接使用内置的if_sheet_exists参数简化逻辑，不需要手动加载工作簿，配合with上下文管理器自动处理资源释放，从根源避免文件损坏。

修复后的完整代码如下：

import pandas as pd
from pathlib import Path

xls = pd.ExcelFile("test.xlsx")
df = pd.read_excel(xls, "Sheet1")

op_name = "op.xlsx"
my_file = Path(op_name)
if my_file.exists():
    # 追加模式，自动跳过已有工作表、创建新工作表
    with pd.ExcelWriter(op_name, engine='openpyxl', mode='a', if_sheet_exists='new') as writer:
        # 不需要保留pandas索引列就加index=False，按需调整
        df.to_excel(writer, sheet_name="r2", index=False)
else:
    # 新建工作簿模式
    with pd.ExcelWriter(op_name, engine='openpyxl') as writer:
        df.to_excel(writer, sheet_name="r1", index=False)

如果你的pandas版本低于1.4.0不支持if_sheet_exists参数，在原有逻辑基础上增加一行工作表同步代码即可：

import pandas as pd
from pathlib import Path
from openpyxl import load_workbook

xls = pd.ExcelFile("test.xlsx")
df = pd.read_excel(xls, "Sheet1")

op_name = "op.xlsx"
my_file = Path(op_name)
if my_file.exists():
    book = load_workbook(my_file)
    writer = pd.ExcelWriter(op_name, engine='openpyxl', mode='a')
    writer.book = book
    # 新增这行同步已有工作表信息到writer对象
    writer._sheets = [ws for ws in book.worksheets]
    df.to_excel(writer, sheet_name="r2", index=False)
else:
    writer = pd.ExcelWriter(op_name, engine='openpyxl')
    df.to_excel(writer, sheet_name="r1", index=False)
writer.save()
writer.close()

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