Python使用Openpyxl处理Excel时无法将更新后的工作表回写至原工作簿

• 使用pd.ExcelWriter初始化openpyxl引擎时默认是写入模式，会直接清空原有目标文件生成空的占位文件，后续openpyxl.load_workbook加载这个空文件时，因为xlsx本质是zip压缩包，空文件不符合zip结构，就会触发zipfile.BadZipFile报错。
• 原有代码缺少已有工作表同步配置，追加写入时会自动给重名工作表加数字后缀，无法实现覆盖更新原有工作表的需求。

import openpyxl
import pandas as pd
import os
import numpy as np

# step 1: 读取5个源Excel文件
df1 = pd.read_excel(r"file1path.xlsx")
df2 = pd.read_excel(r"file2path.xlsx")
df3 = pd.read_excel(r"file3path.xlsx")
df4 = pd.read_excel(r"file4path.xlsx")
df5 = pd.read_excel(r"file5path.xlsx")

dest_filename = r'masterfilepath.xlsx'

# step 2: 第一次写入生成总工作簿
with pd.ExcelWriter(dest_filename, engine='xlsxwriter') as writer:
    df1.to_excel(writer, sheet_name='W.5 Revenue', index=False)
    df2.to_excel(writer, sheet_name='W.4 Rev details', index=False)
    df3.to_excel(writer, sheet_name='W.7 Accrual', index=False)
    df4.to_excel(writer, sheet_name='W.6 Adhoc', index=False)
    df5.to_excel(writer, sheet_name='W.8 State', index=False)

# step 3: 处理df1数据后回写更新
df1 = df1[df1["Ledger account"].str.startswith('4')]

# 追加模式初始化ExcelWriter，不会覆盖原有文件，适配pandas 1.4.0及以上版本
with pd.ExcelWriter(dest_filename, engine='openpyxl', mode='a', if_sheet_exists='replace') as writer:
    # 直接写入目标工作表，if_sheet_exists='replace'会自动覆盖已有同名工作表
    df1.to_excel(writer, sheet_name='W.5 Revenue', index=False)

若你使用的pandas版本低于1.4.0，不支持if_sheet_exists参数，可替换step3的代码为：

if os.path.exists(dest_filename):
    book = openpyxl.load_workbook(dest_filename)
    # 先删除原有同名工作表
    if 'W.5 Revenue' in book.sheetnames:
        del book['W.5 Revenue']
    writer = pd.ExcelWriter(dest_filename, engine='openpyxl')
    writer.book = book
    # 同步已有工作表信息，避免自动生成重名后缀工作表
    writer.sheets = {ws.title: ws for ws in book.worksheets}
    df1.to_excel(writer, sheet_name='W.5 Revenue', index=False)
    writer.save()
    writer.close()

• 追加写入时必须加mode='a'参数，避免初始化阶段覆盖原有文件
• 用with上下文管理器管理ExcelWriter，不需要手动调用save和close，避免资源泄漏

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