如何用Python/PySpark解析含合并单元格的XLSX文件？

问题概述 #

需解析一份以合并单元格作为数据表头的XLSX文件，当前纠结两种技术方案：

• 方案1：先转JSON格式，再执行透视/数据转换
• 方案2：直接读取XLSX单元格值，但担忧扩展性与灵活性不足

尝试用xlrd库实现XLSX转JSON时遇到两个问题：

• 未加载全量数据，但无任何报错
• 输出JSON存在空键问题

后续需在Databricks Spark中完成最终数据转换，已准备测试代码、输出样例、输入输出截图及测试数据集。

方案对比与最优选型 #

方案1（XLSX→JSON→Spark处理） #

额外增加JSON转换环节，易引入数据丢失、格式错乱问题（如你遇到的空键/不全量问题），且JSON结构化对合并表头的适配成本高，不推荐。

方案2（Spark直接读取+合并表头处理） #

直接在Spark层处理，依托Spark分布式能力与内置API，扩展性、灵活性完全可控，是最优路径。

xlrd转JSON问题的临时修复 #

若仍需保留xlrd转换环节，核心要解决合并表头映射与全量数据读取：

1. 合并单元格表头映射：通过sheet.merged_cells获取合并区域，将合并值映射到对应列
2. 全量数据读取：遍历所有行（包括隐藏行），校验sheet.nrows返回的总行数，避免过滤逻辑丢数据
3. 空键问题修复：为无值单元格设置默认键名（如列索引/合并表头值）

示例代码：

import xlrd

def parse_merged_header(sheet):
    header_map = {}
    # 处理合并单元格表头
    for (rlow, rhigh, clow, chigh) in sheet.merged_cells:
        header_val = sheet.cell_value(rlow, clow)
        for col in range(clow, chigh):
            header_map[col] = header_val
    # 补充非合并列的表头
    for col in range(sheet.ncols):
        if col not in header_map:
            header_map[col] = sheet.cell_value(0, col)
    return header_map

# 读取文件并转换
workbook = xlrd.open_workbook("your_data.xlsx")
sheet = workbook.sheet_by_index(0)
header_map = parse_merged_header(sheet)

full_data = []
for row_idx in range(1, sheet.nrows):
    row_dict = {}
    for col_idx in range(sheet.ncols):
        key = header_map.get(col_idx, f"col_{col_idx}")
        row_dict[key] = sheet.cell_value(row_idx, col_idx)
    full_data.append(row_dict)

Databricks Spark核心处理步骤 #

直接用Spark Excel库读取文件，配合自定义逻辑处理合并表头：

1. 依赖准备 #

在Databricks集群库管理界面添加依赖：com.crealytics:spark-excel_2.12:0.14.0（版本适配你的Spark版本）

2. 读取原始数据（不自动识别表头） #

// Scala示例，Python语法类似
val raw_df = spark.read
  .format("com.crealytics.spark.excel")
  .option("header", "false") // 手动处理表头
  .option("inferSchema", "false")
  .option("dataAddress", "'Sheet1'!A1:Z1000") // 指定数据范围
  .load("/dbfs/path/to/your/file.xlsx")

3. 解析合并表头并映射列名 #

• 读取第一行表头数据，结合XLSX的合并单元格信息，构建「原始列名→实际表头」的映射关系
• 对于跨列合并的表头，将对应所有原始列映射为同一个表头名称

4. 重命名列并过滤有效数据 #

• 根据映射关系重命名DataFrame的列
• 过滤掉表头行，保留业务数据行

5. 数据转换与透视 #

使用Spark的groupBy、pivot、join等API完成后续的透视、聚合等操作，完全满足灵活转换需求。

关键注意事项 #

• 若XLSX存在隐藏行/列，可添加option("includeHiddenColumns", "true")参数控制读取
• 针对跨多行的合并表头，需先合并多行表头内容，再映射到对应列
• 优先使用Spark原生API处理数据，避免中间格式转换带来的损耗

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