Error with download dataframe as excel file

Using Streamlit
1

I have a python that read files and convert it to dataframe using python and streamlit than i want to create a function to allow the user to download this dataframe as excel file with extension xls .

code:

import pandas as pd 
import streamlit as st

writer=pd.ExcelWriter('update2.xlsx')
        df.to_excel(writer, index = False, header=True,encoding='utf-8')
        with open(writer,'rb') as f:
            b64 = base64.b64encode(f.read())
            href = f'<a href="data:file/xls;base64,{b64}" download="new_file.{extension}">Download {extension}</a>'

    st.write(href, unsafe_allow_html=True)

When i try to download the file using a link the file doesn’t download and display this error:

Failed-Network error

where is the error in my code ??

2

Hi @leb_dev,

I was able to create a working example with the help of these two stackoverflow posts:

  1. Setting mime type for excel document - Stack Overflow
  2. java - How to decode an encoded excel file using python - Stack Overflow
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) # write to BytesIO buffer
towrite.seek(0)  # reset pointer
b64 = base64.b64encode(towrite.read()).decode() 
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)

download-excel
download-excel2560Ă—1440 1.45 MB

Let us know if this works for you :slight_smile:

Happy Streamlit-ing! :balloon:
Snehan

1 Like
3

This is very handy and thank you.

I have put the above code into a function. So I want to create multiple links for different dfs. My code downloads the files with new filenames(correct) but is using the same dataframe(the last one referenced).

I am guessing that the io.BytesIO() lines need to be changed. But I am not sure how.

Any suggestions?

Thanks

Richard

def create_download_link(df, dfname):
towrite = io.BytesIO()
downloaded_file = df.to_excel(towrite, encoding='utf-8', index=False, header=True) # write to BytesIO buffer
towrite.seek(0)  # reset pointer
b64 = base64.b64encode(towrite.read()).decode() 
fn = dfname + '.xlsx'
#linko= f'<a href="data:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;base64,{b64}" download="myfilename.xlsx">Download excel file</a>'
linko= f'<a href="data:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;base64,{b64}" download=' + fn + '>' + fn + '</a>'
return(linko)

st.markdown(create_download_link(df, "df"), unsafe_allow_html=True)
st.markdown(create_download_link(df2, "df2"), unsafe_allow_html=True)
4

@Richard_Fitzsimons Suggest you close() the towrite stream after the b64 encoding.

      :
   b64 = base64.b64encode(towrite.read()).decode() 
   towrite.close()
      :
1 Like
5

Thanks for your answer. In fact the problem was elsewhere. I had made a shallow copy of my dataframe (for testing) : df2=df1
When I modified df2, df1 was also changed.
I changed to
df2 = df1.copy(deep=True)

Now I have an independent df and everything works.
Currently there is no difference if I add in towrite.close() or not.

Cheers

Richard

1 Like