Python Pandas合并多Excel多工作表效率低下问题及优化咨询

我正在开发一个Python项目，需合并多个各含约100个工作表的Excel文件，要求合并后的文件保留所有源工作表的独立标识（例如：excel_1.xlsx含工作表1-100，excel_2.xlsx含101-200，合并后文件需包含1-200的工作表）。

目前使用pandas和ExcelWriter实现的代码效率极低，合并时间随文件数量增加显著增长，耗时近乎呈斐波那契序列递增。

当前代码 #

import pandas as pd
import os
import time

# read excel file
excel_files = []
file_name = 'output_240326_pivot_{i}.xlsx'
for i in range(64):
    file_path = f'./our_data/240403/' + file_name.format(i=i)
    excel_files.append(file_path)

# final merging path
final_excel_path = './our_data/240403/240403_final_for_tgt.xlsx'

# start time count for total merging
start_time = time.time()

with pd.ExcelWriter(final_excel_path) as writer:
    # for each excel file
    for file in excel_files:
        # start time count
        file_start_time = time.time()
    
        # Read every sheets in excel file
        xls = pd.ExcelFile(file)
        for sheet_name in xls.sheet_names:
            df = pd.read_excel(xls, sheet_name)
            # Save each sheet seperately using ExcelWriter
            df.to_excel(writer, sheet_name=sheet_name, index=False)
    
        # Time spent for merging current file
        file_end_time = time.time()
        print(f"Completed merging {os.path.basename(file)} in {file_end_time - file_start_time:.2f} seconds.")

# Total time for merging
end_time = time.time()
print(f"All sheets combined into {final_excel_path} in {end_time - start_time:.2f} seconds.")

时间日志 #

Completed merging output_240326_pivot_0.xlsx in 0.64 seconds.
...
Completed merging output_240326_pivot_18.xlsx in 102.94 seconds.

疑问 #

1. 这种延迟是否因添加更多工作表时ExcelWriter的开销增长导致？
2. 先将每个工作表转为独立CSV再合并是否更高效？
3. 恳请提供优化思路或替代方案。

问题根源分析 #

是的，耗时递增问题确实和pd.ExcelWriter的机制有关。默认情况下，pandas依赖的openpyxl库在每次调用to_excel时，都会重新写入整个工作簿的已有内容，再添加新工作表——工作表越多，每次写入需要处理的数据量就越大，最终导致耗时呈非线性增长，和你观察到的规律完全吻合。

优化方案 #

1. 原生Excel库直接复制工作表（最优解）

跳过pandas的DataFrame转换，用openpyxl直接复制整个工作表，避免数据解析和重构的开销，这是效率最高的方式：

from openpyxl import load_workbook, Workbook
import os
import time

final_excel_path = './our_data/240403/240403_final_for_tgt.xlsx'
file_name = 'output_240326_pivot_{i}.xlsx'
excel_files = [f'./our_data/240403/{file_name.format(i=i)}' for i in range(64)]

# 创建空的目标工作簿
wb_final = Workbook()
wb_final.save(final_excel_path)
wb_final.close()

start_time = time.time()

for file in excel_files:
    file_start = time.time()
    # 以只读模式加载源文件，减少内存占用
    wb_source = load_workbook(file, read_only=True)
    wb_target = load_workbook(final_excel_path)
    
    # 逐个复制工作表到目标工作簿
    for sheet_name in wb_source.sheetnames:
        sheet_source = wb_source[sheet_name]
        sheet_target = wb_target.create_sheet(title=sheet_name)
        # 仅复制数据（需保留样式可去掉values_only=True）
        for row in sheet_source.iter_rows(values_only=True):
            sheet_target.append(row)
    
    wb_target.save(final_excel_path)
    wb_source.close()
    wb_target.close()
    
    print(f"Completed merging {os.path.basename(file)} in {time.time() - file_start:.2f} seconds.")

print(f"All done in {time.time() - start_time:.2f} seconds.")

2. 保留pandas的优化方案：批量缓存后一次性写入

如果需要对数据做预处理必须用pandas，可先将所有工作表的DataFrame缓存到内存，最后一次性写入工作簿，避免多次IO操作：

import pandas as pd
import os
import time

final_excel_path = './our_data/240403/240403_final_for_tgt.xlsx'
file_name = 'output_240326_pivot_{i}.xlsx'
excel_files = [f'./our_data/240403/{file_name.format(i=i)}' for i in range(64)]

# 缓存所有工作表数据到内存
sheet_data = {}
start_time = time.time()

for file in excel_files:
    file_start = time.time()
    xls = pd.ExcelFile(file)
    for sheet_name in xls.sheet_names:
        df = pd.read_excel(xls, sheet_name)
        sheet_data[sheet_name] = df
    print(f"Loaded {os.path.basename(file)} in {time.time() - file_start:.2f} seconds.")

# 一次性写入所有工作表
with pd.ExcelWriter(final_excel_path) as writer:
    for sheet_name, df in sheet_data.items():
        df.to_excel(writer, sheet_name=sheet_name, index=False)

print(f"All sheets written in {time.time() - start_time:.2f} seconds.")

这种方式的耗时会线性增长，而非非线性，因为仅做一次工作簿写入操作。

3. 关于CSV转Excel的方案

不推荐这种方式。CSV本身没有工作表概念，后续需要将每个CSV导入到Excel的不同工作表中，反而会增加额外的IO和转换步骤，效率不如直接操作Excel文件。

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