Pandas将xlsx数据写入xlsm文件时出现文件损坏问题求助

核心问题 #

你遇到的xlsm文件损坏，本质是openpyxl在追加模式（mode='a'）下无法正确处理xlsm格式的宏结构：

• xlsm格式比普通xlsx多了宏相关的OLE容器文件（如vbaProject.bin），openpyxl的追加模式不会保留这些关键部件，每次循环打开文件时都会破坏原有文件的结构。
• 循环内重复创建ExcelWriter并频繁打开/关闭文件，多次IO操作进一步加剧了文件结构的损坏。

修正方案 #

方案1：一次性写入所有工作表（推荐） #

不要在循环内重复打开文件，而是一次性加载带宏的模板后完成所有工作表的写入，避免多次IO操作破坏xlsm结构。需提前准备一个带宏的空xlsm模板文件（比如template.xlsm，可手动创建一个带空宏的文件），代码如下：

import pandas as pd
from openpyxl import load_workbook

# 读取输入文件
read_file = pd.read_excel('input_file.xlsx')
read_file['DATE'] = read_file['DATE'].dt.strftime('%d/%m/%y')
names = read_file['Name'].unique().astype(str)

# 加载带宏的xlsm模板，保留宏结构
wb = load_workbook('template.xlsm', keep_vba=True)

# 一次性写入所有工作表
with pd.ExcelWriter('output_file.xlsm', 
                    engine='openpyxl', 
                    mode='a', 
                    if_sheet_exists='replace', 
                    engine_kwargs={'keep_vba': True}) as writer:
    writer.book = wb
    for n in names:
        data = read_file[read_file['Name'] == n]
        data.to_excel(writer, sheet_name=n, index=False, startrow=0, header=True)

方案2：先写入xlsx再转xlsm（无宏需求时用） #

如果不需要保留宏功能，可以先写入普通xlsx文件，再手动通过Excel另存为xlsm格式，或用pandas结合win32com自动转换（仅Windows环境）：

import pandas as pd
import win32com.client as win32

# 先写入临时xlsx文件
read_file = pd.read_excel('input_file.xlsx')
read_file['DATE'] = read_file['DATE'].dt.strftime('%d/%m/%y')
names = read_file['Name'].unique().astype(str)

with pd.ExcelWriter('temp_output.xlsx') as writer:
    for n in names:
        data = read_file[read_file['Name'] == n]
        data.to_excel(writer, sheet_name=n, index=False)

# 自动转换为xlsm格式（Windows环境专属）
excel = win32.gencache.EnsureDispatch('Excel.Application')
wb = excel.Workbooks.Open(r'temp_output.xlsx')
wb.SaveAs(r'output_file.xlsm', FileFormat=52)  # 52是xlsm格式的官方编号
wb.Close()
excel.Quit()

关键注意点 #

• 处理xlsm文件时必须指定keep_vba=True，否则openpyxl会丢弃宏相关部件，直接导致文件损坏。
• 避免在循环内重复打开/关闭ExcelWriter，尽量一次性完成所有写入操作，减少文件结构被破坏的风险。

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