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

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

amaaiia — Thu, 20 Feb 2025 07:33:51 GMT

Hi.

I'm trying to create a python function to create a new XLSX file in Files directory. I want to append new sheet if the file already exists, otherwise, create new file.

This is tha data I provide to the function:

dest_path = 'abfss://'+str(workspace_id)+'@onelake.dfs.fabric.microsoft.com/'+str(lakehouse_id)+"/Files/testing_file.xlsx"
sheet_name = 'sheet1'
df (pandas df)

def df_to_excel(df, dest_path, sheet_name): import pandas as pd from openpyxl import load_workbook try: import os file_name=os.path.basename(dest_path) local_path = '/tmp/'+str(file_name) # Copy file from Azure Blob Storage to local file system import fsspec account_name = 'onelake' account_host = 'onelake.dfs.fabric.microsoft.com' fs = fsspec.filesystem('abfss', account_name=account_name, account_host=account_host) 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) print("Exists") except FileNotFoundError: existing_book = None print("Doesn't exist") # If exists, add existing_book. Otherwise, new file with pd.ExcelWriter(local_path, engine='openpyxl',mode='a') as writer: if existing_book: writer.book = existing_book df_pd.to_excel(writer, sheet_name=sheet_name, index=False)

But it's not working.

I copy the file into local system because load_workbook function is not able to find the file in remote server

Any ideas?

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

v-prasare — Thu, 20 Feb 2025 18:34:23 GMT

Your current function has a few issues that need fixing:

shutil is not imported – You are using shutil.copyfileobj() but haven't imported the module.
df_pd is undefined – You should be using df, which is the function argument.
File handling in Azure OneLake (ABFS) needs a write-back operation – You are copying the file locally, modifying it, but not writing it back to OneLake.

Can you please fix these and try again once.

Thanks,

Prashanth Are

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

v-prasare — Thu, 27 Feb 2025 06:46:11 GMT

@amaaiia, As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for your issue worked? or let us know if you need any further assistance here?

Thanks,

Prashanth Are

MS Fabric community support

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

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

v-prasare — Mon, 03 Mar 2025 05:20:32 GMT

@amaaiia, As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for your issue worked? or let us know if you need any further assistance here?

Thanks,

Prashanth Are

MS Fabric community support

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

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

v-prasare — Thu, 06 Mar 2025 06:37:47 GMT

@amaaiia, As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for your issue worked? or let us know if you need any further assistance here?

Thanks,

Prashanth Are

MS Fabric community support

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

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

amaaiia — Thu, 06 Mar 2025 08:01:37 GMT

Not working

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

v-prasare — Mon, 23 Jun 2025 07:45:22 GMT

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

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

v-prasare — Mon, 23 Jun 2025 07:45:35 GMT

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

If we don’t hear back, we’ll go ahead and close this thread. For any further discussions or questions, please start a new thread in the Microsoft Fabric Community Forum we’ll be happy to assist.

Thank you for being part of the Microsoft Fabric Community.