如何将Excel中的报告版式布局转换为Python dataframe？

以下是可直接落地的自动化处理方案，适配绝大多数带特殊排版的业务报告类Excel文件，可直接扩展为批量处理脚本：

依赖库准备 #

先安装所需的处理依赖：
pip install pandas openpyxl xlrd

核心处理步骤 #

1. 合并单元格自动填充 #

报告类Excel普遍存在跨行/跨列合并单元格，首先需要把合并单元格的取值填充到区域内所有单元格，避免读取时出现空值：

from openpyxl import load_workbook
import pandas as pd
import os

def fill_merged_cells(file_path, sheet_name=0):
    wb = load_workbook(file_path, data_only=True)
    ws = wb.worksheets[sheet_name] if isinstance(sheet_name, int) else wb[sheet_name]
    # 遍历所有合并区域填充值
    for merged_area in ws.merged_cells.ranges:
        fill_value = ws.cell(row=merged_area.min_row, column=merged_area.min_col).value
        for row in range(merged_area.min_row, merged_area.max_row + 1):
            for col in range(merged_area.min_col, merged_area.max_col + 1):
                ws.cell(row=row, column=col, value=fill_value)
    # 转换为原始DataFrame
    return pd.DataFrame(ws.values)

2. 有效数据自动提取 #

跳过报告开头的冗余说明行、自动识别表头位置，提取有效数据段：

def extract_valid_data(raw_df):
    # 自动定位表头：取非空单元格占比最高的行作为表头，可根据你的报告格式调整规则
    header_row = raw_df.notna().sum(axis=1).idxmax()
    # 设置列名
    raw_df.columns = [str(col).strip() for col in raw_df.iloc[header_row].tolist()]
    # 截取表头后的有效数据，重置索引
    valid_df = raw_df.iloc[header_row + 1:].reset_index(drop=True)
    # 过滤全空行
    valid_df = valid_df.dropna(how='all').reset_index(drop=True)
    return valid_df

3. 批量自动化处理脚本 #

遍历指定文件夹下所有Excel文件，批量转换后导出为标准化CSV，也可直接返回DataFrame供后续分析使用：

def batch_convert_report_excel(input_folder, output_folder):
    os.makedirs(output_folder, exist_ok=True)
    for filename in os.listdir(input_folder):
        # 过滤临时文件和非Excel文件
        if filename.startswith('~$') or not filename.endswith(('.xlsx', '.xls')):
            continue
        file_path = os.path.join(input_folder, filename)
        # 处理合并单元格
        raw_df = fill_merged_cells(file_path)
        # 提取有效数据
        clean_df = extract_valid_data(raw_df)
        # 导出为标准化文件
        output_name = f"{os.path.splitext(filename)[0]}.csv"
        clean_df.to_csv(os.path.join(output_folder, output_name), index=False, encoding='utf-8-sig')

优化建议 #

• 如果你的报告格式固定，比如表头固定在第4行、前2行固定为说明文字，可以直接硬编码表头位置，不需要自动识别，处理速度可提升30%以上
• 若存在多行嵌套表头，可将多行表头内容拼接为复合列名，比如raw_df.iloc[2].fillna('') + '_' + raw_df.iloc[3].fillna('')
• 若单页Excel包含多个独立数据区块，可根据空行或者固定关键词（比如明细、汇总）分割为多个DataFrame分别处理

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