如何在Azure生态中用openpyxl加载Blob Storage的Excel工作簿

核心问题 #

load_workbook的filename参数不支持直接传入Blob的普通URL或StorageStreamDownloader对象：

• 普通Blob URL默认无访问权限，会触发FileNotFoundError
• StorageStreamDownloader是Azure SDK的专属对象，不符合load_workbook要求的字符串、字节或路径类对象类型，导致TypeError

可行解决方案 #

方案1：内存中通过字节流加载（推荐） #

利用io.BytesIO将Blob的二进制内容包装为类文件对象，直接供load_workbook读取，全程无需本地存储：

import io
from openpyxl import load_workbook
from azure.storage.blob import BlobServiceClient

# 初始化客户端
conn_str = "abc123"
container_name = "a_container"
xl_blob_name = "a_xl"

blob_service_client = BlobServiceClient.from_connection_string(conn_str)
container_client = blob_service_client.get_container_client(container_name)

# 读取Blob内容并转为类文件对象
download_blob = container_client.download_blob(xl_blob_name)
blob_byte_stream = io.BytesIO(download_blob.readall())

# 加载工作簿
workbook = load_workbook(filename=blob_byte_stream)

方案2：使用SAS URL加载 #

如果有权限生成Blob的SAS（共享访问签名）URL，可直接将带权限的SAS URL传入load_workbook：

from openpyxl import load_workbook
from azure.storage.blob import BlobServiceClient, generate_blob_sas, BlobSasPermissions
from datetime import datetime, timedelta

conn_str = "abc123"
container_name = "a_container"
xl_blob_name = "a_xl"

# 初始化客户端
blob_service_client = BlobServiceClient.from_connection_string(conn_str)
blob_client = blob_service_client.get_blob_client(container=container_name, blob=xl_blob_name)

# 生成有效期1小时的SAS令牌
sas_token = generate_blob_sas(
    account_name=blob_service_client.account_name,
    container_name=container_name,
    blob_name=xl_blob_name,
    account_key=blob_service_client.credential.account_key,
    permission=BlobSasPermissions(read=True),
    expiry=datetime.utcnow() + timedelta(hours=1)
)
sas_url = f"{blob_client.url}?{sas_token}"

# 通过SAS URL加载工作簿
workbook = load_workbook(filename=sas_url)

说明 #

两种方案均在Azure生态内完成操作，无需依赖本地内存之外的存储。方案1无需额外权限配置，适用性更广；方案2适合需要共享链接的场景。

内容的提问来源于stack exchange，提问作者fritz.ann165