[Python] Using Python, how to update an excel sheet on Azure Blob Storage while maintaining...

I'm trying to update an existing Excel worksheet on an Azure Blob Storage. The problem is that the existing data in the sheet gets wiped out. I want to maintain the existing data within the spreadsheet. Here is my code:

from azure.storage.blob import BlobServiceClient
import pandas as pd
import logging
import openpyxl as xl
import io
def update_excel():
try:
#Azure Blob Storage Configuration
account_name = 'account_name'
account_key = 'account_key'
connect_str = 'DefaultEndpointsProtocol=https;AccountName=' + account_name + ';AccountKey=' + account_key + ';EndpointSuffix=core.windows.net'

#create a client to interact with blob storage
blob_service_client = BlobServiceClient.from_connection_string(connect_str)

# container info
container = 'container_name'
directory = 'directory_name'

#use the client to connect to the container
container_client = blob_service_client.get_container_client(container)

#excel info
excel_name = 'Excel_file.xlsx'
excel_name_new = 'Excel_file_new.xlsx'
sheet_name = 'Sheet1'
blob_name = directory + '/' + excel_name
new_blob_name = directory + '/' + excel_name_new

#download blob
blob_client = blob_service_client.get_blob_client(container=container, blob=blob_name)
downloaded_blob = blob_client.download_blob()

#load excel
wb1 = xl.load_workbook(filename=io.BytesIO(downloaded_blob.read()),keep_vba=False)
ws = wb1[sheet_name]
max_row = ws.max_row

#create dataframe
data = {'Name': ['Tom', 'nick', 'krish', 'jack'],
'Age': ['20', '30', '40', '50']
}
df = pd.DataFrame(data)

#upload excel
output = io.BytesIO()
df.to_excel(output, sheet_name = sheet_name, startrow = max_row, startcol=1, engine='openpyxl', header=False, index=False)
output.seek(0)
container_client.upload_blob(name=new_blob_name, data=output, overwrite=True)
except Exception as e:
logging.error(f"Error: {e}")


Is there a way I can modify the code so it maintains the existing data in the worksheet while updating?

Continue reading...