如何加速将多个Excel文件合并为单个Excel文件的过程

需求说明 #

• 将多个.xlsx/.xls文件合并到单个Excel文件中，每个工作表名称对应原文件名（若原文件有多工作表，可扩展为「文件名_工作表名」格式）

现有问题 #

运行提供的Python代码处理少量文件后，程序变得明显缓慢，且内存占用急剧升高

已尝试的无效方案 #

手动关闭Excel文件、删除DataFrame对象、调用gc.collect()回收内存，但问题未得到解决

原代码 #

import pandas as pd
import openpyxl
import os
import gc as gc

print("Copying sheets from multiple files to one file")

dir_input = 'D:/MeusProjetosJava/Importacao/'
dir_output = "Integrados/combined.xlsx"

cwd = os.path.abspath(dir_input)
files = os.listdir(cwd)

df_total = pd.DataFrame()
df_total.to_excel(dir_output) #create a new file
workbook=openpyxl.load_workbook(dir_output)
ss_sheet = workbook['Sheet1']
ss_sheet.title = 'TempExcelSheetForDeleting'
workbook.save(dir_output)


for file in files:                         # loop through Excel files
    if file.endswith('.xls') or file.endswith('.xlsx'):
        excel_file = pd.ExcelFile(cwd+"/"+file)
        sheets = excel_file.sheet_names
        for sheet in sheets:
            sheet_name = str(file.title())
            sheet_name = sheet_name.replace(".xlsx","").lower()
            sheet_name = sheet_name.removesuffix(".xlsx")

            print(file, sheet_name)

            df = excel_file.parse(sheet_name = sheet)
            with pd.ExcelWriter(dir_output,mode='a') as writer:
                df.to_excel(writer, sheet_name=f"{sheet_name}", index=False)
                del df

        excel_file.close()
        del excel_file
        sheets = None
        gc.collect()


workbook=openpyxl.load_workbook(dir_output)
std=workbook["TempExcelSheetForDeleting"]
workbook.remove(std)
workbook.save(dir_output)
print("all done")

问题根源分析 #

1. 重复加载整个工作簿：每次调用pd.ExcelWriter(mode='a')都会把目标Excel文件完整加载到内存，随着工作表增多，内存占用呈指数级上升
2. 冗余操作：临时工作表的创建与删除步骤多余，加重了IO和内存消耗
3. 路径拼接不规范：手动拼接路径易出错，且跨平台兼容性差
4. 内存释放不彻底：虽然手动删除了对象，但重复加载的工作簿资源未被有效回收

优化后的代码（单工作表文件场景） #

import pandas as pd
import openpyxl
import os
import gc

print("合并多个Excel文件到单个工作簿中")

dir_input = 'D:/MeusProjetosJava/Importacao/'
dir_output = "Integrados/combined.xlsx"

# 确保输出目录存在，避免保存失败
os.makedirs(os.path.dirname(dir_output), exist_ok=True)

# 初始化空工作簿，直接删除默认Sheet
workbook = openpyxl.Workbook()
workbook.remove(workbook.active)
workbook.save(dir_output)

cwd = os.path.abspath(dir_input)
files = os.listdir(cwd)

for file in files:
    # 统一处理xls和xlsx后缀，忽略大小写
    if file.lower().endswith(('.xls', '.xlsx')):
        file_path = os.path.join(cwd, file)
        # 直接提取无后缀的文件名作为工作表名，转小写
        sheet_name = os.path.splitext(file)[0].lower()
        
        print(f"正在处理: {file} -> 工作表名: {sheet_name}")
        
        # 读取当前文件的第一个工作表
        df = pd.read_excel(file_path)
        
        # 使用openpyxl引擎追加写入，避免重复加载整个工作簿
        with pd.ExcelWriter(
            dir_output, 
            engine='openpyxl', 
            mode='a', 
            if_sheet_exists='replace'  # 若工作表已存在则覆盖
        ) as writer:
            df.to_excel(writer, sheet_name=sheet_name, index=False)
        
        # 及时释放内存
        del df
        gc.collect()

print("所有文件处理完成")

优化后的代码（多工作表文件场景） #

如果原文件包含多个工作表，可扩展为以下逻辑，将每个工作表单独存入目标文件：

import pandas as pd
import openpyxl
import os
import gc

print("合并多个Excel文件的所有工作表到单个工作簿中")

dir_input = 'D:/MeusProjetosJava/Importacao/'
dir_output = "Integrados/combined.xlsx"

os.makedirs(os.path.dirname(dir_output), exist_ok=True)

workbook = openpyxl.Workbook()
workbook.remove(workbook.active)
workbook.save(dir_output)

cwd = os.path.abspath(dir_input)
files = os.listdir(cwd)

for file in files:
    if file.lower().endswith(('.xls', '.xlsx')):
        file_path = os.path.join(cwd, file)
        file_name = os.path.splitext(file)[0].lower()
        
        print(f"正在处理文件: {file}")
        
        # 获取当前文件的所有工作表
        excel_file = pd.ExcelFile(file_path)
        sheets = excel_file.sheet_names
        
        for idx, sheet in enumerate(sheets):
            # 多工作表时，命名为「文件名_工作表名」，单工作表则直接用文件名
            sheet_name = f"{file_name}_{sheet.lower()}" if len(sheets) > 1 else file_name
            
            print(f"  处理工作表: {sheet} -> {sheet_name}")
            
            df = excel_file.parse(sheet)
            
            with pd.ExcelWriter(
                dir_output, 
                engine='openpyxl', 
                mode='a', 
                if_sheet_exists='replace'
            ) as writer:
                df.to_excel(writer, sheet_name=sheet_name, index=False)
            
            del df
            gc.collect()
        
        del excel_file
        gc.collect()

print("所有文件处理完成")

核心优化点 #

• 减少工作簿重复加载：初始化一次空工作簿，后续所有写入操作基于该工作簿，避免每次追加时重新加载整个文件
• 简化路径与文件名处理：用os.path.join和os.path.splitext替代手动拼接，逻辑更简洁且跨平台兼容
• 内存精细化管理：及时删除DataFrame和ExcelFile对象，配合垃圾回收降低内存占用
• 冗余操作移除：直接初始化无默认工作表的工作簿，省去临时表的创建与删除步骤

内容的提问来源于Stack Exchange，提问作者KenobiBastila