使用Python覆盖Excel指定工作表时其余表丢失、文件损坏如何解决

• 第一版代码默认使用w（覆盖写入）模式创建ExcelWriter实例，会直接生成空白的全新Excel文件覆盖原文件，因此写入完成后原文件内仅保留新写入的Previous Month工作表，其余7个工作表全部丢失。
• 第二版代码运行后文件损坏，通常由三个原因导致：
  • pandas版本低于1.4.0，低版本不支持if_sheet_exists参数，参数解析异常破坏了Excel文件结构
  • 运行代码时目标file.xlsx正被Excel/WPS等办公软件打开，文件锁冲突导致写入中断
  • openpyxl版本过低，对xlsx格式的解析兼容存在已知bug

首先先升级依赖包到最新稳定版本，避免版本兼容问题：

pip install --upgrade pandas openpyxl

方案一：最简实现（pandas 1.4+ 推荐） #

使用上下文管理器自动处理文件保存和资源释放，不需要手动调用save()和close()，避免资源未释放导致的文件损坏：

import pandas as pd

# 读取txt格式的源数据
df = pd.read_csv('file.txt', sep='\t')

# 追加模式打开文件，仅替换指定工作表，其余工作表全部保留
with pd.ExcelWriter(
    'file.xlsx',
    mode='a',
    engine='openpyxl',
    if_sheet_exists='replace'
) as writer:
    df.to_excel(
        writer,
        sheet_name='Previous Month',
        startrow=2,
        startcol=4,
        index=False  # 不需要pandas自动生成的行索引列就加上该参数，避免多出无意义序号列
    )

方案二：稳妥兼容方案（适配低版本pandas） #

如果受环境限制无法升级pandas，可以手动加载原有工作簿，迁移需要保留的工作表后再写入新内容：

import pandas as pd
from openpyxl import load_workbook

# 读取txt源数据
df = pd.read_csv('file.txt', sep='\t')

# 加载原有完整Excel工作簿
wb = load_workbook('file.xlsx')
# 先删除已存在的旧目标工作表
if 'Previous Month' in wb.sheetnames:
    del wb['Previous Month']

with pd.ExcelWriter('file.xlsx', engine='openpyxl') as writer:
    writer.book = wb
    # 将所有需要保留的原有工作表绑定到写入对象
    for sheet_name in wb.sheetnames:
        ws = wb[sheet_name]
        ws._parent = writer.book
        writer._sheets.append(ws)
    # 写入新的目标工作表内容
    df.to_excel(
        writer,
        sheet_name='Previous Month',
        startrow=2,
        startcol=4,
        index=False
    )

• 运行代码前必须完全关闭所有打开目标Excel文件的办公软件，否则会因为文件占用导致写入失败、文件损坏
• 如果写入后发现表格最前面多出一列从0开始的序号列，就是没加index=False参数导致的，补上即可
• 不要修改startrow和startcol之外的工作表格式参数，避免破坏原有表格的样式设置

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