Download Button with Excel File

🎈 Using Streamlit
1

I’m working on an app that allows for information to be downloaded to a Microsoft Excel file. I use XLSXWriter (https://xlsxwriter.readthedocs.io/) to create a file. Using inputs from Streamlit, the app I have created then downloads (or at least I am hoping it will) the excel file for the user. XLSXWriter returns a:
<class ‘xlsxwriter.workbook.Workbook’>
I am using the download button with the following code:
st.download_button(label=‘Export to Excel’
, data = excel_file # excel file is the name of the downloaded <class ‘xlsxwriter.workbook.Workbook’>
, file_name= ‘df_test.xlsx’
, mime = ‘application/vnd.openxmlformats-officedocument.spreadsheetml.sheet’)
Any help on how to make this work would be greatly appreciated.

2

Hi @user-3135, welcome to the Streamlit community!! :wave: :partying_face:

I’ve shown how to use xlsxwriter, pandas, and st.download_button to download an Excel file here: How to add a download excel/csv function to a button? - #16 by snehankekre

Best, :balloon:
Snehan

3

Thank You. I’ve gone over that. I poorly explained where my holdup is. I don’t have a dataframe that writes. A function takes in parameters and exports an excel sheet. The users for my app care about formating.

I wrote a function that makes a pretty excel sheet with xlsxwriter. It is super long (2000+ lines), but for broad strokes purposes the function does the following:
def write_excel(file_path, string_input):
workbook = xlsxwriter.Workbook(file_path)
worksheet = workbook.add_worksheet()
worksheet.write(‘A1’, string_input)
workbook.close()
My end goal in streamlit is to call said function like so:
excel_file = write_excel(’/file_location’, ‘hello world!’)

I am having trouble getting that (the pretty excel file) to work with my Streamlit app.

Thank you for welcoming me to the Streamlit community! So far everything has been AMAZING!

4

I am having trouble getting that (the pretty excel file) to work with my Streamlit app.

By trouble you mean you’ve not been able to use st.download_button to download the Excel file?

5

That is correct. Currently, my function returns a <class ‘xlsxwriter.workbook.Workbook’>

If I put that in the data part of the download button, I’ll get an error that says": Invalid binary data format: <class ‘xlsxwriter.workbook.Workbook’>"

Right now I’m trying to figure out how to tie the buffer = io.BytesIO() to the xlsxwriter function; however, I’m a novice at this so I’m doing a lot of reading at the moment. The goal would be to convert the Workbook to a binary data format.

1 Like
6

I’ll offer a quick update. I changed my approach. I now specify the path when I create the file rather than try to use the download_button. This isn’t ideal; however, I’ll choose working solutions over temporary solutions. Anyway. That works when I run my app on the local machine. When I connect to the app on Streamlit’s servers, I cannot get the file to appear anywhere on my computer. I’m working to fix that problem now. I do not know entirely how this works so I have more reading to do the rest of tonight and tomorrow.

7

Hi @user-3135 :wave:

Here’s a reproducible example that uses st.download_button to download an Excel workbook written to an in-memory string with BytesIO:

import streamlit as st
import xlsxwriter
from io import BytesIO

output = BytesIO()

# Write files to in-memory strings using BytesIO
# See: https://xlsxwriter.readthedocs.io/workbook.html?highlight=BytesIO#constructor
workbook = xlsxwriter.Workbook(output, {'in_memory': True})
worksheet = workbook.add_worksheet()

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

st.download_button(
    label="Download Excel workbook",
    data=output.getvalue(),
    file_name="workbook.xlsx",
    mime="application/vnd.ms-excel"
)

You should be able to adapt this example to your use-case. :slightly_smiling_face: Let us know if you run into roadblocks!

Happy Streamlit-ing! :balloon:
Snehan

2 Likes
8

That worked! Thank You SO MUCH!!! You saved me hours of deep diving into the internet to fix this thing!

1 Like
9

Great!!

Worked for me too…

This saved me a lot of time.

Thank You,

Streamlit is Amazing

1 Like
10

thank u first ! but I’m confused about when will the data in memory be cleared? or how to clear?

11

Could you clarify what you want to clear? If you mean the Python IO Stream:

12

yes, I mean the python is stream! :smiling_face_with_three_hearts:thank you very much!!

13

Hi, this works great for me locally. But when I deploy to azure cloud I get “the site can’t be reached” error. I am able to download another pickled file in a similar way an a pdf by generating a link, so I don’t think it has to do with az cloud restrictions.
Any ideas on how to solve this? Best regards