Python合并多个.xlsm指定区域数据问题求助

需要将桌面文件夹中约20个.xlsm文件的AF23:AQ1500区域数据合并到主文件ALL.xlsm中，要求保持原格式、工作表名称及单元格位置，仅使用Python实现（如pandas、xlwings），禁止使用Excel VBA或Power Query。

当前编写的代码运行后，第二个文件的数据可正常复制，但第一个文件的数据未全部复制，寻求解决方案。

import pandas as pd
import openpyxl

# 文件路径
file1 = r'C:\Users\hruby\Desktop\XLSM_merge\CEN_Hrdlicka.xlsm'
file2 = r'C:\Users\hruby\Desktop\XLSM_merge\CEN_Hruby.xlsm'
output_file = r'C:\Users\hruby\Desktop\XLSM_merge\ALL_V2.xlsm'

# 读取两个文件指定区域的数据
df1 = pd.read_excel(file1, usecols="AF:AQ", skiprows=22, nrows=1478)  # 对应AF23:AQ1500
df2 = pd.read_excel(file2, usecols="AF:AQ", skiprows=22, nrows=1478)  # 对应AF23:AQ1500

# 打开文件并保留VBA
wb = openpyxl.load_workbook(file1, keep_vba=True)

# 选择活动工作表
sheet = wb.active

# 写入第一个文件的数据
for i, row in enumerate(df1.values, start=23):  # 从第23行开始
    for j, value in enumerate(row, start=32):  # AF是第32列
        sheet.cell(row=i, column=j, value=value)

# 写入第二个文件的数据
for i, row in enumerate(df2.values):
    row_index = i + 23  # 对应Excel第23行
    for j, value in enumerate(row, start=32):
        if value is not None:  # 非空值才写入
            sheet.cell(row=row_index, column=j, value=value)

# 保存结果
wb.save(output_file)
print(f"变更已成功保存到文件 '{output_file}'。")

1. 数据源覆盖问题：代码直接以file1作为主文件模板，写入df1数据时，本质是将file1原区域的数据重新写入一遍，但pandas读取空值时会转为NaN，写入时可能覆盖原文件的格式或空单元格的原有属性。
2. 空值处理逻辑不一致：第一个循环未判断空值，第二个循环仅写入非空值，导致第一个文件的空值会覆盖主文件（即file1）的原有数据，而非空值可能因pandas读取丢失格式。
3. 批量处理缺失：当前代码仅支持2个文件，无法应对20个文件的需求。
4. 格式保留不足：openpyxl对.xlsm的格式（如单元格样式、公式）支持有限，无法完整保留原文件格式。

xlwings直接调用Excel实例，能完美保留格式、VBA及单元格属性，且更适合批量合并场景：

import xlwings as xw
import os

# 配置参数
folder_path = r'C:\Users\hruby\Desktop\XLSM_merge'
master_file = os.path.join(folder_path, 'ALL.xlsm')
target_range = 'AF23:AQ1500'
sheet_name = None  # 若指定工作表名称，可改为具体名称如'销售数据'

# 初始化Excel应用，后台运行
with xw.App(visible=False, add_book=False) as app:
    # 打开主文件，若不存在则新建（需确保模板格式正确）
    if os.path.exists(master_file):
        master_wb = app.books.open(master_file)
    else:
        master_wb = app.books.add()
        master_wb.save(master_file)
    
    # 获取目标工作表（默认活动表，若指定sheet_name则用master_wb.sheets[sheet_name]）
    master_sheet = master_wb.sheets.active if sheet_name is None else master_wb.sheets[sheet_name]
    
    # 遍历文件夹中所有xlsm文件，排除主文件
    for filename in os.listdir(folder_path):
        if filename.endswith('.xlsm') and filename != os.path.basename(master_file):
            file_path = os.path.join(folder_path, filename)
            # 打开源文件
            source_wb = app.books.open(file_path)
            source_sheet = source_wb.sheets.active if sheet_name is None else source_wb.sheets[sheet_name]
            
            # 获取源区域的数据和格式，仅复制非空单元格
            source_range = source_sheet.range(target_range)
            for cell in source_range:
                if cell.value is not None:
                    # 复制值和格式到主文件对应位置
                    master_sheet.range(cell.address).value = cell.value
                    master_sheet.range(cell.address).api.Copy(master_sheet.range(cell.address).api)
            
            # 关闭源文件，不保存变更
            source_wb.close()
    
    # 保存并关闭主文件
    master_wb.save()
    master_wb.close()

print("所有文件数据合并完成！")

关键改进点 #

• 格式完整保留：xlwings通过Excel原生API复制单元格，完美保留原格式、公式及单元格属性。
• 批量处理支持：自动遍历文件夹中所有.xlsm文件，无需手动添加文件路径。
• 精准合并逻辑：仅复制非空单元格，避免覆盖主文件已有数据。
• 空值处理正确：跳过空单元格，确保各文件的非重叠数据都能合并到主文件。

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