如何用Python导入结构可变的XLS DataFrame并统一格式？

核心思路 #

针对这类规则固定但格式混乱的Excel文件，我们可以编写一个单文件处理函数，批量遍历所有目标文件完成统一处理后，再合并成单一数据集。以下是基于pandas和openpyxl的实现代码（openpyxl用于解决合并单元格读取为空的问题）。

步骤1：安装依赖 #

先确保安装必要的库：

pip install pandas openpyxl

步骤2：编写单文件处理函数 #

import pandas as pd
from openpyxl import load_workbook

def process_single_excel(file_path):
    # 1. 加载工作簿并填充合并单元格值
    wb = load_workbook(file_path, data_only=True)
    ws = wb.active
    
    # 把合并单元格的左上角值填充到整个合并区域，避免读取后出现空值
    merged_cells = list(ws.merged_cells.ranges)
    for merged_range in merged_cells:
        top_left_val = ws[merged_range.min_row][merged_range.min_col-1].value
        for row in ws.iter_rows(min_row=merged_range.min_row, max_row=merged_range.max_row,
                               min_col=merged_range.min_col, max_col=merged_range.max_col):
            for cell in row:
                cell.value = top_left_val
    
    # 2. 获取列名（第7行对应索引6，Python从0计数）
    header_row_idx = 6
    headers = [cell.value for cell in ws[header_row_idx+1]]  # openpyxl行号从1开始
    
    # 3. 定位最后一个含"CD"的列
    cd_col_indices = [i for i, header in enumerate(headers) if header and "CD" in str(header)]
    if not cd_col_indices:
        raise ValueError(f"文件{file_path}未找到含'CD'的列")
    last_cd_col_idx = max(cd_col_indices)
    
    # 4. 读取数据：从第9行（索引8）开始，到A列出现"TOTAL"的前一行
    df = pd.DataFrame(ws.values)
    df = df.iloc[8:, :last_cd_col_idx+1]  # 截取目标行和列范围
    df.columns = headers[:last_cd_col_idx+1]  # 设置列名
    
    # 截断到A列出现"TOTAL"的前一行
    total_row_mask = df.iloc[:, 0] == "TOTAL"
    if total_row_mask.any():
        total_row_idx = df[total_row_mask].index[0]
        df = df.loc[:total_row_idx-1, :]
    
    # 5. 清理列：删除空白列和含"TOTAL"的列
    df = df.dropna(axis=1, how='all')  # 删除全空列
    df = df.drop(columns=[col for col in df.columns if col and "TOTAL" in str(col)])
    
    # 重置索引
    df = df.reset_index(drop=True)
    
    return df

步骤3：批量处理并合并文件 #

import os

# 替换为你的Excel文件所在文件夹路径
folder_path = "/path/to/your/excel/files"
list_dataframes = []

# 遍历文件夹中所有xlsx文件
for filename in os.listdir(folder_path):
    if filename.endswith(".xlsx"):
        file_path = os.path.join(folder_path, filename)
        try:
            df = process_single_excel(file_path)
            list_dataframes.append(df)
            print(f"成功处理：{filename}")
        except Exception as e:
            print(f"处理{filename}失败：{str(e)}")

# 合并所有数据集
final_df = pd.concat(list_dataframes, axis=0, ignore_index=True)

# 可选：保存合并后的结果
final_df.to_excel("/path/to/save/final_dataset.xlsx", index=False)

关键细节说明 #

• 合并单元格处理：通过openpyxl遍历所有合并区域，将左上角单元格的值填充到整个合并范围，避免pandas读取后合并区域出现空值。
• 列范围控制：通过遍历列名定位最后一个含"CD"的列，确保只读取到目标列位置。
• 行范围控制：判断第一列的值是否为"TOTAL"，自动截断数据到该行之前。
• 列清理：先删除全空列，再移除名称含"TOTAL"的列，保证输出数据整洁。

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