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

Dash Python
1

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)
1 Like
2

Hey Josiias, did you find any solutions ?

3

Hello friend, not yet …

4

I think the answer is here,

5

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')`
2 Likes
6

Nice solution.
Thank you :slight_smile:

7

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')
5 Likes
8

For some reason I keep getting an error saying ‘dash_core_components’ has no attribute ‘send_data_frame’? happens with send_file and send_bytes as well. Are these deprecated?

9

What version are you on? How does your imports look?

1 Like
10

I am using Dcc 2.0.0 and my import was import dash_core_components as dcc, but I just switched it to from dash import dcc and everything seems to work. Thank you for the insight I would never have thought that was the issue!

11

This solution is excellent! Just wanted to add: replace writer.save() with writer.close() as the save function is generating depreciation warnings.

1 Like