Black Lives Matter. Please consider donating to Campaign Zero's mission of ending police violence in America.
https://www.joincampaignzero.org

Allow users to dowload an Excel in a click

@Hujikol0 I was trying to do something like this but I kept getting stuck and I couldn’t find ANYTHING on how to fix the issues I was running into, but your code was FINALLY just what I needed to get my app working. Thank you so much for sharing!!!

Do you know if there would be any reason why this solution wouldn’t work on a mac? I’m trying to run your code but the xls file that gets downloaded has 0 bytes and cannot be opened… :frowning:

This is really great. Thanks for sharing - just what I needed!

Hi - have you tried in different browsers? We have seen strangely different behaviour between Safari and Chrome.

I figured it out, it’s because of the cache. Reset your cache in Chrome and in the future the send_file function you should set the cache_timeout=0.

Here’s the whole example:

import dash
import dash_html_components as html
from flask import send_file
import pandas as pd


app = dash.Dash()
app.layout = html.Div(
    children=[html.A("download excel", href="/download_excel/")])


@app.server.route("/download_excel/")
def download_excel():
    # Create DF
    d = {"col1": [1, 2], "col2": [3, 4]}
    df = pd.DataFrame(data=d)

    # Convert DF
    buf = io.BytesIO()
    excel_writer = pd.ExcelWriter(buf, engine="xlsxwriter")
    df.to_excel(excel_writer, sheet_name="sheet1")
    excel_writer.save()
    excel_data = buf.getvalue()
    buf.seek(0)

    return send_file(
        buf,
        mimetype = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet', 
        attachment_filename="test11311.xlsx",
        as_attachment=True,
        cache_timeout=0
    )


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

Thanks, that’s good to know - we’ll try using that too!

Is it possible to integrate this in a callback which creates the dataframe dynamically (based on inputs to the callback)?

You could refer to Allowing users to download CSV on click to integrate this in a callback.
Hope the following example helps

import dash
import flask
import dash_html_components as html
import dash_core_components as dcc
from dash.dependencies import Input, Output
from flask import send_file
import io
import pandas as pd

d = {"col1": [1, 2], "col2": [3, 4]}
df = pd.DataFrame(data=d)

app = dash.Dash()
app.layout = html.Div([
    html.A("Download as Excel", id = 'my-link'),
    dcc.Dropdown(
                    id='my-dropdown',
                    options=[
                        {'label': '1', 'value': '1'},
                        {'label': '2', 'value': '2'},
                    ],value='1'
                    )])


@app.callback(
    Output('my-link', 'href'),
    [Input('my-dropdown', 'value')])

def update_link(value):
    return '/dash/urlToDownload?value={}'.format(value)

@app.server.route('/dash/urlToDownload')
def download_excel():
    value = flask.request.args.get('value')
    df1 = df['col{}'.format(value)]
    buf = io.BytesIO()
    excel_writer = pd.ExcelWriter(buf, engine="xlsxwriter")
    df1.to_excel(excel_writer, sheet_name="sheet1", index=False)
    excel_writer.save()
    excel_data = buf.getvalue()
    buf.seek(0)
    return send_file(
        buf,
        mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
        attachment_filename="test11311.xlsx",
        as_attachment=True,
        cache_timeout=0
    )


if __name__ == "__main__":
    app.run_server(debug=False)
1 Like

Could you recommend how I can ‘send’ a DataFrame generated in another callback to the download route served by Flask?

2 Likes

You could try to cache it the Dataframe and then pull it in your download_excel().

Also…I’m having problems with running some of the earlier code. I keep getting an error in excel about corrupt values or file format. Any ideas?

here is callback-only version (doesn’t work on IE):

import base64
import io
import dash
import pandas as pd

app = dash.Dash(__name__)

app.layout = html.Div([
    html.A(
        'Download Excel Data',
        id='excel-download',
        download="data.xlsx",
        href="",
        target="_blank"
    )
])

@app.callback(Output('excel-download', 'href'),
             [Input(...)])
def update_dashboard(...):
    df = pd.DataFrame(...)
    xlsx_io = io.BytesIO()
    writer = pd.ExcelWriter(xlsx_io, engine='xlsxwriter')
    df.to_excel(writer, sheet_name=period)
    writer.save()
    xlsx_io.seek(0)
    # https://en.wikipedia.org/wiki/Data_URI_scheme
    media_type = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
    data = base64.b64encode(xlsx_io.read()).decode("utf-8")
    href_data_downloadable = f'data:{media_type};base64,{data}'
    return href_data_downloadable 

needed this tip! Thank you!

Hi, i made my code your way, and everything worked fine, but when i need to download big file - it gives me empty file (0 bytes).
And I need to make my code to download big xlsx file’s.

Does anyone know how i can do this?

SOS, need help!
i have this button:

html.A(
    html.Button('Скачать'),
    id='excel-download',
    download="data.xlsx",
    href='',
    target="_blank"
)

and a callback:

@app.callback(Output('excel-download', 'href'),
              [Input('start_revise', 'n_clicks')],
              [State('loading-states-table', 'data'), State('offer', 'value'),
               State('goal', 'value'), State('subid-col', 'value'), State('tid-col', 'value'),
               State('select_id', 'value')])
def download_some_excel(click, df, offer_id, goal_id, subid_col, tid_col, id_for_load):
    return file_download(df, subid_col, offer_id, goal_id, id_for_load)

and a file download function:

def file_download(df, id_col, offer_id, goal_id, id_for_load):
    adv_data = pd.DataFrame(df)
    ...some other genius code...

    xlsx_io = io.BytesIO()
    excel_writer = pd.ExcelWriter(xlsx_io, engine='xlsxwriter')

    if some statement:
        some_df.to_excel(excel_writer, sheet_name="Дубли в стате лидгида", index=False)
    if another statement:
        some_df.to_excel(excel_writer, sheet_name="other_sheet_name", index=False)

    excel_writer.save()
    xlsx_io.seek(0)
    # https://en.wikipedia.org/wiki/Data_URI_scheme
    media_type = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
    data = base64.b64encode(xlsx_io.read()).decode("utf-8")
    href_data_downloadable = f'data:{media_type};base64,{data}'

    return href_data_downloadable

and everything worked fine, until it has become necessary to download a big file, around 40k rows with 3-4 sheets. In results it gave me just an empty file (0 bytes): image

Does anyone knows, what to do? I need this “download file” option.

Are you running your app locally or hosted online somewhere (like Heroku)? I know Heroku limits web requests to 30 seconds so larger downloads could exceed this limit. I found this out the hard way (also getting 0 byte downloads) and was able to optimze my excel create time to sneak under this limit. You could also retool your app to generate the large excel file using a worker thread in the background and then have plumbing in place for the web app to download when done.

I solve my problem with dash-download-file-link-server as here:

1 Like

Hi i used your exact code but when i click the link i get the “500 Internal Server Error”. Have you ever encountered this before?

In case it helps, here’s an example of creating an Excel file w/ openpyxl, saving it to a stream vs. a file on disk, and sending to a user. A non-Pandas example : ) Also, using an export button.

import io
import dash
import dash_html_components as html
from flask import send_file
from openpyxl import Workbook


app = dash.Dash()
app.layout = html.Div(
    children=[html.A(id='export_link', href='/download_excel/', children=[html.Button(id='export_button', type='button', children=['Export'])])])


@app.server.route("/download_excel/")
def download_excel():
    workbook = Workbook()
    worksheet = workbook.active
    worksheet.cell(column=1, row=1, value='hi!')

    # save workbook to a stream (vs. saving to a file on disk) to later send to user
    excel_stream = io.BytesIO()
    workbook.save(excel_stream)
    excel_stream.seek(0)    # go to the beginning of the stream

    return send_file(
        excel_stream,
        mimetype = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
        attachment_filename="greeting.xlsx",
        as_attachment=True,
        cache_timeout=0
    )


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

FYI this option is built in to the DataTable as well, see http://dash.plotly.com/datatable/reference for all available options: dash_table.DataTable(export_format='xlsx')

This is an extremely useful feature @chriddyp! Thank you!

Is there any way to influence the Export button? (position - e.g. below table instead of above; button text; etc.)

It would be great to have a separate section called e.g. ‘Data export to csv, Excel’ in the DataTable documentation https://dash.plotly.com/datatable. So that it is the first place to land from google search ‘dash data table excel export’ :wink: