How to add a download excel/csv function to a button?

Thanks very much for your attention and time.
:pray:

1 Like

Hi mate, is there a way to customize the link where the user will click? Like the image below…

Download excel file

Hi @feliperoque, certainly! I’ll refer you to this thread by @Chad_Mitchell who creates a download button with custom CSS:

  1. A download button with custom CSS - #13 by jrieke
  2. A download function and examples app for Streamlit · GitHub

Using Chad’s function in the code from this thread:

import streamlit as st
import pandas as pd
import io

import base64
import os
import json
import pickle
import uuid
import re


def download_button(object_to_download, download_filename, button_text, pickle_it=False):
    """
    Generates a link to download the given object_to_download.
    Params:
    ------
    object_to_download:  The object to be downloaded.
    download_filename (str): filename and extension of file. e.g. mydata.csv,
    some_txt_output.txt download_link_text (str): Text to display for download
    link.
    button_text (str): Text to display on download button (e.g. 'click here to download file')
    pickle_it (bool): If True, pickle file.
    Returns:
    -------
    (str): the anchor tag to download object_to_download
    Examples:
    --------
    download_link(your_df, 'YOUR_DF.csv', 'Click to download data!')
    download_link(your_str, 'YOUR_STRING.txt', 'Click to download text!')
    """
    if pickle_it:
        try:
            object_to_download = pickle.dumps(object_to_download)
        except pickle.PicklingError as e:
            st.write(e)
            return None

    else:
        if isinstance(object_to_download, bytes):
            pass

        elif isinstance(object_to_download, pd.DataFrame):
            #object_to_download = object_to_download.to_csv(index=False)
            towrite = io.BytesIO()
            object_to_download = object_to_download.to_excel(towrite, encoding='utf-8', index=False, header=True)
            towrite.seek(0)

        # Try JSON encode for everything else
        else:
            object_to_download = json.dumps(object_to_download)

    try:
        # some strings <-> bytes conversions necessary here
        b64 = base64.b64encode(object_to_download.encode()).decode()

    except AttributeError as e:
        b64 = base64.b64encode(towrite.read()).decode()

    button_uuid = str(uuid.uuid4()).replace('-', '')
    button_id = re.sub('\d+', '', button_uuid)

    custom_css = f""" 
        <style>
            #{button_id} {{
                display: inline-flex;
                align-items: center;
                justify-content: center;
                background-color: rgb(255, 255, 255);
                color: rgb(38, 39, 48);
                padding: .25rem .75rem;
                position: relative;
                text-decoration: none;
                border-radius: 4px;
                border-width: 1px;
                border-style: solid;
                border-color: rgb(230, 234, 241);
                border-image: initial;
            }} 
            #{button_id}:hover {{
                border-color: rgb(246, 51, 102);
                color: rgb(246, 51, 102);
            }}
            #{button_id}:active {{
                box-shadow: none;
                background-color: rgb(246, 51, 102);
                color: white;
                }}
        </style> """

    dl_link = custom_css + f'<a download="{download_filename}" id="{button_id}" href="data:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;base64,{b64}">{button_text}</a><br></br>'

    return dl_link


vals= ['A','B','C']
df= pd.DataFrame(vals, columns=["Title"])
df
filename = 'my-dataframe.xlsx'
download_button_str = download_button(df, filename, f'Click here to download {filename}', pickle_it=False)
st.markdown(download_button_str, unsafe_allow_html=True)

image

Best, :balloon:
Snehan

4 Likes

I had a problem with this function while i was trying to convert a csv to excel. Didn’t worked.
Could i create just this part:

button_uuid = str(uuid.uuid4()).replace('-', '')
    button_id = re.sub('\d+', '', button_uuid)

    custom_css = f""" 
        <style>
            #{button_id} {{
                display: inline-flex;
                align-items: center;
                justify-content: center;
                background-color: rgb(255, 255, 255);
                color: rgb(38, 39, 48);
                padding: .25rem .75rem;
                position: relative;
                text-decoration: none;
                border-radius: 4px;
                border-width: 1px;
                border-style: solid;
                border-color: rgb(230, 234, 241);
                border-image: initial;
            }} 
            #{button_id}:hover {{
                border-color: rgb(246, 51, 102);
                color: rgb(246, 51, 102);
            }}
            #{button_id}:active {{
                box-shadow: none;
                background-color: rgb(246, 51, 102);
                color: white;
                }}
        </style> """

and them insert into this one:

link_to_donwload = custom_css + f'<a id="{button_id} href="data:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;base64,{b64}" download="cg_data.xlsx">Donwload Cg Spreadsheet Data</a>'

@feliperoque I edited my previous answer such that it converts a dataframe to .xlsx instead of .csv :slight_smile:

2 Likes

Thx you very much, i was looking for this my whole night rsrs.

1 Like

Thanks for the useful replies and cross post linking everyone!

I got my Excel file download working with a combination of openpyxl, using wb.save("test.xlsx") to save it to the current app dir and then jamming that file into:
file = f'<a href="test.xlsx" download="test.xlsx">Download excel file</a>'
and using st.markdown(file, unsafe_allow_html=True) to actually show a download link.

I use a regular st.button to generate the new Excel file and then the markdown link to download gets generated underneath it.

I wish Streamlit would add a great looking download button like the one that you posted feliperoque :slight_smile:
It’s a bit weird that we’ve had the file_uploader widget for quite some time but no nice button to download files(and no way to link a regular st.button to download a file).

Might have to find some time to learn how those UI components are built in Streamlit so I can try to build one.

1 Like

Starting with v0.88 now have st.download_button natively built into Streamlit. Check out the release notes and demo: 0.88.0 Release Notes

2 Likes

Hi @snehankekre, thanks a lot for this solution. Can you please help me with understadning, how to add multiple sheets in the excel file and then download that. With your current code, I checked, only one sheet is getting added. Thanks

Hi @A2-Response,

Certainly. I’ll show you how to use st.download_button to do the job. You’ll have to install the xlsxwriter package and/or add it to your requirements file.

I’ll use xlsxwriter's official example: Pandas Excel with multiple dataframes and an example from the pandas docs to write the Excel file to RAM:

Code

import streamlit as st
import pandas as pd
import io

buffer = io.BytesIO()

# Create some Pandas dataframes from some data.
df1 = pd.DataFrame({'Data': [11, 12, 13, 14]})
df2 = pd.DataFrame({'Data': [21, 22, 23, 24]})
df3 = pd.DataFrame({'Data': [31, 32, 33, 34]})

# Create a Pandas Excel writer using XlsxWriter as the engine.
with pd.ExcelWriter(buffer, engine='xlsxwriter') as writer:
    # Write each dataframe to a different worksheet.
    df1.to_excel(writer, sheet_name='Sheet1')
    df2.to_excel(writer, sheet_name='Sheet2')
    df3.to_excel(writer, sheet_name='Sheet3')

    # Close the Pandas Excel writer and output the Excel file to the buffer
    writer.save()

    st.download_button(
        label="Download Excel worksheets",
        data=buffer,
        file_name="pandas_multiple.xlsx",
        mime="application/vnd.ms-excel"
    )

Output

pandas-multiple

Hope this helps!

Happy Streamlit-ing, :balloon:
Snehan

7 Likes

Thanks very much, https://media.tenor.com/images/532b2d7d95f00ab4d4da4522cfbd857f/tenor.gif

1 Like

Hi,

Thank you very much for your code. I do have a question regarding the buffer.
I am trying to have 6 streamlit download buttons. Each button has 5 dataframes that it needs to save.
The first download button does the job well. But the second download button with 5 other dataframes doesn’t work properly. It still shows data from the previous button. Could you help me out what is going on here?

Do I have to write a function to reset the buffer?

Many thanks in advance!

@sehitkaradag Without a reproducible code snippet, I’m afraid I can’t be of much help. Using Streamlit: how to post a question in the Streamlit forum

You’ll need to create a new buffer and writer per download button. So, in your case 6 buffers and 6 writers. Do this by copying the code shown by @snehankekre multiple times, or with a bit more organization, you can wrap it in an object/class and reuse the class 6 times.

2 Likes

That solved it! Thank you so much!

Why do you need to have the st.download_button function inside with pd.ExcelWriter(…) as writer: ?
Is buffer not stored in memory so you can separate these functions?

1 Like

Yes, it works fine if you put the download button outside of the ExcelWriter context manager

Ok. I’m asking since I manage to do this perfectly locally but when I put it on azure cloud i get “page doesnt exist”. I have set the privileges of the app users and am able to download other pickled files and pdf-files, so I don’t think it’s a security issue. I was thinking there was something going on with what was stored in memory. I also tried to write explicitly to a file, opened the file and used the download_button and then removed the file. That also worked locally but not on cloud. Will now try the link approach (on button click - create a link to download your file), but am just blindly trying out stuff since I don’t understand what’s going on. Any ideas?

 isim= 'Analsed_Data.csv'
 indir = df_analiz_download.to_csv(index=False)
 b64 = base64.b64encode(indir.encode(encoding='ISO-8859-1')).decode(encoding='ISO-8859-1')  
 linko_final= f'<a href="data:file/csv;base64,{b64}" download={isim}>Download Analysed Data</a>'
 st.markdown(linko_final, unsafe_allow_html=True)  

this is how i have been using it on streamlit cloud
it is an hyperlink, not a button.
it just works fine and when clicked, it downloads dataframe in csv file format.

1 Like

This is what I do now that works on cloud, hope it might help someone else.
I’ve taken inspiration from all the different threads on the topic, and I create a button and when the user clicks the button a download link appears.

import base64
import io
import pandas as pd

def create_financial_report(df):
    output = io.BytesIO()
    writer = pd.ExcelWriter(output, engine='xlsxwriter')

    workbook = writer.book
    worksheet = workbook.add_worksheet('worksheet name')
    worksheet.write('A1', 'title of document')

    df.to_excel(writer, sheet_name='worksheet name', startrow=2, startcol=0)

    writer.close()

    return output.getvalue()

def get_xlsx_download_link(df):
    val = create_financial_report(df)
    b64 = base64.b64encode(val)
    return f'<a href="data:application/octet-stream;base64,{b64.decode()}" download="Financial_report.xlsx">Download file</a>'

df = pd.DataFrame(columns = ('a', 'b'), data = ([1, 2], [3,4]))
export_financial_report = export_area.button('Generate financial report')

if export_financial_report:
    export_area.markdown(output.get_xlsx_download_link(df), unsafe_allow_html=True)