python - 从Azure blob读取excel数据并使用Python azure函数转换为csv

Question

我想部署具有以下功能的azure函数

1. 将 Excel 数据从 Azure blob 读取到流对象中，而不是下载到虚拟机上。
2. 读入数据框我需要帮助将 Excel 文件读取到数据框中。如何更新已放置的持有人 download_file_path 以读取 Excel 数据。

    import pandas as pd 
    import os 
    import io
    from azure.storage.blob import BlobClient,BlobServiceClient,ContentSettings
        
    connectionstring="XXXXXXXXXXXXXXXX" 
    excelcontainer = "excelcontainer"        
    excelblobname="Resource.xlsx" 
    sheet ="Resource" 
            
    blob_service_client =BlobServiceClient.from_connection_string(connectionstring)
    download_file_path =os.path.join(excelcontainer)
    blob_client = blob_service_client.get_blob_client(container=excelcontainer, blob=excelblobname)
    with open(download_file_path, "rb") as f:
       data_bytes = f.read()
    df =pd.read_excel(data_bytes, sheet_name=sheet, encoding = "utf-16")

Answer 1

如果你想用 panda 从 Azure blob 读取 excel 文件，你有两种选择

1. 为 blob 生成 SAS token ，然后使用带有 SAS token 的 blob URL 来访问它

from datetime import datetime, timedelta
import pandas as pd
from azure.storage.blob import BlobSasPermissions, generate_blob_sas
def main(req: func.HttpRequest) -> func.HttpResponse:
    account_name = 'andyprivate'
    account_key = 'h4pP1fe76*****A=='
    container_name = 'test'
    blob_name="sample.xlsx"
    sas=generate_blob_sas(
      account_name=account_name,
      container_name=container_name,
      blob_name=blob_name,
      account_key=account_key,
      permission=BlobSasPermissions(read=True),
      expiry=datetime.utcnow() + timedelta(hours=1)
    )

    blob_url = f'https://{account_name}.blob.core.windows.net/{container_name}/{blob_name}?{sas}'
    df=pd.read_excel(blob_url)
    print(df)
    ......

下载 blob

from azure.storage.blob import  BlobServiceClient
def main(req: func.HttpRequest) -> func.HttpResponse:
    account_name = 'andyprivate'
    account_key = 'h4pP1f****='

    blob_service_client = BlobServiceClient(account_url=f'https://{account_name }.blob.core.windows.net/', credential=account_key)
    blob_client = blob_service_client.get_blob_client(container='test', blob='sample.xlsx')
    downloader =blob_client.download_blob()
    df=pd.read_excel(downloader.readall())
    print(df)
    ....