Learn how to use Dash Bio for next-gen sequencing & quality control. 🧬 Register for the Oct 27 webinar.

Generate multiple tabs in Excel file with dcc.send_data_frame()

Hello Community,

I have a dict of dataframes and I want to generate 1 Excel File with multiple tabs where each tab is related to one dataframe of my dict .

I saw the example below in the doc but it’s work only for a unique dataframe and a unique excel tab.

any solution?

here the example I saw in the doc

import dash
from dash.dependencies import Output, Input
import dash_html_components as html
import dash_core_components as dcc

app = dash.Dash(prevent_initial_callbacks=True)
app.layout = html.Div(
    [
        html.Button("Download Excel", id="btn_xlxs"),
        dcc.Download(id="download-dataframe-xlxs"),
    ]
)

import pandas as pd
import xlsxwriter

df = pd.DataFrame({"a": [1, 2, 3, 4], "b": [2, 1, 5, 6], "c": ["x", "x", "y", "y"]})


@app.callback(
    Output("download-dataframe-xlxs", "data"),
    Input("btn_xlxs", "n_clicks"),
    prevent_initial_call=True,
)
def func(n_clicks):
    return dcc.send_data_frame(df.to_excel, "mydf.xlxs", sheet_name="Sheet_name_1")


if __name__ == "__main__":
    app.run_server(debug=True)

Hey Josiias, did you find any solutions ?

Hello friend, not yet …

I think the answer is here,

One solution can be that you can save the excel file to your project folder and use dcc.send_file(). Refer to Downloading Images section in dcc.Download | Dash for Python Documentation | Plotly. For Example:

`

@app.callback(Output('download-dataframe-xlsx', 'data'), Input('download-button', 'n_clicks'), prevent_initial_call=True,)
def export_plot_data_to_excel(n):
    
    # Create some Pandas dataframes from some data.
    df1 = pd.DataFrame({'Data': [11, 12, 13, 14]})
    df2 = pd.DataFrame({'Data': [21, 22, 23, 24]})
    df3 = pd.DataFrame({'Data': [31, 32, 33, 34]})

    # Create a Pandas Excel writer using XlsxWriter as the engine.
    writer = pd.ExcelWriter('new_excel_file.xlsx'), engine='xlsxwriter')

    # Write each dataframe to a different worksheet.
    df1.to_excel(writer, sheet_name='Sheet1')
    df2.to_excel(writer, sheet_name='Sheet2')
    df3.to_excel(writer, sheet_name='Sheet3')

    # Close the Pandas Excel writer and output the Excel file.
    writer.save()

    return dcc.send_file('new_excel_file.xlsx')`
1 Like

Nice solution.
Thank you :slight_smile:

I hope my answer will help someone else. I found the solution with dcc.send_file() is not as convinient as dcc.send_data_frame(). You have to store a lot of files inside your project folder. Solution that worked for me is dcc.send_bytes()

import io
output = io.BytesIO()
writer = pd.ExcelWriter(output, engine='xlsxwriter')
df_1.to_excel(writer, sheet_name='sheet_1', index=False)  # writes to BytesIO buffer
df_2.to_excel(writer, sheet_name='sheet_2', index=False)
df_3.to_excel(writer, sheet_name='sheet_3', index=False)
df_4.to_excel(writer, sheet_name='sheet_4', index=False)
df_5.to_excel(writer, sheet_name='sheet_5', index=False)
writer.save()
data = output.getvalue()
return dcc.send_bytes(data, 'name_of_the_file.xlsx')