如何使用Python合并Excel所有工作表并追加至输出文件

需要实现指定路径下Excel文件的批量处理逻辑：

• 先对单个源Excel文件做内部合并：把该文件下所有工作表的内容合并为1个工作表
• 再把每个源文件合并完成的单工作表，逐一追加到新建的输出Excel文件中

现有代码的问题是：会直接把所有源文件的所有工作表全量追加到输出文件，没有完成「单文件内部先合并所有工作表」的前置步骤。
现有代码如下：

import glob
import os
import pandas as pd
import sys
import os.path
from openpyxl import load_workbook
from openpyxl import Workbook

#System Arguments
folder = sys.argv[1]
inputFile = sys.argv[2]
outputFile = sys.argv[3]

# specifying the path to xlsx files
path = r""+folder+""

#Create the new Excel Workbook with nameing convention provided by user
def create_file():
    wb = Workbook()
    wb.save(outputFile)

#Append the NEW or EXISTING Workbook with Input Files and Tabs to the already existing Excel File
def appened_file():
    outputPath = outputFile
    book = load_workbook(outputPath)
    writer = pd.ExcelWriter(outputPath, engine = 'openpyxl', mode="a", if_sheet_exists="new")
    writer.book = book
    for filename in glob.glob(path + "*" + inputFile + "*"):
            print(filename)
            excel_file = pd.ExcelFile(filename)
            (_, f_name) = os.path.split(filename)
            (f_short_name, _) = os.path.splitext(f_name)
            for sheet_name in excel_file.sheet_names:
                df_excel = pd.read_excel(filename, sheet_name=sheet_name,engine='openpyxl')
                df_newSheets = pd.DataFrame(df_excel)
                df_newSheets.to_excel(writer, sheet_name, index=False)                   
    writer.save()

核心调整逻辑：把原来遍历每个文件时直接逐表写入输出的逻辑，改成在当前文件的循环内先收集所有工作表的数据、合并成一个DataFrame，再把这个合并后的DataFrame写入输出文件即可。
调整后的完整可运行代码：

import glob
import os
import pandas as pd
import sys
from openpyxl import load_workbook
from openpyxl import Workbook

# 读取启动参数
folder = sys.argv[1]
inputFile = sys.argv[2]
outputFile = sys.argv[3]
path = os.path.join(folder, "")

# 初始化输出文件
def create_file():
    wb = Workbook()
    # 删除新建文件默认生成的空Sheet
    default_sheet = wb.active
    wb.remove(default_sheet)
    wb.save(outputFile)

def append_file():
    outputPath = outputFile
    book = load_workbook(outputPath)
    writer = pd.ExcelWriter(outputPath, engine='openpyxl', mode="a", if_sheet_exists="replace")
    writer.book = book

    for filename in glob.glob(os.path.join(path, f"*{inputFile}*")):
        # 跳过输出文件本身，避免同路径下循环读取自身
        if os.path.abspath(filename) == os.path.abspath(outputFile):
            continue
        print(f"正在处理文件: {filename}")
        (_, f_name) = os.path.split(filename)
        (f_short_name, _) = os.path.splitext(f_name)
        
        # 存储当前文件下所有工作表的数据
        df_list = []
        excel_file = pd.ExcelFile(filename, engine='openpyxl')
        for sheet_name in excel_file.sheet_names:
            df = pd.read_excel(excel_file, sheet_name=sheet_name)
            # 可选：新增列标记数据来源工作表，方便后续溯源，不需要可直接删除
            df["来源工作表"] = sheet_name
            df_list.append(df)
        
        # 合并当前文件所有工作表数据，忽略原索引重新生成
        merged_df = pd.concat(df_list, ignore_index=True)
        # 合并后的数据写入输出文件，Sheet名使用原文件名
        merged_df.to_excel(writer, sheet_name=f_short_name, index=False)
    
    writer.close()

if __name__ == "__main__":
    create_file()
    append_file()

关键修改说明 #

• 调整单文件内的工作表遍历逻辑：从「直接写入输出文件」改为「先收集到列表，用pd.concat合并为单个DataFrame」，实现单文件内多表先合并的需求
• 新增输出文件自身的跳过判断，避免输出文件存放在目标路径下时被重复读取
• 替换路径拼接写法为os.path.join，适配Windows、macOS、Linux不同系统的路径分隔符
• 初始化输出文件时删除openpyxl默认创建的空工作表，避免输出文件出现无意义的空白Sheet
• 可选增加「来源工作表」标记列，不需要溯源可以直接删除对应代码
• 替换原代码中已废弃的writer.save()为规范的writer.close()，适配新版本pandas的API要求
• 调整if_sheet_exists参数为replace，重跑代码时会直接覆盖同名Sheet，不会重复生成带数字后缀的冗余Sheet

注意：如果同一文件下不同工作表的列名、列顺序不一致，pd.concat会自动对齐列名，不存在的列会填充空值，如果需要严格统一列顺序，可以在合并前增加列名校验和重排逻辑。

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