Saving multi-sheet excel file to Azure Blob Storage using Pandas in Python

Question

I am trying to create two sheets in an excel file, and then upload it to Azure Blob Storage using Pandas in Python. However, I am getting an error - "Cannot .getvalue() of 'OpenpyxlWriter' object new_writer".

    blob_client = blob_service_client.get_blob_client(container=new_container_name, blob=file_name)

    writer = io.BytesIO()

    summary = pd.DataFrame({"one_val": [1, 2]})
    summary.to_excel(writer, sheet_name="Sheet A", index=False)
    blob_client.upload_blob(writer.getvalue(), overwrite=True)

    writer = io.BytesIO()
    summary2 = pd.DataFrame({"two_val": [3, 4]})

    with pd.ExcelWriter(writer, engine="openpyxl", mode="a") as new_writer:
        summary2.to_excel(new_writer, sheet_name="Sheet B", index=False, encoding="utf-8")
        blob_client.upload_blob(new_writer.getvalue(), blob_type="AppendBlob", overwrite=False)

Any help regarding a solution to this problem is appreciated. Thanks

See my answer below, but I would guess, that you forgot writer.save() before writer.getValue() — FloLie
– FloLie, Commented Jan 7, 2022 at 9:56

FloLie · Accepted Answer · 2022-01-07 09:54:24Z

3

Read this for how to save xlsx to a string https://xlsxwriter.readthedocs.io/working_with_pandas.html#saving-the-dataframe-output-to-a-string

and this for handling multiple sheets https://xlsxwriter.readthedocs.io/example_pandas_multiple.html

final example is

import pandas as pd
import io

# Create some Pandas dataframes from some data.
df1 = pd.DataFrame({'Data': [11, 12, 13, 14]})
df2 = pd.DataFrame({'Data': [21, 22, 23, 24]})

output = io.BytesIO()

writer = pd.ExcelWriter(output, engine='xlsxwriter')

# Write the data frame to the BytesIO object.
df1.to_excel(writer, sheet_name='Sheet1')
df2.to_excel(writer, sheet_name='Sheet2')

writer.save()
xlsx_data = output.getvalue()

edited Jan 7, 2022 at 9:54

answered Jan 7, 2022 at 9:48

FloLie

1,8511 gold badge10 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

lsl__ Over a year ago

Hi, how the container name and blob name are defined for the excel file uploaded to azure in your example? thx!

FloLie Over a year ago

You would have to use the Azure pyhton library. How to is here learn.microsoft.com/de-de/azure/storage/blobs/…

Collectives™ on Stack Overflow

Saving multi-sheet excel file to Azure Blob Storage using Pandas in Python

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related