Excel Writer to dcc.Download

[dash-beginner]

Hi Everyone!

I’m new to Dash and loving it so far! I was wondering whether it is possible to use a xlsxwriter.workbook.Workbook object with the dcc.Download component? I was looking at the docs and it seems pretty straightforward to create a single-page, single-dataframe example but I wasn’t sure how to go about sending this writer object over. I have the writer object created with something along the lines of the following code (apologies if I’m missing something because I rewrote my original code – the error message I got here and in my original code was “AttributeError: ‘XlsxWriter’ object has no attribute ‘name’”):

import dash
import dash_core_components as dcc
import dash_html_components as html
from dash.dependencies import Input, Output, State
import pandas as pd
import numpy as np
import xlsxwriter

app = dash.Dash(__name__)

app.layout = html.Div(
    children = [
        html.Button(
            id = 'export-button',
            children = 'Export',
        ),
        dcc.Download(
            id = "download-data"
        ),
    ]
)

@app.callback(
    Output('download-data', 'data'),
    Input('export-button', 'n_clicks')
)
def export_data(n_clicks):
    filename = 'placeholder.xlsx'

    df1 = pd.DataFrame(np.random.randint(0, 100, size = (17, 3)), columns = ['A', 'B', 'C'])
    df2 = pd.DataFrame(np.random.randint(0, 100, size = (17, 3)), columns = ['A', 'B', 'C'])
    df3 = pd.DataFrame(np.random.randint(0, 100, size = (17, 3)), columns = ['A', 'B', 'C'])


    writer = pd.ExcelWriter(filename, engine = 'xlsxwriter')
    df1.to_excel(writer, sheet_name = 'df1')
    df2.to_excel(writer, sheet_name = 'df2')
    df3.to_excel(writer, sheet_name = 'df3')
    
    # At this point, the writer has been created, but I don't know how to proceed to allowing this to interact with the dcc.Download component if even possible -- the rest of this is just conjecture...
    # This return statement was really just a hail-mary attempt
    return dcc.send_data_frame(writer, filename = filename)

if __name__ == '__main__':
    app.run_server(debug=True, port=8036)

If this method is along the wrong lines, I’m also more than happy to explore other options if anyone has suggestions!

Thanks in advance for the help!

[dash-beginner]

Just hoping to bump this post as I still am not sure how to go about doing this…

[Eduardo]

Hi @dash-beginner

I was looking into your issue and also trying to understand how pandas.ExcelWriter works.

As described in the following link, you can save the file without using any dcc.Download or other component, just direct with Python code.

https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.ExcelWriter.html

For an esy solution, I changed your code and add a no_update as return because you do not need to send anything to the dcc.Download, you can change all the callback and layout as you want. Here is the code:

import dash
import dash_core_components as dcc
import dash_html_components as html
from dash.dependencies import Input, Output, State
import pandas as pd
import numpy as np
import xlsxwriter
from dash.dash import no_update

app = dash.Dash(__name__)

app.layout = html.Div(
    children = [
        html.Button(
            id = 'export-button',
            children = 'Export',
        ),
        dcc.Download(
            id = "download-data"
        ),
    ]
)

@app.callback(
    Output('download-data', 'data'),
    Input('export-button', 'n_clicks')
)
def export_data(n_clicks):
    filename = 'placeholder.xlsx'

    df1 = pd.DataFrame(np.random.randint(0, 100, size = (17, 3)), columns = ['A', 'B', 'C'])
    df2 = pd.DataFrame(np.random.randint(0, 100, size = (17, 3)), columns = ['A', 'B', 'C'])
    df3 = pd.DataFrame(np.random.randint(0, 100, size = (17, 3)), columns = ['A', 'B', 'C'])

    with pd.ExcelWriter('placeholder.xlsx') as writer:
        df1.to_excel(writer, sheet_name = 'df1')
        df2.to_excel(writer, sheet_name = 'df2')
        df3.to_excel(writer, sheet_name = 'df3')
    
    # writer = pd.ExcelWriter(filename, engine = 'xlsxwriter')
    # df1.to_excel(writer, sheet_name = 'df1')
    # df2.to_excel(writer, sheet_name = 'df2')
    # df3.to_excel(writer, sheet_name = 'df3')
    
    # At this point, the writer has been created, but I don't know how to proceed to allowing this to interact with the dcc.Download component if even possible -- the rest of this is just conjecture...
    # This return statement was really just a hail-mary attempt
    return no_update # dcc.send_data_frame(writer, "mydf.csv")

if __name__ == '__main__':
    app.run_server(debug=True, port=8036)

And this is the file extracted:

[dash-beginner]

Hi Eduardo! I appreciate the reply . I have been able to personally download the Excel Writer object to my computer, but the desire to use dcc.Download was hoping that the user would be able to download data that they interact with to their own personal device for additional manipulation using Excel. I tried to find a way to do this outside of Dash but I’m thinking that a ‘hacky’ way of solving this might be to create a tempfile in conjunction with dcc.send_file that I see in the docs here, deleting the tempfile upon download? I’m a bit new to this so I’m not sure if that will work and would appreciate another set of eyes on it if possible - will try my hand at it and post my result with more questions / if it actually works.

[Eduardo]

Hi @dash-beginner

I think you can do that following my example.
Once you save the file using pd.ExcelWriter read it using Pandas read excel:
https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_excel.html

Then downlod to the user’s PC sending the file with dcc.Download.

Hope it works.

[dash-beginner]

Hi Eduardo – I think the below code covers the functionality that I needed it to (it might be a bit of over-engineering, so if you have any suggestions on how to keep the functionality while cleaning up the process, I would love to hear it) . I really appreciate your help on this!

import tempfile

import dash
import dash_core_components as dcc
import dash_html_components as html
from dash.dependencies import Input, Output, State
import pandas as pd
import numpy as np

app = dash.Dash(__name__)

app.layout = html.Div(
    children = [
        html.Button(
            id = 'export-button',
            children = 'Export',
        ),
        dcc.Download(
            id = "download-data"
        ),
    ]
)

@app.callback(
    Output('download-data', 'data'),
    Input('export-button', 'n_clicks'),
    prevent_initial_call = True
)
def export_data(n_clicks):
    with tempfile.TemporaryDirectory() as tmpdir:
        filename = 'placeholder.xlsx'
        
        df1 = pd.DataFrame(np.random.randint(0, 100, size = (17, 3)), columns = ['A', 'B', 'C'])
        df2 = pd.DataFrame(np.random.randint(0, 100, size = (17, 3)), columns = ['A', 'B', 'C'])
        df3 = pd.DataFrame(np.random.randint(0, 100, size = (17, 3)), columns = ['A', 'B', 'C'])

        with pd.ExcelWriter(f'{tmpdir}/placeholder.xlsx') as writer:
            df1.to_excel(writer, sheet_name = 'df1')
            df2.to_excel(writer, sheet_name = 'df2')
            df3.to_excel(writer, sheet_name = 'df3')

            return dcc.send_file(f'{tmpdir}/placeholder.xlsx')

if __name__ == '__main__':
    app.run_server(debug=True, port=8036)