求助：Pandas to_excel导出XLSM丢失宏的解决方法

问题背景 #

有3000个带宏和公式的中等复杂度XLSM文件（各含7个工作表），需在指定工作表的固定列中查找替换字符串。当前采用Pandas+openpyxl方案，但写入后文件损坏（改xlsx后缀才可打开）且宏丢失。

核心问题分析 #

直接使用mode='a'写入原XLSM文件时，Pandas+openpyxl的默认处理方式易破坏Excel的VBA归档结构，导致文件损坏、宏丢失。需调整代码确保VBA信息被正确保留，同时避免文件结构被破坏。

修正后的Pandas+openpyxl方案 #

关键调整点 #

• 加载工作簿时必须指定keep_vba=True，完整保留VBA数据
• 显式传递vba_archive给ExcelWriter，确保宏信息不丢失
• 优化字符串检查逻辑，避免TypeError
• 建议写入时排除DataFrame索引，避免多余列干扰原表结构

修正代码 #

import pandas as pd
import openpyxl
import re
import warnings

# 遍历所有XLSM文件（假设files是存储文件路径的字典/列表）
for file_path in sorted(files.values()):
    # 屏蔽openpyxl的无关警告
    warnings.filterwarnings('ignore', category=UserWarning, module='openpyxl')
    
    # 读取指定工作表为DataFrame
    df = pd.read_excel(file_path, sheet_name='Planning')
    
    # 检查目标列是否存在指定字符串
    string_found = False
    # 优化：跳过空值并统一转字符串，避免TypeError
    for comment in df['Comment'].dropna().astype(str):
        if re.search('mystring', comment, re.IGNORECASE):
            string_found = True
            print(file_path, comment)
    
    if string_found:
        # 加载原工作簿，强制保留VBA
        book = openpyxl.load_workbook(file_path, keep_vba=True)
        
        # 创建ExcelWriter，关联已加载的工作簿
        with pd.ExcelWriter(
            file_path,
            engine='openpyxl',
            mode='a',
            if_sheet_exists='replace'
        ) as writer:
            # 绑定已加载的工作簿和工作表映射
            writer.workbook = book
            writer.sheets = {ws.title: ws for ws in book.worksheets}
            # 关键：传递VBA归档信息，确保宏被保留
            writer.vba_archive = book.vba_archive
            
            # 写入新工作表，排除DataFrame索引
            df.to_excel(writer, sheet_name='Planning2', index=False)

注意事项 #

1. 确保openpyxl版本≥2.5，更早版本对VBA的支持不完善
2. 处理前务必备份测试文件，避免批量操作时数据丢失
3. 确保所有文件在处理时处于关闭状态，否则会导致写入失败或文件损坏

替代方案：使用win32com直接调用Excel API #

如果追求100%保留宏、公式及原文件结构，推荐使用win32com.client直接操作Excel应用（仅支持Windows环境），该方案通过调用Excel原生API，完全避免文件结构损坏问题。

示例代码 #

import win32com.client as win32
import re
import os

# 初始化Excel应用，后台运行
excel = win32.gencache.EnsureDispatch('Excel.Application')
excel.Visible = False
excel.DisplayAlerts = False  # 屏蔽保存提示

for file_path in sorted(files.values()):
    # 打开目标文件
    wb = excel.Workbooks.Open(file_path)
    ws = wb.Worksheets('Planning')
    
    string_found = False
    # 获取Comment列的实际使用范围（避免遍历整列）
    used_range = ws.UsedRange
    comment_col_idx = None
    # 遍历表头找到Comment列的位置
    for col in range(1, used_range.Columns.Count + 1):
        if ws.Cells(1, col).Value == 'Comment':
            comment_col_idx = col
            break
    
    if comment_col_idx:
        # 遍历该列的有效数据行
        for row in range(2, used_range.Rows.Count + 1):
            cell_value = ws.Cells(row, comment_col_idx).Value
            if cell_value and isinstance(cell_value, str):
                if re.search('mystring', cell_value, re.IGNORECASE):
                    string_found = True
                    print(file_path, cell_value)
                    # 如需替换字符串，直接修改单元格值
                    # ws.Cells(row, comment_col_idx).Value = cell_value.replace('mystring', 'new_value', flags=re.IGNORECASE)
    
    if string_found:
        # 新增工作表并复制原数据
        ws_new = wb.Worksheets.Add(After=wb.Worksheets(wb.Worksheets.Count))
        ws_new.Name = 'Planning2'
        ws.UsedRange.Copy(ws_new.Range('A1'))
        # 保存为XLSM格式（格式代码52对应xlsm）
        wb.SaveAs(file_path, FileFormat=52)
    
    # 关闭工作簿
    wb.Close(SaveChanges=True)

# 退出Excel应用
excel.Quit()

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