Python操作XLSM文件生成损坏文件，如何正常保存？

需要实现以下Python功能：

• 对比两个XLSM文件中指定的4个同名工作表，若存在内容差异则替换目标文件对应工作表，无差异则跳过
• 运行目标文件内的宏
• 生成新文件后，将其中两个工作表替换为名为"reconciliation"文件中的对应工作表

目前卡在第一步：运行代码后生成的文件完全损坏无法打开，甚至原文件也可能被损坏。使用openpyxl编写的代码如下：

import openpyxl as xl
import tkinter as tk
from tkinter import filedialog
import os

root = tk.Tk()
root.withdraw()

# open file dialogs to select the excel files
file_path1 = filedialog.askopenfilename(title='Select the first Excel file',
                                        filetypes=[('Excel files', '*.xlsm')])
file_path2 = filedialog.askopenfilename(title='Select the second Excel file',
                                        filetypes=[('Excel files', '*.xlsm')])

wb1 = xl.load_workbook(file_path1, keep_vba=True)
wb2 = xl.load_workbook(file_path2, keep_vba=True)

for sheet_name in wb1.sheetnames:
    sheet1 = wb1[sheet_name]
    sheet2 = wb2[sheet_name]
    # get the number of rows and columns in the worksheet
    max_row = max(sheet1.max_row, sheet2.max_row)
    max_col = max(sheet1.max_column, sheet2.max_column)
    # loop through each row in the worksheet
    for row in range(1, max_row+1):
        # loop through each cell in the row
        for col in range(1, max_col+1):
            # get the values of the corresponding cells in the two worksheets
            cell1 = sheet1.cell(row=row, column=col).value
            cell2 = sheet2.cell(row=row, column=col).value
            if cell1 != cell2:
                # if the values are different, ask the user if they want to replace the sheet
                answer = input(f"Do you want to replace the sheet '{sheet_name}' in the second file with the sheet from the first file? (Y/N) ")
                if answer.lower() == 'y':
                    new_sheet = wb2.create_sheet(title=sheet_name)
                    for row in sheet1.iter_rows(values_only=True):
                        new_sheet.append(row)
                    new_sheet.title = sheet_name
                    del wb2[sheet_name]
                # break out of the loops, since a difference has already been found
                break
        else:
            # continue to the next row
            continue
        # break out of the loops, since we already found a difference
        break

file_name, file_ext = os.path.splitext(file_path1)
new_file_path = file_name + "_updated.xlsm"

new_workbook = xl.Workbook()

for sheet_name in wb1.sheetnames:
    sheet = wb1[sheet_name]
    new_sheet = new_workbook.create_sheet(sheet_name)
    for row in sheet.iter_rows():
        new_row = []
        for cell in row:
            new_row.append(cell.value)
        new_sheet.append(new_row)

new_workbook.save(new_file_path)

# Run the Macro2 in the updated Excel file
# xl = win32.Dispatch("Excel.Application")
# xl.Visible = True
# xl.Workbooks.Open(new_file_path)
# xl.Application.Run("Macro2")
# xl.ActiveWorkbook.Close(SaveChanges=True)
# xl.Quit()

#if input == "y":
#    print("'{sheet_name}' values replaced and new file saved as {}".format(sheet_name, new_file_path))
#else:
#    print("No changes were made")

导致文件损坏的核心问题：

1. 格式不匹配：openpyxl默认创建的Workbook()是XLSX格式，强行保存为XLSM会破坏文件结构
2. 工作表替换逻辑错误：创建新表后直接重命名为已存在的名称，再删除原表，会导致Excel内部引用混乱
3. 丢失关键信息：仅复制单元格值，未保留原文件的VBA代码、单元格格式、公式、图表等XLSM核心内容
4. 原文件风险：直接操作原文件wb2，若程序意外中断会导致原文件损坏

import openpyxl as xl
import tkinter as tk
from tkinter import filedialog
import os
import win32com.client as win32

# 配置参数：指定需要对比的4个工作表名称
TARGET_SHEETS = ["Sheet1", "Sheet2", "Sheet3", "Sheet4"]
# 需要替换为reconciliation文件的工作表名称
RECONCILIATION_SHEETS = ["SheetA", "SheetB"]

root = tk.Tk()
root.withdraw()

# 选择文件
file_path1 = filedialog.askopenfilename(title='选择源XLSM文件', filetypes=[('Excel文件', '*.xlsm')])
file_path2 = filedialog.askopenfilename(title='选择目标XLSM文件', filetypes=[('Excel文件', '*.xlsm')])
reconcile_path = filedialog.askopenfilename(title='选择reconciliation文件', filetypes=[('Excel文件', '*.xlsm')])

# 加载工作簿，保留VBA和公式
wb_source = xl.load_workbook(file_path1, keep_vba=True, data_only=False)
wb_target = xl.load_workbook(file_path2, keep_vba=True, data_only=False)
wb_reconcile = xl.load_workbook(reconcile_path, keep_vba=True, data_only=False)

# 对比并替换指定工作表
for sheet_name in TARGET_SHEETS:
    if sheet_name not in wb_source.sheetnames or sheet_name not in wb_target.sheetnames:
        print(f"警告：工作表{sheet_name}在某个文件中不存在，跳过")
        continue
    
    sheet_source = wb_source[sheet_name]
    sheet_target = wb_target[sheet_name]
    
    # 检查是否存在差异
    has_diff = False
    max_row = max(sheet_source.max_row, sheet_target.max_row)
    max_col = max(sheet_source.max_column, sheet_target.max_column)
    
    for row in range(1, max_row + 1):
        for col in range(1, max_col + 1):
            val_source = sheet_source.cell(row=row, column=col).value
            val_target = sheet_target.cell(row=row, column=col).value
            if val_source != val_target:
                has_diff = True
                break
        if has_diff:
            break
    
    if has_diff:
        answer = input(f"工作表{sheet_name}存在差异，是否替换目标文件中的该工作表？(Y/N) ")
        if answer.lower() == 'y':
            # 正确替换逻辑：先删除原表，再复制源表
            if sheet_name in wb_target.sheetnames:
                del wb_target[sheet_name]
            # 复制源表到目标工作簿
            new_sheet = wb_target.create_sheet(title=sheet_name)
            # 复制所有单元格（包含格式、公式）
            for row in sheet_source.iter_rows():
                for cell in row:
                    new_cell = new_sheet.cell(row=cell.row, column=cell.column, value=cell.value)
                    # 复制单元格样式
                    new_cell.font = cell.font.copy()
                    new_cell.border = cell.border.copy()
                    new_cell.fill = cell.fill.copy()
                    new_cell.alignment = cell.alignment.copy()
                    new_cell.number_format = cell.number_format
            print(f"已替换工作表{sheet_name}")

# 保存修改后的目标文件为新文件
file_name, _ = os.path.splitext(file_path2)
updated_file = f"{file_name}_updated.xlsm"
wb_target.save(updated_file)
print(f"中间文件已保存：{updated_file}")

# 运行宏
excel = win32.Dispatch("Excel.Application")
excel.Visible = False  # 后台运行，无需显示窗口
try:
    wb = excel.Workbooks.Open(updated_file)
    excel.Application.Run("Macro2")  # 替换为你的宏名称
    wb.Save()
    print("宏运行完成")
finally:
    wb.Close()
    excel.Quit()

# 替换为reconciliation中的指定工作表
wb_final = xl.load_workbook(updated_file, keep_vba=True, data_only=False)
for sheet_name in RECONCILIATION_SHEETS:
    if sheet_name not in wb_reconcile.sheetnames or sheet_name not in wb_final.sheetnames:
        print(f"警告：工作表{sheet_name}在某个文件中不存在，跳过")
        continue
    
    # 删除原表
    if sheet_name in wb_final.sheetnames:
        del wb_final[sheet_name]
    # 复制reconciliation中的工作表
    sheet_reconcile = wb_reconcile[sheet_name]
    new_sheet = wb_final.create_sheet(title=sheet_name)
    for row in sheet_reconcile.iter_rows():
        for cell in row:
            new_cell = new_sheet.cell(row=cell.row, column=cell.column, value=cell.value)
            new_cell.font = cell.font.copy()
            new_cell.border = cell.border.copy()
            new_cell.fill = cell.fill.copy()
            new_cell.alignment = cell.alignment.copy()
            new_cell.number_format = cell.number_format
    print(f"已替换为reconciliation中的工作表{sheet_name}")

# 保存最终文件
final_file = f"{file_name}_final.xlsm"
wb_final.save(final_file)
print(f"最终文件已保存：{final_file}")

# 清理资源
wb_source.close()
wb_target.close()
wb_reconcile.close()
wb_final.close()

1. 基于原文件操作：直接加载目标XLSM文件并修改，保留原文件的VBA和所有格式，避免新建工作簿导致的格式不兼容
2. 正确的工作表替换逻辑：先删除目标工作簿中的原表，再创建新表并复制源表内容，避免命名冲突和引用错误
3. 保留完整信息：复制单元格时不仅复制值，还复制字体、边框、填充、对齐方式和数字格式，尽可能保留原表样式与公式
4. 隔离原文件：所有操作都基于加载的内存副本，最后保存为新文件，不会损坏原文件
5. 明确指定工作表：通过TARGET_SHEETS和RECONCILIATION_SHEETS参数，只处理需求中指定的工作表，避免不必要的操作
6. 安全运行宏：使用win32com后台运行Excel，完成后确保关闭工作簿和退出Excel进程，避免残留进程

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