Black Lives Matter. Please consider donating to Black Girls Code today.

Download Raw Data - xls/xlsx

Hi All!

I’m trying to download raw data in xls or xlsx format, but can’t get it to work. In the community I can already find how to download raw data in csv-format, which works perfectly fine for me:

# In app.layout
html.A(
    'Download Data',
    id='download-link',
    download="rawdata.csv",
    href="",
    target="_blank"
)


@app.callback(
Output('download-link', 'href'),
[Input('dropdown-view-data', 'value')])
def update_download_button(value):
     #Create DF
     d = {'col1': [1, 2], 'col2': [3, 4]}
     df = pd.DataFrame(data=d)

     csv_string = df.to_csv(index=False, encoding='utf-8')
     csv_string = "data:text/csv;charset=utf-8,%EF%BB%BF" + urllib.parse.quote(csv_string)
     return csv_string

As I’m using Dash within Django (and not Flask), I can’t use the flask-route approach described in https://community.plotly.com/t/allow-users-to-dowload-an-excel-in-a-click/9410.

I think that changing the csv_string should return me the right file. I came up with the following with the help of Google:

# In app.layout
html.A(
    'Download Data',
    id='download-link',
    download="table.xls",
    href="",
    target="_blank"
)


@app.callback(
Output('download-link', 'href'),
[Input('dropdown-view-data', 'value')])
def update_download_button(value):
     #Create DF
     d = {'col1': [1, 2], 'col2': [3, 4]}
     df = pd.DataFrame(data=d)

     writer = pd.ExcelWriter('table.xls', engine='xlsxwriter')
     excel_data = df.to_excel(writer, sheet_name='Sheet1', index=False, encoding='base64')
     excel_data = "data:application/vnd.ms-excel;base64" + urllib.parse.quote(excel_data)
     return excel data

I have figured out how to save an Excel file on the server-side (by adding writer.save()), but I would like to have this on the client-side. Anyone any ideas if this is possible? Am I doing something wrong within excel_data?

Many thanks for your help!

1 Like

Does anyone know the answer? I tried for 1 hour as well, but cannot make this work either.

following works like a charm:

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 
2 Likes

Very nice,
How to add a filename to the output file ?
And also you call a variable “period” which is not set is it normal ?

To change filename, you’d need to Target field “download” of html.A component.
You can’t call variable a period, why you asked?

1 Like

I was talking about the sheetname option in your df.to_excel function but I guess this period variable was set in in the input callback or something.

Anyway, thank’s a lot it’s working for me .