Unable to convert dataframe to excel file

🎈 Using Streamlit
1

Summary

I’m trying to create an app, that converts the resulting data frame (df) into an Excel file. The program is working fine in jupyter notebook. However, when integrating this into Streamlit, I am encountering an issue where the program appears to revert to a previous step.

Steps to reproduce

Code snippet:

if df is not None:
file_name = st.text_input('Enter a file name for the Excel file (e.g., email_data.xlsx)')
download = st.download_button(label='Download Excel', data=df.to_excel(index=False, header=True), key='download')

if file_name and download:
    with open(file_name, "wb") as f:
        f.write(download)
    st.success(f"File '{file_name}' has been downloaded successfully.")

Expected behavior:

It should create an excel file.

Actual behavior:

It’s getting back to the previous step and not responding to the download function.

2

Hi @Sidra_Tul_Muntaha, see if this is what you want:

import streamlit as st
import pandas as pd
from io import BytesIO

df=pd.DataFrame({'Num_col': [1000, 2000, 3000], 'Comma_col': [4000, 5000, 6000], 'PC_col': [18, 83, 64]})
st.dataframe(df)

flnme = st.text_input('Enter Excel file name (e.g. email_data.xlsx)')
if flnme != "":
    if flnme.endswith(".xlsx") == False:  # add file extension if it is forgotten
        flnme = flnme + ".xlsx"

    buffer = BytesIO()
    with pd.ExcelWriter(buffer, engine='xlsxwriter') as writer:
        df.to_excel(writer, sheet_name='Report')

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

Cheers

2 Likes
3

Hi ! You can refer this code below if maybe helpful.

import streamlit as st
import pandas as pd

df = pd.read_csv("dir/file.csv")

@st.experimental_memo
def convert_df(df):
   return df.to_csv(index=False).encode('utf-8')


csv = convert_df(df)

st.download_button(
   "Press to Download",
   csv,
   "file.csv",
   "text/csv",
   key='download-csv'
)

use .to_csv insted to excel
if you want your data to be exported into pure excel format then consider using other python lib like

open_xlsb
xlsxwriter

Any question ? plz be free to ask

2 Likes
5

Hi @Shawn_Pereira, this isn’t working. This is responding in the same way as the provided code. Any other solution would be really appreciated.
Thanks.

6

Hi @inline-arc, this code is working, however i would appreciate if u could help me download the file in .xlsx format.
Thanks.

7

For me, that code shows a download button after filling in a file name. Clicking the download button downloads the dataframe as an excel file. If that is not what you want please clarify.

Your code cannot possibly work because df.to_excel(index=False, header=True) will raise a TypeError.The first parameter to DatFrame.to_excel must be:

excel_writer : path-like, file-like, or ExcelWriter object

File path or existing ExcelWriter.

1 Like
8

Thanks @Goyo

@Sidra_Tul_Muntaha, the code works perfectly on my computer. I am using the latest version on Streamlit

You need to provide your file name and then click on the download button. If you want, I can upload a GIF file displaying its working… Do copy-past only my code into a new .py file and check the same at your end.

Cheers

9

It will be awesome if you can share any source to your code something like github repo like or streamlit project link aside

there is how you can covert it into excel you have to import xlsxwriter

pip install xlxswriter 

import 

df = pd.DataFrame(data)

# download button to download dataframe as xlsx

with pd.ExcelWriter(buffer, engine='xlsxwriter') as writer:
    # Write each dataframe to a different worksheet.
    df.to_excel(writer, sheet_name='Sheet1', index=False)

    download2 = st.download_button(
        label="Download data as Excel",
        data=buffer,
        file_name='large_df.xlsx',
        mime='application/vnd.ms-excel'
    )