如何用Python高效合并大量大型Excel文件？优化方案问询

需要合并数百个大型Excel文件（每个文件含数百列、数千行数据）到一个文件中。目前用openpyxl实现，但逐单元格复制的方式耗时极长（部分场景超过30分钟）。想知道有没有支持批量复制单元格区域的Python库，或者如何优化现有openpyxl代码。当前代码如下：

from datetime import date, datetime
from openpyxl import load_workbook
from openpyxl import Workbook
from pathlib import Path
from shutil import rmtree
import argparse
import os


# 解析命令行参数
parser = argparse.ArgumentParser( description = 'Merge excel (xls, xlsx files)')
parser.add_argument('main_file',help='Main excel file full path with file name', nargs='?',  default='./files/1.xlsx')
parser.add_argument('target_dir', help='Directory containing excel files to be merged with the main file', nargs='?', default='./files')
args = parser.parse_args()

# 主文件，所有其他xls/xlsx文件将合并到这里
main_file = Path(args.main_file)
target_dir = Path(args.target_dir)


wb_main = load_workbook(main_file)
ws_main = wb_main.active

# 加载目标目录下的所有xls/xlsx文件并合并到主文件
for file in target_dir.iterdir():

    if file.suffix == '.xls' or file.suffix == '.xlsx':
        if file.name != main_file.name:
            
             wb = load_workbook(file)
             ws = wb.active

             current_row = ws_main.max_row
             current_col = 0

             count = 0

             for row in ws.values:
                count += 1

                if current_row < ws_main.max_row + ws.max_row:
                    current_col = 0
                    current_row += 1
    
                for value in row:
                    if current_col < ws.max_column:
                        current_col += 1
                    ws_main.cell (current_row, current_col).value = value

# 将最终数据保存到主文件
wb_main.save(main_file)

一、优化openpyxl的实现方式 #

原代码逐单元格赋值的效率极低，openpyxl本身支持批量写入整行数据，完全没必要逐个操作单元格。通过一次性读取源文件的所有数据，再批量写入主文件，能大幅减少IO操作次数，提升速度。

优化后的代码：

from datetime import date, datetime
from openpyxl import load_workbook
from pathlib import Path
import argparse
import os

# 解析命令行参数
parser = argparse.ArgumentParser(description='Merge excel (xls, xlsx files)')
parser.add_argument('main_file', help='Main excel file full path with file name', nargs='?', default='./files/1.xlsx')
parser.add_argument('target_dir', help='Directory containing excel files to be merged with the main file', nargs='?', default='./files')
args = parser.parse_args()

main_file = Path(args.main_file)
target_dir = Path(args.target_dir)

wb_main = load_workbook(main_file)
ws_main = wb_main.active

for file in target_dir.iterdir():
    # 跳过主文件，只处理xls/xlsx格式
    if file.suffix in ('.xlsx', '.xls') and file.name != main_file.name:
        # openpyxl仅支持xlsx格式，xls文件需要额外处理
        if file.suffix == '.xlsx':
            # 用只读模式加载源文件，速度更快、内存占用更低
            wb = load_workbook(file, read_only=True)
            ws = wb.active
            # 一次性读取所有行数据
            all_rows = list(ws.values)
            # 批量写入主工作表，append方法直接添加整行
            for row in all_rows:
                ws_main.append(row)
            # 及时关闭工作簿释放资源
            wb.close()

wb_main.save(main_file)

关键优化点：

• 用read_only=True加载源文件，避免加载不必要的格式信息，读取速度提升明显
• 一次性读取所有行数据list(ws.values)，减少循环次数
• 用ws_main.append(row)批量写入整行，替代逐单元格赋值，大幅降低工作表操作开销
• 注意：openpyxl不支持读取.xls格式，这类文件可以先转成.xlsx，或者用xlrd库读取后再处理

二、用pandas实现高效合并 #

如果不需要保留Excel的格式（比如单元格样式、公式），pandas是更优选择——它的向量化操作处理大型表格数据的速度比openpyxl快几十倍，代码也更简洁。

代码示例：

import pandas as pd
from pathlib import Path
import argparse

# 解析命令行参数
parser = argparse.ArgumentParser(description='Merge excel (xls, xlsx files)')
parser.add_argument('main_file', help='Main excel file full path with file name', nargs='?', default='./files/1.xlsx')
parser.add_argument('target_dir', help='Directory containing excel files to be merged with the main file', nargs='?', default='./files')
args = parser.parse_args()

main_file = Path(args.main_file)
target_dir = Path(args.target_dir)

# 读取主文件数据
df_main = pd.read_excel(main_file)

for file in target_dir.iterdir():
    if file.suffix in ('.xlsx', '.xls') and file.name != main_file.name:
        # 读取当前文件，header=0表示第一行是表头，无表头则设为None
        df = pd.read_excel(file, header=0)
        # 合并数据，ignore_index重置索引避免重复
        df_main = pd.concat([df_main, df], ignore_index=True)

# 保存合并结果，index=False不写入索引列
df_main.to_excel(main_file, index=False)

优势：

• 速度快：内部用向量化处理，比逐单元格操作效率高几个量级
• 自动对齐列：不同文件的列顺序不一致也能自动匹配
• 支持xls和xlsx格式（需要提前安装xlrd和openpyxl依赖）
• 代码简洁，逻辑清晰，出错概率低

注意：如果需要保留Excel的格式（比如单元格颜色、公式），还是得用优化后的openpyxl方案，pandas只保留数据内容。

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