如何将含跳跃列索引的XML表格导入Databricks/Pandas DataFrame

你遇到的核心问题是XML中<Cell>的ss:Index属性未被正确解析，导致数据按Cell出现顺序填充列，而非按指定索引匹配表头列。以下是Pandas和Databricks（PySpark）两种环境下的正确实现方案：

一、Pandas 实现方案 #

使用Python内置的xml.etree.ElementTree解析XML，手动处理命名空间和Cell索引映射：

import xml.etree.ElementTree as ET
import pandas as pd

# 解析XML文件（如果是字符串则用ET.fromstring）
tree = ET.parse("your_file.xml")
root = tree.getroot()

# 处理XML命名空间
ns = {"ss": "urn:schemas-microsoft-com:office:spreadsheet"}

# 提取表头行（第一个<Row>），建立索引到列名的映射
header_row = root.find(".//ss:Worksheet/ss:Table/ss:Row[1]", ns)
col_map = {}
for cell in header_row.findall("ss:Cell", ns):
    idx = int(cell.attrib[f"{{{ns['ss']}}}Index"])
    col_name = cell.find("ss:Data", ns).text
    col_map[idx] = col_name

# 提取所有数据行（从第二个<Row>开始）
data_rows = root.findall(".//ss:Worksheet/ss:Table/ss:Row[position()>1]", ns)
data = []
for row in data_rows:
    row_data = {v: "" for v in col_map.values()}
    for cell in row.findall("ss:Cell", ns):
        idx = int(cell.attrib[f"{{{ns['ss']}}}Index"])
        if idx in col_map:
            col_name = col_map[idx]
            cell_data = cell.find("ss:Data", ns).text
            row_data[col_name] = cell_data
    data.append(row_data)

# 构建DataFrame
df = pd.DataFrame(data)
print(df.to_markdown(index=False))

二、Databricks（PySpark）实现方案 #

使用spark-xml库解析XML，配合自定义逻辑处理索引映射：

1. 先安装依赖 #

在Databricks工作区中，通过库页面安装com.databricks:spark-xml_2.12:0.16.0（版本需适配你的Spark版本）。

2. 实现代码 #

from pyspark.sql import SparkSession
from pyspark.sql.functions import udf, col
from pyspark.sql.types import MapType, StringType

# Databricks中可省略SparkSession初始化，直接使用内置spark对象
spark = SparkSession.builder.appName("XMLImport").getOrCreate()

# 读取XML，提取所有Row数据
xml_df = spark.read.format("xml")\
    .option("rowTag", "Row")\
    .option("namespace", "urn:schemas-microsoft-com:office:spreadsheet")\
    .option("attributePrefix", "ss_")\
    .load("/path/to/your/file.xml")

# 提取表头行，构建索引到列名的映射
header_row = xml_df.filter(col("ss_Index") == "1").collect()[0]
col_map = {}
for cell in header_row.Cell:
    cell_idx = str(cell.ss_Index)
    col_name = cell.Data._VALUE
    col_map[cell_idx] = col_name

# 定义UDF：根据Cell的ss_Index匹配列名，生成完整行的键值对
def process_row(cells):
    row_data = {v: "" for v in col_map.values()}
    for cell in cells:
        cell_idx = str(cell.ss_Index)
        if cell_idx in col_map:
            row_data[col_map[cell_idx]] = cell.Data._VALUE
    return row_data

process_row_udf = udf(process_row, MapType(StringType(), StringType()))

# 处理数据行，转换为结构化DataFrame
result_df = xml_df.filter(col("ss_Index") != "1")\
    .withColumn("row_map", process_row_udf(col("Cell")))\
    .select([col("row_map").getItem(c).alias(c) for c in col_map.values()])

# 展示结果
result_df.show()

问题原因说明 #

你之前的实现没有针对ss:Index属性做映射逻辑，默认按XML中<Cell>的出现顺序依次填充DataFrame的列，导致缺失Cell的位置被后续Cell占据（比如第一行数据的第5个Cell被填充到第3列）。上述方案通过建立索引-列名的映射关系，强制每个Cell数据匹配到对应列，从而实现正确的列对齐。

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