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

Very helpful, I have a requirement to download a zip folder. How may I achieve that using the above code?

Thanks.
But what about excel files?

Hi @Alias4D,

I extended the above code snippet to download .xlsx excel files with the help of two stackoverflow posts:

  1. java - How to decode an encoded excel file using python - Stack Overflow
  2. Setting mime type for excel document - Stack Overflow

Here’s a working example and what it looks like:

import streamlit as st
import pandas as pd
import base64
import io

vals= ['A','B','C']
df= pd.DataFrame(vals, columns=["Title"])
df

towrite = io.BytesIO()
downloaded_file = df.to_excel(towrite, encoding='utf-8', index=False, header=True)
towrite.seek(0)  # reset pointer
b64 = base64.b64encode(towrite.read()).decode()  # some strings
linko= f'<a href="data:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;base64,{b64}" download="myfilename.xlsx">Download excel file</a>'
st.markdown(linko, unsafe_allow_html=True)

Does this help with your use case?

Best, :balloon:
Snehan

5 Likes

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

8 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