Writing pandas dataframe as xlsx file to an azure blob storage without creating a local file

Question

I have the following solution using a local file, but I would like to skip that. Is there a possibility?

blob_service_client = BlobServiceClient(account_url = url, credential = token)
write_blob_client = blob_service_client.get_blob_client(container=container_name, blob=blob_name)
filename = "example.xlsx"
df.to_excel(filename ,index = False)
with open(filename, "rb") as df:
write_blob_client.upload_blob(df, overwrite = True)

Instead of the last three rows I have tried

teststream = BytesIO(df.to_records(index = False).tostring())
write_blob_client.upload_blob(teststream, overwrite = True)

This writes an excel file to the blob storage, but when trying to open it I get an error that the file extention doesn't match the format of the file.

Answer 1

You have to get the value from the BytesIO object. The following code works for me.

writer = io.BytesIO()
df.to_excel(writer)
blob_client.upload_blob(writer.getvalue(), overwrite = True)