Hi amaiia,
I noticed that there are some errors in the code apart from missing an export from shutil.
The ExcelWriter context is not correctly configured when appending sheets.
You're not writing the file back to the lakehouse after saving it locally and there is a typo in df_pd.to_excel(...) — it should be df.to_excel(...)
You can use this code and check-
def df_to_excel(df, dest_path, sheet_name):
import pandas as pd
import shutil
import os
from openpyxl import load_workbook
from pandas import ExcelWriter
import fsspec
account_name = 'onelake'
account_host = 'onelake.dfs.fabric.microsoft.com'
fs = fsspec.filesystem('abfss', account_name=account_name, account_host=account_host)
file_name = os.path.basename(dest_path)
local_path = f'/tmp/{file_name}'
existing_book = None
file_exists = False
try:
with fs.open(dest_path, 'rb') as src_file:
with open(local_path, 'wb') as dst_file:
shutil.copyfileobj(src_file, dst_file)
existing_book = load_workbook(local_path)
file_exists = True
print("File exists, appending sheet.")
except FileNotFoundError:
print("File does not exist, will create a new one.")
# Write to the Excel file
with pd.ExcelWriter(local_path, engine='openpyxl', mode='a' if file_exists else 'w') as writer:
if existing_book:
writer.book = existing_book
writer.sheets = {ws.title: ws for ws in existing_book.worksheets}
df.to_excel(writer, sheet_name=sheet_name, index=False)
# Upload updated file back to lakehouse
with open(local_path, 'rb') as f:
with fs.open(dest_path, 'wb') as dest_file:
shutil.copyfileobj(f, dest_file)
print("File written successfully.")
Hope this helps!
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query
