Python读取内含XML数据的xls文件并转为Pandas DataFrame方法

你所持有的后缀为.xls的文件并非传统二进制格式Excel文件，实际为微软Excel 2003 支持的Spreadsheet XML格式（SpreadsheetML），因此调用pandas.read_excel时无论指定openpyxl还是默认引擎，都会因为尝试用二进制格式解析纯XML内容报错。
文件头部XML结构如下：

<?xml version="1.0" encoding="UTF-8"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40">
<Styles>
<Style ss:ID="sDT"><NumberFormat ss:Format="Short Date"/></Style>
</Styles>
<Worksheet ss:Name="XXX">
    <Table>
        <Row>
            <Cell><Data ss:Type="String">Request ID</Data></Cell>
            <Cell><Data ss:Type="String">Date</Data></Cell>
            <Cell><Data ss:Type="String">XXX ID</Data></Cell>
            <Cell><Data ss:Type="String">Customer Name</Data></Cell>
            <Cell><Data ss:Type="String">Amount</Data></Cell>
            <Cell><Data ss:Type="String">Requested Action</Data></Cell>
            <Cell><Data ss:Type="String">Status</Data></Cell>
            <Cell><Data ss:Type="String">Transaction ID</Data></Cell>
            <Cell><Data ss:Type="String">Merchant UTR</Data></Cell>
        </Row>

方法1：pandas.read_xml 搭配lxml解析（推荐，兼容性最好） #

由于XML声明了默认命名空间，直接调用pandas.read_xml不指定XPath和命名空间无法定位到数据节点，按以下步骤操作：

• 先明确命名空间规则：根节点声明的默认命名空间为urn:schemas-microsoft-com:office:spreadsheet，所有表格节点都属于该命名空间
• 定位到目标工作表的行节点，逐行提取单元格值，自动处理空单元格避免列错位
  完整可运行代码：

import pandas as pd
from lxml import etree

# 按需修改配置
FILE_PATH = "你的文件路径.xls"
TARGET_SHEET_NAME = "XXX" # 和XML中Worksheet节点的ss:Name属性保持一致
NS = {"ss": "urn:schemas-microsoft-com:office:spreadsheet"}

# 加载XML内容
with open(FILE_PATH, "r", encoding="utf-8") as f:
    root = etree.parse(f).getroot()

# 定位到目标工作表的表格节点
table_node = root.xpath(
    f".//ss:Worksheet[@ss:Name='{TARGET_SHEET_NAME}']/ss:Table",
    namespaces=NS
)[0]

parsed_rows = []
for row in table_node.xpath(".//ss:Row", namespaces=NS):
    cell_nodes = row.xpath(".//ss:Cell", namespaces=NS)
    current_row = []
    last_col_idx = 0
    for cell in cell_nodes:
        # 补全被省略的空单元格
        col_idx = int(cell.get(f"{{{NS['ss']}}}Index", last_col_idx + 1))
        current_row.extend([""] * (col_idx - last_col_idx - 1))
        # 提取单元格值
        data_node = cell.xpath(".//ss:Data", namespaces=NS)
        cell_val = data_node[0].text if data_node else ""
        current_row.append(cell_val)
        last_col_idx = col_idx
    parsed_rows.append(current_row)

# 组装为DataFrame，第一行为表头
df = pd.DataFrame(parsed_rows[1:], columns=parsed_rows[0])

方法2：格式转换后读取（操作最简单） #

该XML格式是Excel原生支持的格式，无需写代码即可转换：

• 直接用桌面版Excel打开这个.xls文件，Excel会自动识别XML格式并正常加载内容
• 点击「另存为」，选择保存类型为.xlsx标准Excel格式
• 后续直接调用pd.read_excel("另存后的文件路径.xlsx")即可正常读取

方法3：xmltodict 快速解析 #

适合表格结构简单、无空单元格的场景，代码更简洁：

import pandas as pd
import xmltodict

with open("你的文件路径.xls", "r", encoding="utf-8") as f:
    xml_dict = xmltodict.parse(f.read())

# 定位到目标工作表，存在多个工作表时遍历Worksheet节点匹配ss:Name即可
table = xml_dict["Workbook"]["Worksheet"]["Table"]
parsed_rows = []
for row in table["Row"]:
    current_row = []
    for cell in row["Cell"]:
        data = cell.get("Data", {})
        current_row.append(data.get("#text", "") if isinstance(data, dict) else "")
    parsed_rows.append(current_row)

df = pd.DataFrame(parsed_rows[1:], columns=parsed_rows[0])

注意：该方法未处理带ss:Index属性的空单元格场景，如果表格存在空值可能出现列错位，复杂表格优先使用方法1。

后续处理提示 #

读取完成后所有列默认是字符串类型，可按需转换字段类型：

• 金额、数值类列用pd.to_numeric()转为数值类型
• 日期类列用pd.to_datetime()转为时间类型

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