openpyxl处理大型xlsm文件致损坏，求Pandas写入Excel表方案

问题背景 #

我做了一个文件拆分工具：通过shutil.copy复制11MB的复杂XLSM主文件，再用openpyxl修改副本使其仅保留指定用户（如User A/B/C）的数据，重复该操作完成拆分。但现在遇到问题：

• 主文件包含多个Excel表格（非普通单元格区域）、数据模型、启动自动更新的透视表、图表及控制图表的切片器
• shutil.copy生成的副本可正常打开，但仅用openpyxl打开并保存（未做任何修改）就会触发文件损坏错误
• 135KB的同功能小文件操作完全正常

最小复现代码 #

import openpyxl

# 定位文件
file = "C:\\blah blah\\sourcefile_usera.xlsm"

wb = openpyxl.load_workbook(file, read_only=False, keep_vba=True)

wb.save(file)
wb.close()

Excel错误提示 #

We found a problem with some content in 'sourcefile_usera.xlsm'. Do you want us to try to recover as much as we can? If you trust the source of this workbook, click Yes.

点击“是”后Excel会陷入打开循环，需通过任务管理器强制关闭。

已尝试的无效方案 #

• 尝试用pandas筛选数据，但因透视表和图表依赖内置Excel表格，无法将DataFrame写入原有表格，转用openpyxl
• 降级openpyxl至v3.0.1、3.0.3、2.6.4版本，问题依旧
• 将主文件转为XLSX格式保存，无效
• 尝试openpyxl的read_only/write_only模式：前者无法修改文件，后者无法保留原文件完整性，均不适用

需求 #

要么解决大文件用openpyxl保存后损坏的问题，要么实现将pandas DataFrame写入现有Excel表格，同时保留文件中的隐藏工作表、透视表、图表、数据模型等内容。

解决方案1：调用Excel原生COM接口（win32com） #

openpyxl对XLSM中的高级特性（数据模型、透视表、切片器）支持有限，直接调用Excel自身的API能完美保留所有原生内容，不会损坏文件。示例代码：

import win32com.client as win32
import shutil

# 复制源文件到目标路径
source_path = "C:\\path\\to\\main.xlsm"
target_path = "C:\\path\\to\\user_a.xlsm"
shutil.copy(source_path, target_path)

# 后台启动Excel
excel = win32.gencache.EnsureDispatch('Excel.Application')
excel.Visible = False
wb = excel.Workbooks.Open(target_path)

# 定位目标工作表和表格
data_sheet = wb.Worksheets["数据工作表"]  # 替换为你的工作表名
target_table = data_sheet.ListObjects["数据表格"]  # 替换为你的表格名

# 筛选指定用户数据（假设第1列是用户标识列）
target_table.Range.AutoFilter(Field=1, Criteria1="User A")

# 删除筛选后的非匹配行（保留表头）
target_table.DataBodyRange.SpecialCells(win32.constants.xlCellTypeVisible).Delete()

# 取消筛选并保存
data_sheet.AutoFilterMode = False
wb.Save()
wb.Close()
excel.Quit()

注意：此方案依赖Windows环境和已安装的Excel软件。

解决方案2：使用xlwings库（更简洁的原生API调用） #

xlwings封装了Excel的COM接口，语法更直观，同时支持Windows和Mac平台：

import xlwings as xw
import pandas as pd
import shutil

source_path = "C:\\path\\to\\main.xlsm"
target_path = "C:\\path\\to\\user_a.xlsm"
shutil.copy(source_path, target_path)

# 后台操作Excel
with xw.App(visible=False) as app:
    wb = xw.Book(target_path)
    data_sheet = wb.sheets["数据工作表"]
    target_table = data_sheet.tables["数据表格"]
    
    # 读取表格数据为DataFrame并筛选
    table_df = target_table.options(pd.DataFrame).value
    filtered_df = table_df[table_df["用户列"] == "User A"]  # 替换为你的用户列名
    
    # 清空表格原有数据并写入筛选结果
    target_table.data_body_range.delete()
    data_sheet.range(target_table.data_body_range.address).value = filtered_df
    
    wb.save()
    wb.close()

优势：代码更简洁，对Excel对象的操作更符合Python习惯，原生特性保留完整。

解决方案3：优化openpyxl操作（仅适用于特性较少的场景） #

如果必须使用openpyxl，需要手动维护Excel表格的引用范围，减少对文件结构的破坏：

import openpyxl
import pandas as pd
from openpyxl.utils.dataframe import dataframe_to_rows

file_path = "C:\\blah blah\\sourcefile_usera.xlsm"

# 加载文件时保留所有关键属性
wb = openpyxl.load_workbook(
    file_path,
    keep_vba=True,
    data_only=False,
    keep_links=True
)

data_sheet = wb["数据工作表"]
target_table = data_sheet.tables["数据表格"]

# 读取并筛选数据
df = pd.read_excel(file_path, sheet_name="数据工作表", header=0)
filtered_df = df[df["用户列"] == "User A"]

# 清空表格原有数据（保留表头）
start_row = target_table.ref.split(':')[0].row + 1
end_row = data_sheet.max_row
if end_row >= start_row:
    data_sheet.delete_rows(start_row, end_row - start_row + 1)

# 写入筛选后的数据
for row_idx, row in enumerate(dataframe_to_rows(filtered_df, index=False, header=False), start=start_row):
    for col_idx, value in enumerate(row, 1):
        data_sheet.cell(row=row_idx, column=col_idx, value=value)

# 更新表格的引用范围
new_end_cell = data_sheet.cell(row=start_row + len(filtered_df) - 1, column=len(filtered_df.columns))
target_table.ref = f"{target_table.ref.split(':')[0]}:{new_end_cell.coordinate}"

wb.save(file_path.replace(".xlsm", "_fixed.xlsm"))
wb.close()

注意：此方案对复杂的数据模型和透视表可能仍会导致损坏，仅建议在无法使用原生API时尝试。

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