Plotly Dash Table: How to export formatted excel files from the dash table

Question

This example script for dash table:

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

app = dash.Dash(__name__)
app.layout = html.Div([
    html.Button("Download Excel", id="btn_xlsx"),
    dcc.Download(id="download-dataframe-xlsx"),
])


df = pd.DataFrame({"a": [1, 2, 3, 4], "b": [2, 1, 5, 6], "c": ["x", "x", "y", "y"]})


@app.callback(
    Output("download-dataframe-xlsx", "data"),
    Input("btn_xlsx", "n_clicks"),
    prevent_initial_call=True,
)
def func(n_clicks):
    return dcc.send_data_frame(df.to_excel, "mydf.xlsx", sheet_name="Sheet_name_1")


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

This script how to export formatted excel file from pandas dataframe:

"""
Show examples of modifying the Excel output generated by pandas
"""
import pandas as pd
import numpy as np

from xlsxwriter.utility import xl_rowcol_to_cell


df = pd.read_excel("../in/excel-comp-datav2.xlsx")

# We need the number of rows in order to place the totals
number_rows = len(df.index)

# Add some summary data using the new assign functionality in pandas 0.16
df = df.assign(total=(df['Jan'] + df['Feb'] + df['Mar']))
df = df.assign(quota_pct=(1+(df['total'] - df['quota'])/df['quota']))

# Create a Pandas Excel writer using XlsxWriter as the engine.
# Save the unformatted results
writer_orig = pd.ExcelWriter('simple.xlsx', engine='xlsxwriter')
df.to_excel(writer_orig, index=False, sheet_name='report')
writer_orig.save()

# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter('fancy.xlsx', engine='xlsxwriter')
df.to_excel(writer, index=False, sheet_name='report')

# Get access to the workbook and sheet
workbook = writer.book
worksheet = writer.sheets['report']

# Reduce the zoom a little
worksheet.set_zoom(90)

# Add a number format for cells with money.
money_fmt = workbook.add_format({'num_format': '$#,##0', 'bold': True})

# Add a percent format with 1 decimal point
percent_fmt = workbook.add_format({'num_format': '0.0%', 'bold': True})

# Total formatting
total_fmt = workbook.add_format({'align': 'right', 'num_format': '$#,##0',
                                 'bold': True, 'bottom':6})
# Total percent format
total_percent_fmt = workbook.add_format({'align': 'right', 'num_format': '0.0%',
                                         'bold': True, 'bottom':6})

# Format the columns by width and include number formats

# Account info columns
worksheet.set_column('B:D', 20)
# State column
worksheet.set_column('E:E', 5)
# Post code
worksheet.set_column('F:F', 10)

# Monthly columns
worksheet.set_column('G:K', 12, money_fmt)
# Quota percent columns
worksheet.set_column('L:L', 12, percent_fmt)

# Add total rows
for column in range(6, 11):
    # Determine where we will place the formula
    cell_location = xl_rowcol_to_cell(number_rows+1, column)
    # Get the range to use for the sum formula
    start_range = xl_rowcol_to_cell(1, column)
    end_range = xl_rowcol_to_cell(number_rows, column)
    # Construct and write the formula
    formula = "=SUM({:s}:{:s})".format(start_range, end_range)
    worksheet.write_formula(cell_location, formula, total_fmt)

# Add a total label
worksheet.write_string(number_rows+1, 5, "Total",total_fmt)
percent_formula = "=1+(K{0}-G{0})/G{0}".format(number_rows+2)
worksheet.write_formula(number_rows+1, 11, percent_formula, total_percent_fmt)

# Define our range for the color formatting
color_range = "L2:L{}".format(number_rows+1)

# Add a format. Light red fill with dark red text.
format1 = workbook.add_format({'bg_color': '#FFC7CE',
                               'font_color': '#9C0006'})

# Add a format. Green fill with dark green text.
format2 = workbook.add_format({'bg_color': '#C6EFCE',
                               'font_color': '#006100'})

# Highlight the top 5 values in Green
worksheet.conditional_format(color_range, {'type': 'top',
                                           'value': '5',
                                           'format': format2})

# Highlight the bottom 5 values in Red
worksheet.conditional_format(color_range, {'type': 'bottom',
                                           'value': '5',
                                           'format': format1})

writer.save()

Is there any way to export formatted excel files from the dash table? It seems to need to be replaced in dcc.send_data_frame (df.to_excel) to dcc.send_data_frame (writer.save()) but this way doesn't work...

user_5812408 · Answer

Based on this answer on the Plotly forum, you need to use dcc.send_bytes. There are some other options listed there, but if you are going to do conditional formatting and don't want to save a file to disk, you could do:

import dash
from dash.dependencies import Output, Input
from dash import dcc, html
import pandas as pd
import io

app = dash.Dash(__name__)
app.layout = html.Div([
    html.Button("Download Excel", id="btn_xlsx"),
    dcc.Download(id="download-dataframe-xlsx"),
])


df = pd.DataFrame({"a": [1, 2, 3, 4], "b": [2, 1, 5, 6], "c": ["x", "x", "y", "y"]})


@app.callback(
    Output("download-dataframe-xlsx", "data"),
    Input("btn_xlsx", "n_clicks"),
    prevent_initial_call=True,
)
def func(n_clicks):
    output = io.BytesIO()
    writer = pd.ExcelWriter(output, engine='xlsxwriter')

    # do your formatting here

    df.to_excel(writer, sheet_name='Sheet_name_1', index=False)  # writes to BytesIO buffer
    writer.save()
    data = output.getvalue()
    return dcc.send_bytes(data, 'mydf.xlsx')


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

Tested with Python 3.10 and

dash==2.3.0
dash-bootstrap-components==1.0.3
dash-bootstrap-templates==1.0.5
dash-core-components==2.0.0
dash-html-components==2.0.0
dash-table==5.0.0

Plotly Dash Table: How to export formatted excel files from the dash table

1 Answers