Python读取URL来源xls文件触发AssertionError报错问题

通过Python读取URL指向的xls文件并转为pandas DataFrame时，触发如下AssertionError报错：

Traceback (most recent call last):
File "c:\Sample_project\venv\excel_read_v1.py", line 17, in 
df = pd.read_excel("file.xls",
File "C:\Sample_project\venv\lib\site-packages\pandas\util_decorators.py", line 311, in wrapper
return func(*args, **kwargs)
File "C:\Sample_project\venv\lib\site-packages\pandas\io\excel_base.py", line 457, in read_excel
io = ExcelFile(io, storage_options=storage_options, engine=engine)
File "C:\Sample_project\venv\lib\site-packages\pandas\io\excel_base.py", line 1419, in __init__
self._reader = self._engines[engine](self._io, storage_options=storage_options)
File "C:\Sample_project\venv\lib\site-packages\pandas\io\excel_xlrd.py", line 25, in __init__
super().__init__(filepath_or_buffer, storage_options=storage_options)
File "C:\Sample_project\venv\lib\site-packages\pandas\io\excel_base.py", line 518, in __init__
self.book = self.load_workbook(self.handles.handle)
File "C:\Sample_project\venv\lib\site-packages\pandas\io\excel_xlrd.py", line 38, in load_workbook
return open_workbook(file_contents=data)
File "C:\Sample_project\venv\lib\site-packages\xlrd\__init__.py", line 172, in open_workbook
bk = open_workbook_xls(
File "C:\Sample_project\venv\lib\site-packages\xlrd\book.py", line 104, in open_workbook_xls
bk.parse_globals()
File "C:\Sample_project\venv\lib\site-packages\xlrd\book.py", line 1211, in parse_globals
self.handle_sst(data)
File "C:\Sample_project\venv\lib\site-packages\xlrd\book.py", line 1178, in handle_sst
self._sharedstrings, rt_runlist = unpack_SST_table(strlist, uniquestrings)
File "C:\Sample_project\venv\lib\site-packages\xlrd\book.py", line 1472, in unpack_SST_table
assert _unused_i == nstrings - 1
AssertionError

已验证的现象与需求：

• 文件下载到本地后，手动删除末尾2个空行即可正常读取
• 需要实现从URL拉取到结果导出的全自动化流程，不能手动修改文件
• 已尝试切换openpyxl、xlrd作为pandas读取引擎，均无法解决问题
  当前使用的测试代码如下：

import openpyxl
import xlrd 
from xlrd import open_workbook
import requests
import pandas as pd
url = url

r = requests.get(url)
with open('maskefile.xls', 'wb') as output:
    output.write(r.content)







df = pd.read_excel("maskedfile.xls",sheet_name = "maskedsheetname")            

df.to_csv("C:\Sample_project\maskedfile.csv" ,index = False)

报错根源：目标xls文件末尾带无效空字节/空行，触发xlrd解析共享字符串表时的内置断言校验。代码自动清理末尾无效内容即可实现全流程自动化，无需手动修改文件。

方案1：最小改动（落盘后自动清理） #

下载文件后自动截断末尾连续空内容再读取，和手动删空行效果完全一致：

import requests
import pandas as pd
import os

url = "替换为实际目标文件URL"
save_path = "maskedfile.xls"
export_path = r"C:\Sample_project\maskedfile.csv"

# 下载文件
r = requests.get(url)
with open(save_path, 'wb') as f:
    f.write(r.content)

# 自动清理文件末尾的连续无效空字符
with open(save_path, 'rb+') as f:
    f.seek(0, os.SEEK_END)
    file_size = f.tell()
    # 从末尾往前定位到最后一个有效字节
    for i in range(file_size-1, -1, -1):
        f.seek(i)
        if f.read(1) not in [b'\x00', b'\r', b'\n', b' ', b'\t']:
            f.truncate(i+1)
            break

# 正常读取导出
df = pd.read_excel(save_path, sheet_name="maskedsheetname")
df.to_csv(export_path, index=False)

方案2：无临时文件（内存直接处理） #

不需要在本地存储临时xls文件，直接在内存中清理内容后读取，流程更简洁：

import io
import requests
import pandas as pd

url = "替换为实际目标文件URL"
export_path = r"C:\Sample_project\maskedfile.csv"

r = requests.get(url)
# 直接清理字节流末尾所有无效空字符
content = r.content.rstrip(b'\x00\r\n\t ')

# 从内存字节流直接读取，无需落盘
df = pd.read_excel(io.BytesIO(content), sheet_name="maskedsheetname")
df.to_csv(export_path, index=False)

注意事项 #

• openpyxl仅支持xlsx/xlsm格式，本身无法读取老式xls文件，之前切换这个引擎无效属于正常情况，读xls默认用xlrd即可
• Windows本地路径前加r标记为原生字符串，避免反斜杠被Python识别为转义字符导致路径报错
• 若清理后仍有解析问题，可以先打印content[-20:]查看末尾残留的异常字节，加到rstrip的清理字符列表里即可

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