从结构不一致的多个.xlsx文件中提取指定表格数据

核心思路 #

先读取整个工作表的原始数据，定位所有包含目标表头的行，取最后一行作为表格起始点，再通过识别连续空行确定表格结束位置，最终转换为标准DataFrame。

实现代码（Pandas） #

import pandas as pd

def extract_last_target_table(file_path, sheet_name=0, target_headers=['Well', 'Content']):
    # 读取全量原始数据，不自动识别表头
    df_raw = pd.read_excel(file_path, sheet_name=sheet_name, header=None)
    
    header_rows = []
    # 遍历每一行，筛选包含所有目标表头的行（忽略大小写、前后空格）
    for idx, row in df_raw.iterrows():
        processed_vals = [str(val).strip().lower() for val in row.dropna().tolist()]
        target_headers_lower = [h.strip().lower() for h in target_headers]
        if all(h in processed_vals for h in target_headers_lower):
            header_rows.append(idx)
    
    if not header_rows:
        raise ValueError("未找到包含指定表头的表格")
    
    # 取最后一个表头行作为表格起始点
    start_idx = header_rows[-1]
    table_segment = df_raw.iloc[start_idx:]
    
    # 定位表格结束位置：第一个全空行的前一行
    end_pos = len(table_segment)
    for idx, row in table_segment.iterrows():
        if row.isna().all():
            end_pos = idx - start_idx
            break
    
    # 提取有效表格数据并格式化
    target_df = table_segment.iloc[:end_pos]
    target_df.columns = target_df.iloc[0].str.strip()  # 设置表头并清理空格
    target_df = target_df[1:].reset_index(drop=True)  # 移除表头行
    target_df = target_df.dropna(how='all').dropna(axis=1, how='all')  # 清理空行空列
    
    return target_df

# 调用示例
if __name__ == "__main__":
    try:
        result = extract_last_target_table("your_file.xlsx")
        print(result.head())
    except ValueError as e:
        print(e)

处理合并单元格的补充方案 #

如果Excel存在合并单元格，会导致表头识别失败，可先用openpyxl展开合并单元格：

from openpyxl import load_workbook

def unmerge_cells(file_path, sheet_name=0):
    wb = load_workbook(file_path)
    ws = wb[sheet_name] if isinstance(sheet_name, str) else wb.worksheets[sheet_name]
    
    # 遍历所有合并单元格，填充值到每个单元格后取消合并
    merged_ranges = list(ws.merged_cells.ranges)
    for rng in merged_ranges:
        top_left_val = ws[rng.min_row][rng.min_col].value
        ws.unmerge_cells(str(rng))
        for row in ws.iter_rows(min_row=rng.min_row, max_row=rng.max_row,
                               min_col=rng.min_col, max_col=rng.max_col):
            for cell in row:
                cell.value = top_left_val
    
    # 保存处理后的文件
    processed_file = f"processed_{file_path.split('/')[-1]}"
    wb.save(processed_file)
    return processed_file

# 先处理合并单元格，再提取表格
processed_file = unmerge_cells("your_file.xlsx")
result_df = extract_last_target_table(processed_file)

关键说明 #

• 表头匹配忽略大小写和前后空格，适配不同文件的格式差异
• 通过记录所有表头行并取最后一个，精准定位目标表格
• 用连续空行判断表格结束，兼容可变尺寸的表格
• 最终自动清理空行空列，得到可直接用于后续处理的DataFrame

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