嗨，这个需求我之前帮不少开发者和数据分析师解决过，用Python处理这类Excel批量合并场景，确实能省掉大量手动复制粘贴的功夫，下面给你梳理下经过验证的最佳实践和实用建议：

一、首选工具组合 #

处理表格类数据合并，Pandas是当之无愧的首选——它的DataFrame结构天生适合做数据聚合，搭配openpyxl（支持.xlsx格式）或xlrd==1.2.0（仅支持旧版.xls格式）作为读写引擎，能轻松搞定90%以上的常规场景。

先安装必要依赖：

pip install pandas openpyxl
# 若需处理旧版.xls文件，额外安装指定版本的xlrd
pip install xlrd==1.2.0

二、通用核心实现代码 #

下面是经过多次打磨的通用代码，你可以根据自身需求修改配置参数直接使用：

import pandas as pd
import os

# ---------------------- 按需修改配置 ----------------------
source_dir = "./excel_source"  # 你的源Excel文件所在目录
target_excel = "合并后的总表.xlsx"  # 最终生成的合并文件
target_sheet = "全部合并数据"  # 目标文件的工作表名称
# 指定需要合并的工作表名（所有文件统一要提取的表）
sheets_to_merge = ["销售数据", "月度报表"]
# ---------------------------------------------------------

all_data_frames = []

# 遍历目录下所有Excel文件
for filename in os.listdir(source_dir):
    # 仅处理Excel格式文件
    if not filename.endswith((".xlsx", ".xls")):
        continue
    
    file_path = os.path.join(source_dir, filename)
    print(f"正在处理文件: {filename}")
    
    # 逐个读取指定工作表
    for sheet_name in sheets_to_merge:
        try:
            # 读取工作表数据
            df = pd.read_excel(file_path, sheet_name=sheet_name)
            # 新增溯源列，方便后续定位数据来源
            df["来源文件"] = filename
            df["来源工作表"] = sheet_name
            all_data_frames.append(df)
        except Exception as e:
            print(f"⚠️ 跳过 {filename} 的 {sheet_name}：{str(e)}")

# 合并数据并写入目标文件
if all_data_frames:
    combined_df = pd.concat(all_data_frames, ignore_index=True)
    # 用openpyxl引擎写入，支持.xlsx格式
    with pd.ExcelWriter(target_excel, engine="openpyxl") as writer:
        combined_df.to_excel(writer, sheet_name=target_sheet, index=False)
    print(f"✅ 合并完成！结果已保存至: {target_excel}")
else:
    print("❌ 未找到任何符合条件的工作表数据，合并终止")

代码里的关键细节： #

• 溯源列设计：新增的来源文件和来源工作表是非常实用的设计，后续如果发现数据异常，能快速定位到原始文件。
• 异常容错：避免因某个文件缺少指定工作表导致整个程序崩溃，而是跳过异常项继续处理其他文件。
• 索引重置：ignore_index=True确保合并后的DataFrame索引连续，不会保留原文件的索引混乱。

三、进阶优化与特殊场景处理 #

如果你的需求更复杂，比如要保留格式、处理超大文件，或者不同文件需合并不同工作表，可以参考这些针对性方案：

1. 保留Excel单元格格式/样式 #

如果原始Excel有复杂的格式（比如单元格颜色、字体、公式），Pandas读取时会丢失这些样式。这种场景可以直接用openpyxl操作Excel对象，逐单元格复制内容和样式：

from openpyxl import load_workbook, Workbook

target_wb = Workbook()
# 删除默认创建的空工作表
target_wb.remove(target_wb.active)

for filename in os.listdir(source_dir):
    if not filename.endswith(".xlsx"):
        continue
    file_path = os.path.join(source_dir, filename)
    source_wb = load_workbook(file_path)
    
    for sheet_name in sheets_to_merge:
        if sheet_name not in source_wb.sheetnames:
            print(f"⚠️ 跳过 {filename} 的 {sheet_name}：工作表不存在")
            continue
        # 复制源工作表到目标工作簿
        source_sheet = source_wb[sheet_name]
        target_sheet = target_wb.create_sheet(title=f"{filename}-{sheet_name}")
        # 逐行逐列复制单元格内容与样式
        for row in source_sheet.iter_rows(values_only=False):
            for cell in row:
                target_sheet[cell.coordinate].value = cell.value
                target_sheet[cell.coordinate].style = cell.style

target_wb.save(target_excel)

注意：这种方式适合格式要求极高的场景，但处理速度比Pandas慢，且仅支持.xlsx文件。

2. 处理超大Excel文件 #

如果单个Excel文件超过100MB，直接加载会占用过多内存，可以用chunksize参数分块读取再逐块写入：

with pd.ExcelWriter(target_excel, engine="openpyxl") as writer:
    first_chunk = True
    for filename in os.listdir(source_dir):
        if not filename.endswith(".xlsx"):
            continue
        file_path = os.path.join(source_dir, filename)
        for sheet_name in sheets_to_merge:
            try:
                # 分块读取，每块1000行
                for chunk in pd.read_excel(file_path, sheet_name=sheet_name, chunksize=1000):
                    chunk["来源文件"] = filename
                    chunk["来源工作表"] = sheet_name
                    # 第一块写入时创建表头，后续块仅追加数据
                    chunk.to_excel(writer, sheet_name=target_sheet, index=False, header=first_chunk)
                    first_chunk = False
            except Exception as e:
                print(f"⚠️ 处理 {filename}-{sheet_name} 出错：{str(e)}")

3. 不同文件指定不同工作表 #

如果每个文件要合并的工作表不统一（比如A.xlsx取Sheet1，B.xlsx取Sheet2），可以用配置字典灵活定义：

file_sheet_map = {
    "A.xlsx": ["Sheet1", "Sheet3"],
    "B.xlsx": ["Sheet2"],
    "C.xlsx": ["月度汇总"]
}

for filename, sheets in file_sheet_map.items():
    file_path = os.path.join(source_dir, filename)
    if not os.path.exists(file_path):
        print(f"⚠️ 跳过 {filename}：文件不存在")
        continue
    # 后续逻辑和通用版一致，遍历当前文件的指定工作表
    for sheet_name in sheets:
        try:
            df = pd.read_excel(file_path, sheet_name=sheet_name)
            df["来源文件"] = filename
            df["来源工作表"] = sheet_name
            all_data_frames.append(df)
        except Exception as e:
            print(f"⚠️ 跳过 {filename} 的 {sheet_name}：{str(e)}")

四、额外实用建议 #

• 先小范围测试：先拿1-2个文件验证代码逻辑，确认合并结果符合预期后，再批量处理所有文件，避免出错后返工。
• 备份源文件：处理前一定要备份所有原始Excel文件，虽然Python操作一般不会修改源文件，但不怕一万就怕万一。
• 日志记录：如果处理几十上百个文件，建议用logging模块把过程写入日志文件，方便后续排查：

  import logging
  logging.basicConfig(filename="合并操作日志.log", level=logging.INFO, format="%(asctime)s - %(message)s")
  # 替换print为logging.info
  logging.info(f"正在处理文件: {filename}")
  

• 统一数据类型：合并后可能出现某列数据类型不一致的情况（比如一个文件是数值，另一个是文本），可以用以下代码统一转换：

  combined_df["销售额"] = pd.to_numeric(combined_df["销售额"], errors="coerce")
  combined_df["日期"] = pd.to_datetime(combined_df["日期"], errors="coerce")
  

如果还有其他特殊场景，比如要合并到目标文件的不同工作表、处理透视表关联等，随时补充细节，我再给你调整方案！