使用xlrd读取xls到pandas DataFrame遇AssertionError求助

问题背景 #

尝试读取西班牙配电运营商发布的一个xls文件：

• Mac系统Numbers提示格式无效，但Windows Excel、Google Sheets可正常打开
• 使用pandas.read_excel+xlrd引擎读取时触发AssertionError，报错指向xlrd/book.py中的unpack_SST_table函数，调试发现问题集中在字符串b'UD638423080002'
• 临时将断言包裹在try...except中可加载DataFrame，但不确定数据是否完整

复现代码 #

import requests
import pandas as pd

url = "https://www.ufd.es/wp-content/uploads/2024/05/publicacion-capacidad-Junio.xls"
headers = {
    "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/109.0.0.0 Safari/537.36",
}
response = requests.get(url, headers=headers)
# 注意：FutureWarning提示需用BytesIO包装字节流
df = pd.read_excel(response.content, header=[0, 1], engine="xlrd")

报错信息 #

./xls_test.py:10: FutureWarning: Passing bytes to 'read_excel' is deprecated and will be removed in a future version. To read from a byte string, wrap it in a `BytesIO` object.
  df = pd.read_excel(response.content, header=[0, 1], engine="xlrd")
Traceback (most recent call last):
  File "./xls_test.py", line 10, in <module>
    df = pd.read_excel(response.content, header=[0, 1], engine="xlrd")
  File "/opt/venv/lib/python3.10/site-packages/pandas/io/excel/_base.py", line 495, in read_excel
    io = ExcelFile(
  File "/opt/venv/lib/python3.10/site-packages/pandas/io/excel/_base.py", line 1567, in __init__
    self._reader = self._engines[engine](
  File "/opt/venv/lib/python3.10/site-packages/pandas/io/excel/_xlrd.py", line 46, in __init__
    super().__init__(
  File "/opt/venv/lib/python3.10/site-packages/pandas/io/excel/_base.py", line 573, in __init__
    self.book = self.load_workbook(self.handles.handle, engine_kwargs)
  File "/opt/venv/lib/python3.10/site-packages/pandas/io/excel/_xlrd.py", line 63, in load_workbook
    return open_workbook(file_contents=data, **engine_kwargs)
  File "/opt/venv/lib/python3.10/site-packages/xlrd/__init__.py", line 172, in open_workbook
    bk = open_workbook_xls(
  File "/opt/venv/lib/python3.10/site-packages/xlrd/book.py", line 104, in open_workbook_xls
    bk.parse_globals()
  File "/opt/venv/lib/python3.10/site-packages/xlrd/book.py", line 1211, in parse_globals
    self.handle_sst(data)
  File "/opt/venv/lib/python3.10/site-packages/xlrd/book.py", line 1178, in handle_sst
    self._sharedstrings, rt_runlist = unpack_SST_table(strlist, uniquestrings)
  File "/opt/venv/lib/python3.10/site-packages/xlrd/book.py", line 1474, in unpack_SST_table
    assert _unused_i == nstrings - 1
AssertionError

原因分析 #

这是文件本身格式不规范导致的：

• 该文件大概率由非标准导出工具生成，在共享字符串表（SST，Excel存储重复字符串的优化结构）的索引或长度计算上不符合BIFF格式规范
• Windows Excel和Google Sheets内置了更强的容错解析逻辑，能忽略这类格式错误；但xlrd对格式合规性要求严格，触发了断言检查失败
• 重复报错对应同一字符串，说明该字符串在SST中的存储格式存在异常，导致xlrd解析时索引计算偏差

解决方案 #

1. 换用openpyxl引擎（推荐） #

openpyxl对不规范xls文件的容错性优于xlrd，同时需按照FutureWarning提示用BytesIO包装字节流：

import requests
import pandas as pd
from io import BytesIO

url = "https://www.ufd.es/wp-content/uploads/2024/05/publicacion-capacidad-Junio.xls"
headers = {
    "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/109.0.0.0 Safari/537.36",
}
response = requests.get(url, headers=headers)
df = pd.read_excel(BytesIO(response.content), header=[0, 1], engine="openpyxl")

2. 让pandas自动选择引擎 #

去掉engine参数，让pandas自动检测文件格式并选择合适的解析引擎，有时能避开xlrd的严格检查：

df = pd.read_excel(BytesIO(response.content), header=[0, 1])

3. 预处理修复文件格式 #

先用Windows Excel或Google Sheets打开文件，另存为标准xls/xlsx格式，再用pandas读取——这种方法能彻底修复格式不规范问题，适合长期使用。若需批量处理，可在Windows环境用win32com.client自动化另存操作。

4. 临时修改xlrd断言（不推荐） #

若必须使用xlrd，可临时修改xlrd/book.py中unpack_SST_table函数的断言：

# 原代码行
assert _unused_i == nstrings - 1
# 修改为
try:
    assert _unused_i == nstrings - 1
except AssertionError:
    pass

但此方法会跳过格式检查，可能导致部分字符串解析错误或数据丢失，仅作为应急方案。

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