Allow users to dowload an Excel in a click

Dash Python
23

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)
24

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')

25

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:

1 Like
26

I just spent some time strugelling to get my exel file exportation working. I figured I would share it here as it was not so obvious how to implement it for the beginner I am. I implemented what @chriddyp suggested with send_data_frame from dash_extensions https://pypi.org/project/dash-extensions/ :

import dash
import pandas as pd
from dash_extensions import Download
from dash_extensions.snippets import send_data_frame
import dash_html_components as html

app = dash.Dash(__name__)

app.layout = html.Div(
    children=[
         html.Button("Download Excel File", id='download-results-button'),
         Download(id='download')])

@app.callback(Output('download', 'data'),
             [Input('download-results-button', 'n_clicks')])
def download_data(n_clicks):
    d = {"col1": [1, 2], "col2": [3, 4]}
    df = pd.DataFrame(data=d)
    return send_data_frame(df.to_excel, filename='data.xlsx')

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

I hope it can help some other lost souls get to something running faster!

27

Thanks so much for this solution. It works perfectly on my multi callbacks project.

28

Just a follow up question on this cool topic. (Many of our colleagues still need excel reports).

For this reason I have created a python function with correct formatting where input is a dataframe and a dictionary. The function then create the excel document with two sheets, one with with data and a sheet with some graphs. I am using xlsxwriter to do all this. The question is if there is an easy way to implement this is an app?

29

Hi @jharm

It’s great that you created a solution. I’d be interested to see how you add a sheet with the graphs. Could you post a MWE?

Also, for downloading data (including in excel format) be sure to check out the new dcc.Download() component:
https://dash.plotly.com/dash-core-components/download

30

Here is a small example,

31

Hi @AnnMarieW,

sorry about the late reply.

I am not sure if I was completely clear. But what I use today is in a python script which I would like to move to Dash. This example creates a workbook an then fill in the data using xlsxwriter.

def performance_excel(df,test_info):
    name = test_info['output_name']
    workbook = xlsxwriter.Workbook('data/reports/{}.xlsx'.format(name)) 
    worksheet = workbook.add_worksheet('data') 
    worksheet_graph = workbook.add_worksheet('graph')

I use different formats

 # Formats used   	
    headline = workbook.add_format({
        'bold': True,
        'font_size': 18,
        'align': 'center',
        'border' : 2 })
    
    bold = workbook.add_format({
        'bold': True })
    
    border = workbook.add_format({
        'border': 2,
        'align': 'center',
        'valign': 'vcenter',
        'text_wrap': True})
    
    bold_large = workbook.add_format({
        'font_size': 14,
        'border': 2,
        'bold': True })

Then I fill out the data

### Data
    step_no = df['StepNo'].unique()
    row_st = 18
    
    for i in reversed(step_no):
        no_of_points = df['StepNo'][df['StepNo']==i].size
        worksheet.write('B{}'.format(row_st), i)
        try:
            q = df['Q [m3/h]'][df['StepNo']==i].values.round(2)
            worksheet.write_column('C{}'.format(row_st), q)
        except:
            pass
        try:
            h = df['H [m]'][df['StepNo']==i].values.round(2)
            worksheet.write_column('D{}'.format(row_st), h)
        except:
            pass

Then I make the graphs

# QH chart
    chart1 = workbook.add_chart({'type': 'scatter',
                                 'subtype': 'smooth'}) 
    chart1.add_series({ 
     	'categories': '=data!$C$18:$C${}'.format(row_st-2), 
     	 'values':	 '=data!$D$18:$D${}'.format(row_st-2), 
    	 'line': { 
      			'color': 'red', 
      			'width': 2, 
     			}, 
    })
    chart1.set_title({'name': 'QH Chart'}) 
    chart1.set_x_axis({'name': 'Q [m3/h]',
                       'major_gridlines': {
                           'visible': True,
                           'line':{'width': 1, 'dash_type': 'dash'}
                           }
                       }) 
    chart1.set_y_axis({'name': 'H [m]',
                       'major_gridlines': {
                           'visible': True,
                           'line':{'width': 1, 'dash_type': 'dash'}
                           }
                       })
    chart1.set_legend({'none': True})

And finally I close the workbook

   workbook.close()

What I have just learned is that maybe we can make the report function in Dash and then just use the pd to excel function for those who want the raw data. So right now I am on the lookout for a great report template which has a nice styling. Then I can present it to the managers, and hopefully convince them to go Dash enterprise :slight_smile:

32

Hi @Emil ,

I made your example work, but somehow I cannot make it work if I use the following as input instead of pd…ExcelWrtier()

 output = BytesIO()
 workbook = xlsxwriter.Workbook(output)
 worksheet = workbook.add_worksheet('data') 
 worksheet_graph = workbook.add_worksheet('graph')
 workbook.close()
 send_bytes(output, "some_name.xlsx")

I can see that the object type I get is <class ‘_io.BytesIO’> vs <class ‘pandas.io.excel._xlsxwriter._XlsxWriter’> which is the object type used in your example.

I use The Workbook Class — XlsxWriter Documentation as how to create the bytes_io object.

from io import BytesIO

output = BytesIO()
workbook = xlsxwriter.Workbook(output)
worksheet = workbook.add_worksheet()

worksheet.write('A1', 'Hello')
workbook.close()

xlsx_data = output.getvalue()
33

If you look at the example I linked, you can see that the argument to send_bytes is a function, not a BytesIO object. Hence your code should be something like,

def write_stuff(output):
    workbook = xlsxwriter.Workbook(output)
    worksheet = workbook.add_worksheet('data') 
    worksheet_graph = workbook.add_worksheet('graph')
    workbook.close()
send_bytes(write_stuff, "some_name.xlsx")

Since you example isn’t complete though, I can’t really test if it works.

34

Hi Emil,

I just realised that :slight_smile:

I works now, which is great :+1: I was a bit thrown off with this input as function. Is it possible to have several input to the function, I might want to have dataframe and a dictionary as input. Right now I define them before the function and then it works.