Script to create (or append new sheet) a new XLSX file in lakehouse Files directory

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