批量合并Excel文件

使用说明:

• 安装: pip install openpyxl pandas
• MAX_ROWS_PER_FILE: 一个Excel文件中最大行
• MAX_ROWS_PER_SHEET: 一个Sheet最大行 Excel specifications and limits - Microsoft Support
• dir: 修改为原始Excel目录
• pd.ExcelWriter: 为Excel合并之后存储目录

  
import os, time  
import pandas as pd  
  
def run():  
    # 设置每个工作表的最大行数和每个文件的最大行数  
    MAX_ROWS_PER_SHEET = 100000  # 每个工作表的最大行数  
    MAX_ROWS_PER_FILE = 1000000  # 每个文件的最大行数  
  
    start_time = time.time()  
    dir = r'/Users/yizhuobai/Downloads/excel'  # 设置工作路径  
  
    # 新建列表，存放每个文件数据框  
    DFs = []  
    file_num = 0  
    # 读取所有 Excel 文件并合并  
    for root, dirs, files in os.walk(dir):  # 第一个为起始路径，第二个为起始路径下的文件夹，第三个是起始路径下的文件。  
        files.sort()  # 按照文件名的字母顺序排序  
        for file in files:  
            if file.endswith('.xlsx'):  
                file_num += 1  
                file_path = os.path.join(root, file)  # 将路径名和文件名组合成一个完整路径  
                df = pd.read_excel(file_path, engine='openpyxl')  # excel转换成DataFrame  
                DFs.append(df)  
  
    # 合并所有数据  
    alldata = pd.concat(DFs)  # sort='False'  
    # 转换 "发送时间" 列为日期时间数据类型  
    alldata['发送时间'] = pd.to_datetime(alldata['发送时间'])  
    print('文件读取完成,读取{} 开始排序'.format(file_num))  
  
    # 按照 "发送时间" 列进行排序  
    alldata = alldata.sort_values(by='发送时间')  
    print('文件读取完成,读取{}'.format(file_num))  
    # 初始化变量  
    current_file_index = 1  
    current_sheet_index = 1  
    current_row_count = 0  
    current_file_row_count = 0  
  
    # 创建第一个Excel文件  
    writer = pd.ExcelWriter(f'/Users/yizhuobai/Downloads/merge_{current_file_index}.xlsx', engine='openpyxl')  
  
    # 逐行写入数据  
    for i in range(len(alldata)):  
        # 如果当前文件的行数达到最大行数，保存当前文件并切换到下一个文件  
        if current_file_row_count >= MAX_ROWS_PER_FILE:  
            writer.close()  # 保存并关闭当前文件  
            print('文件已满{}'.format(current_file_index))  
            current_file_index += 1  # 切换到下一个文件  
            writer = pd.ExcelWriter(f'/Users/yizhuobai/Downloads/merge_{current_file_index}.xlsx',  
                                    engine='openpyxl')  
            current_sheet_index = 1  # 重置工作表索引  
            current_row_count = 0  
            current_file_row_count = 0  # 重置文件行计数  
  
        # 如果当前工作表的行数达到最大行数，保存当前工作表并切换到下一个工作表  
        if current_row_count >= MAX_ROWS_PER_SHEET:  
            print('文件 {} sheet 已满{}'.format(current_file_index, current_sheet_index))  
            current_sheet_index += 1  # 切换到下一个工作表  
            current_row_count = 0  # 重置工作表行计数  
  
        # 如果当前工作表的行数为0，写入表头  
        if current_row_count == 0:  
            print('文件 {} sheet {} 行 {}'.format(current_file_index, current_sheet_index, current_row_count))  
            alldata.iloc[[i]].to_excel(writer, sheet_name=f'Sheet{current_sheet_index}', index=False, header=True)  
        else:  
            print('文件 {} sheet {} 行 {}'.format(current_file_index, current_sheet_index, current_row_count))  
            alldata.iloc[[i]].to_excel(writer, sheet_name=f'Sheet{current_sheet_index}', index=False, header=False,  
                                       startrow=current_row_count)  
  
        current_row_count += 1  
        current_file_row_count += 1  # 增加当前文件的行计数  
  
    writer.close()  
    end_time = time.time()  
    times = round(end_time - start_time, 2)  
    print('合并完成，耗时{}秒'.format(times))  
  
  
if __name__ == '__main__':  
    run()