使用R（优先dplyr）自动化处理带多级表头的多Excel文件

核心思路 #

• 封装可复用函数，彻底避免重复代码
• 动态适配不同列数的工作表，无需提前指定列数
• 智能合并多行列表头，自动处理空白表头字段

具体实现代码 #

import pandas as pd
import os

def process_single_sheet(sheet_df):
    # 提取前3行作为表头行
    header_rows = sheet_df.iloc[:3].values.tolist()
    merged_headers = []
    
    # 逐列合并表头，处理空白字段
    for col in zip(*header_rows):
        # 过滤空值与无效文本，保留有效表头部分
        valid_parts = [str(part).strip() for part in col if str(part).strip() not in ('nan', '')]
        # 全空列用默认命名避免重复
        if not valid_parts:
            merged_header = f'未命名列_{len(merged_headers)}'
        else:
            merged_header = '/'.join(valid_parts)
        merged_headers.append(merged_header)
    
    # 提取第4行及以后的数据，设置新表头
    processed_df = sheet_df.iloc[3:].copy()
    processed_df.columns = merged_headers
    processed_df = processed_df.reset_index(drop=True)
    return processed_df

def process_single_excel(file_path, target_sheets=None):
    xls = pd.ExcelFile(file_path)
    # 处理指定工作表，无指定则处理所有工作表
    sheets_to_process = target_sheets if target_sheets else xls.sheet_names
    sheet_dfs = []
    
    for sheet_name in sheets_to_process:
        sheet_df = pd.read_excel(xls, sheet_name=sheet_name, header=None)
        processed_df = process_single_sheet(sheet_df)
        # 添加溯源字段，方便后续排查
        processed_df['来源工作表'] = sheet_name
        sheet_dfs.append(processed_df)
    
    # 自动对齐不同列的工作表，缺失列补NaN
    combined_df = pd.concat(sheet_dfs, axis=0, ignore_index=True)
    return combined_df

# 批量处理示例
if __name__ == '__main__':
    excel_dir = './your_excel_dir'  # 替换为你的Excel文件目录
    target_sheets = ['销售数据', '库存表']  # 可选：指定需要处理的工作表
    all_results = []
    
    for file_name in os.listdir(excel_dir):
        if file_name.endswith(('.xlsx', '.xls')):
            file_path = os.path.join(excel_dir, file_name)
            file_df = process_single_excel(file_path, target_sheets)
            file_df['来源文件'] = file_name
            all_results.append(file_df)
    
    # 如需合并所有文件结果，取消下方注释
    # final_merged_df = pd.concat(all_results, axis=0, ignore_index=True)
    # final_merged_df.to_excel('./合并总结果.xlsx', index=False)

关键说明 #

• process_single_sheet：专注处理单张表的表头合并与数据提取，自动处理空白表头，生成唯一列名
• process_single_excel：负责读取单个Excel文件的指定工作表，处理后合并为同文件的统一DataFrame
• 批量处理逻辑：遍历目标目录下所有Excel文件，统一执行处理流程，添加溯源字段便于后续数据核对
• 用pd.concat自动适配不同列数的工作表，无需手动处理列对齐问题

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