How to use save multiple df to excel file in streamlit

[QIN_DJ]

this code, that is cant work

thank u every one~

df1 = pd.DataFrame({'a': [1, 2, 3], 'b': [4, 5, 6]})
df2 = pd.DataFrame({'c': [7, 8, 9], 'd': [10, 11, 12]})


def dfs_to_excel(df_list, sheet_list, file_name):
    output = BytesIO()
    writer = pd.ExcelWriter(file_name, engine='xlsxwriter')
    
    for dataframe, sheet in zip(df_list, sheet_list):
        dataframe.to_excel(writer, sheet_name=sheet, startrow=0, startcol=0)
    writer.close()

    processed_data = output.getvalue()
    return processed_data

dfs = [df1, df2]
sheets = ['Sheet1', 'Sheet2']
file_name = 'dataaaaaaaaaaa.xlsx'

df_excel = dfs_to_excel(dfs, sheets, file_name)
print(df_excel)
st.download_button(label="Download Excel", data=df_excel, file_name='databbbbbb.xlsx', mime='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
 

[Guna_Sekhar_Venkata]

Hi @QIN_DJ . I can give a idea of how to download dataframe as a excel.

def get_table_download_link(df):
    excel_writer = BytesIO()
    df.to_excel(excel_writer)
    excel_writer.seek(0)
    b64 = base64.b64encode(excel_writer.read()).decode()
    href = f'<a href="data:file/csv;base64,{b64}" download="answers.xlsx">Download Answers as Excel Sheet</a>'
    return href

st.markdown(get_table_download_link(dataframe_variable_name), unsafe_allow_html=True)

Hope it works.
Happy Streamlit-ing

[QIN_DJ]

thank u so much it works.
But, not the effect I needed.
I want it to be able to put the two df’s in separate sheets of the same excel

like this img

sheet1

sheet2

[QIN_DJ]

How to putting multiple dataframes on separate tabs/worksheets

[Goyo]

The pandas docs on .to_excel() have an example.

>>> with pd.ExcelWriter('output.xlsx') as writer:  
...     df1.to_excel(writer, sheet_name='Sheet_name_1')
...     df2.to_excel(writer, sheet_name='Sheet_name_2')

[QIN_DJ]

thank u every one again！

def test_dfs_to_excel(df1, df2):
    excel_writer = BytesIO()
    with pd.ExcelWriter(excel_writer) as writer:
        df1.to_excel(writer, sheet_name='Sheet1_1')
        df2.to_excel(writer, sheet_name='Sheet2_2')
    excel_writer.seek(0)
    b64 = base64.b64encode(excel_writer.read()).decode()
    href = f'<a href="data:file/csv;base64,{b64}" download="answers.xlsx">Download test_dfs_to_excel</a>'
    return href


st.markdown(test_dfs_to_excel(df1, df2), unsafe_allow_html=True)