Allow users to dowload an Excel in a click

Hi,
Is there an easy way to allow users to download an Excel? I tried to put:

 html.A("Download Excel", href="test.xlsx", download="test.xlsx")

but what I get in the dowloaded document is actually HTML code trying load the page.

If i put:

  html.A("Download Excel", href="test.xlsx")

and I click the component in the page it reloads itself but with /test.xlsx. However, in a normal .html document when I write

 <a href="test.xlsx"> Download Excel </a>

I get the Excel document properly.

Any ideas?

Note: the test.xlsx is in the same directory as the dash script

See Allowing users to download CSV on click

2 Likes

Thx! It works like a charm!

For other people that may be interested this is what I did (Im converting a DataFrame into an Excel):

import dash
from dash.dependencies import Input, Output, State
import dash_core_components as dcc
import dash_html_components as html
import dash_table_experiments as dte
from flask import send_file
import io
import flask
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
    strIO = io.BytesIO()
    excel_writer = pd.ExcelWriter(strIO, engine="xlsxwriter")
    df.to_excel(excel_writer, sheet_name="sheet1")
    excel_writer.save()
    excel_data = strIO.getvalue()
    strIO.seek(0)

    return send_file(strIO,
                     attachment_filename='test.xlsx',
                     as_attachment=True)

if __name__ == '__main__':
    app.run_server(debug=True)
8 Likes

@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
three reasons why you should buy plotly pro: support open source, get great support, host your plots and dashboards online