python - 将 Excel 文件流保存到 azure blob 存储

Question

我用 Python 编写了几行代码，使用 openpyxl-library 从 Azure Blob 存储打开 Excel 文件。该代码正在 Azure Functions 中运行。

对内容进行一些修改后，我将创建第二个工作簿并将原始工作簿中的内容复制到其中。我将第二个工作簿保存为流。现在我想再次将第二个工作簿作为 Excel 文件(其他容器)保存回 Blob 存储。

    import logging
    from typing import Container
    
    import azure.functions as func
    import azure.storage.blob
    import openpyxl as xl
    import io
    

    ### Start Trigger
    def main(myblob: func.InputStream):
        logging.info(f"Python blob trigger function processed blob \n"
                     f"Name: {myblob.name}\n"
                     f"Blob Size: {myblob.length} bytes")
        logging.info('Loading Workbook...')
        ### Load Excel file
        wb1 = xl.load_workbook(filename=io.BytesIO(myblob.read()))
        logging.info(wb1.sheetnames)
        
        ### Create Second Workbook
        output = io.BytesIO()
        wb2 = xl.Workbook()
        wb2.active
        wb2.create_sheet('TestSheet')
        wb2.save(output)

        ### Upload to Azure Blob Storage
        blob_service_client = azure.storage.blob.BlobServiceClient.from_connection_string(conString)
        blob_client = blob_service_client.get_blob_client(container='test2', blob='test2.xlsx')
        blob_client.upload_blob(output)

当我现在运行代码时，一切都会正常。但 Blob 存储上的 Excel 文件已损坏，当我尝试打开 Excel 文件时出现以下错误:

感谢您的帮助!

Answer 1

问题好像出在:wb2.save(output)，可以引用如下代码:

import logging
import azure.functions as func
import azure.storage.blob
import openpyxl as xl
import io
from tempfile import NamedTemporaryFile


def main(myblob: func.InputStream):
    logging.info(f"Python blob trigger function processed blob \n"
                 f"Name: {myblob.name}\n"
                 f"Blob Size: {myblob.length} bytes")
    #logging.info(myblob.read())
    logging.info('Loading Workbook...')
    ### Load Excel file
    wb1 = xl.load_workbook(filename=io.BytesIO(myblob.read()))
    ws1 = wb1.worksheets[0]
    logging.info(wb1.sheetnames)

    ### Create Second Workbook
    #output = io.BytesIO()
    wb2 = xl.Workbook()
    ws2 = wb2.create_sheet('TestSheet')

    # calculate total number of rows and 
    # columns in source excel file
    mr = ws1.max_row
    mc = ws1.max_column
    
    # copying the cell values from source 
    # excel file to destination excel file
    for i in range (1, mr + 1):
        for j in range (1, mc + 1):
            # reading cell value from source excel file
            c = ws1.cell(row = i, column = j)
    
            # writing the read value to destination excel file
            ws2.cell(row = i, column = j).value = c.value

    ### Upload to Azure Blob Storage
    conString = ""
    blob_service_client = azure.storage.blob.BlobServiceClient.from_connection_string(conString)
    blob_client = blob_service_client.get_blob_client(container='testout', blob='test2.xlsx')
    with NamedTemporaryFile() as tmp:
        wb2.save(tmp.name)
        output = io.BytesIO(tmp.read())
        blob_client.upload_blob(output)