如何用Python替换复杂Excel工作簿中的工作表

推荐方案：用pywin32调用Excel原生API #

这种方式直接调用本地Excel进程处理，对带透视表、图表的复杂工作簿兼容性拉满，速度也更快——因为不用在Python里解析整个工作簿的复杂结构，全部交给Excel自身处理原有内容。

步骤： #

1. 安装依赖：pip install pywin32
2. 编写替换代码：

import win32com.client as win32
import pandas as pd

# 准备待写入的数据
df1 = pd.DataFrame(...)  # 填入你的数据
df2 = pd.DataFrame(...)

# 启动Excel后台进程
excel = win32.gencache.EnsureDispatch('Excel.Application')
excel.Visible = False  # 生产环境建议后台运行，调试时可设为True
wb = excel.Workbooks.Open(r'C:\full\path\to\your\filename.xlsx')

# 批量替换工作表
sheet_name_list = ['sheet1', 'sheet2']
data_frame_list = [df1, df2]

for sheet_name, df in zip(sheet_name_list, data_frame_list):
    # 先删除旧表（如果存在）
    try:
        wb.Worksheets(sheet_name).Delete()
    except Exception:
        pass
    # 新建同名工作表
    new_sheet = wb.Worksheets.Add()
    new_sheet.Name = sheet_name
    # 写入表头
    for col_idx, col_name in enumerate(df.columns, start=1):
        new_sheet.Cells(1, col_idx).Value = col_name
    # 写入数据行
    for row_idx, row_data in enumerate(df.values, start=2):
        for col_idx, cell_val in enumerate(row_data, start=1):
            new_sheet.Cells(row_idx, col_idx).Value = cell_val

# 保存并关闭
wb.Save()
wb.Close()
excel.Quit()

注意事项： #

• 确保目标Excel文件未被其他进程占用，否则会触发报错
• 只要保持工作表名称不变，原有透视表、图表与数据的关联会自动保留

备选方案：优化openpyxl用法 #

如果偏好openpyxl，可通过以下方式提升性能：

from openpyxl import load_workbook
import pandas as pd

# 加载工作簿，关闭自动计算以提速
wb = load_workbook('filename.xlsx')
wb.calculation.calcMode = 'manual'

# 用replace模式直接覆盖工作表（需pandas 1.4.0+）
with pd.ExcelWriter('filename.xlsx', engine='openpyxl', mode='a', if_sheet_exists='replace') as writer:
    df1.to_excel(writer, sheet_name='sheet1', index=False)
    df2.to_excel(writer, sheet_name='sheet2', index=False)

# 恢复自动计算并保存
wb.calculation.calcMode = 'automatic'
wb.save()

关键优化点：关闭自动计算避免加载工作簿时触发大量公式运算，用if_sheet_exists='replace'直接替换而非追加工作表。

简洁方案：用xlwings库 #

xlwings语法更贴近Excel操作，同样调用原生API，代码更简洁：

1. 安装：pip install xlwings
2. 示例代码：

import xlwings as xw
import pandas as pd

df1 = pd.DataFrame(...)
df2 = pd.DataFrame(...)

# 后台运行Excel
with xw.App(visible=False) as app:
    wb = xw.Book('filename.xlsx')
    
    # 替换sheet1
    try:
        wb.sheets['sheet1'].delete()
    except Exception:
        pass
    wb.sheets.add(name='sheet1').range('A1').value = df1
    
    # 替换sheet2
    try:
        wb.sheets['sheet2'].delete()
    except Exception:
        pass
    wb.sheets.add(name='sheet2').range('A1').value = df2
    
    wb.save()

xlwings对复杂Excel对象（透视表、图表）的支持更友好，日常维护成本更低。

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