Download an excel file with column formatting in Dash Python

Question

I'm creating a Dash app and I want to allow users to download a pandas Dataframe as an excel file with a click on a button.

I managed to implement it with this callback :

@app.callback(
Output("download_xlsx", "data"),
Input("btn_download_xlsx", "n_clicks"), State("df", "data")
)
def download_xlsx(n_clicks, json_df):
    df =  pd.read_json(json_df)
    return dcc.send_data_frame(df.to_excel, "nom_fic.xlsx", **{'sheet_name': "Feuil1", 'index': False})

It works great but the Excel file is not formatted as I want, especially because the first column is a really big integer and Excel displays it with scientific notation, and I want to force Excel to display the whole int.

I tried to use XlsxWriter formating as such :

@app.callback(
Output("download_xlsx", "data"),
Input("btn_download_xlsx", "n_clicks"), State("df", "data")
)
def download_xlsx(n_clicks, json_df):
    df =  pd.read_json(json_df)

    writer = pd.ExcelWriter("nom_fic.xlsx", engine='xlsxwriter')
    df.to_excel(writer, sheet_name="Feuil1", index=False)

    workbook = writer.book
    worksheet = writer.sheets["Feuil1"]

    format = workbook.add_format({'num_format': '#,##00.0'})
    worksheet.set_column('A:A', None, format)

    writer.save()

    return dcc.send_data_frame(df.to_excel, "nom_fic.xlsx", **{'sheet_name': "Feuil1", 'index': False})

But I get the following callback error when I try to dowload the file :

TypeError: to_excel() got multiple values for argument 'excel_writer'

Can someone see how I can deal with the to_excel() inputs so that I can use the writer with the formatting I want, or has any other idea to solve this problem ?

Thank you :)

Answer 1

I eventually found a way to format an Excel file and then allow the user to download it. I use xlsxwriter to format my workbook, then save it in the app environnement, and use dcc.send_file rather than dcc.send_data_frame :

@app.callback(
Output("download_xlsx", "data"),
Input("btn_download_xlsx", "n_clicks"), State("df", "data")
)
def download_xlsx(n_clicks, json_df):
    df =  pd.read_json(json_df)

    if os.path.exists("export.xlsx"): 
         os.remove("export.xlsx")

    writer = pd.ExcelWriter("export.xlsx", engine='xlsxwriter')
    df.to_excel(writer, sheet_name="Feuil1", index=False)

    workbook = writer.book
    worksheet = writer.sheets["Feuil1"]

    format = workbook.add_format({'num_format': '#,##00.0'})
    worksheet.set_column('A:A', None, format)

    writer.save()

    return dcc.send_file("export.xlsx", "filename.xlsx")

It works great locally with only one user, but I'm afraid it will cause issues after deployement when several users will use the app at the same time. (Because it writes a file in the server data, that is shared by all users)

---

EDIT : I found the solution I was looking for, by using a function defined inside the callback that takes the file as a BytesIo in input, formats it, and then writes it. Now it doesn't need to write the formatted excel into a file stored in the server :

@app.callback(
    Output("download_xlsx", "data"),
    Input("btn_download_xlsx", "n_clicks"),
    State("df", "data")
)
def download_xlsx(n_clicks, json_df):
    df =  pd.read_json(json_df)

    def to_xlsx(bytes_io):
        writer = pd.ExcelWriter(bytes_io, engine='xlsxwriter')
        df.to_excel(writer, sheet_name="Feuil1", index=False)

        workbook = writer.book
        worksheet = writer.sheets["Feuil1"]

        format = workbook.add_format({'num_format': '#,##00.0'})
        worksheet.set_column('A:A', None, format)

        writer.save()

    return dcc.send_bytes(to_xlsx, "filename.xlsx")