Multiple dataframes to specific excel sheets

[sehitkaradag]

Hi all,

My streamlit application has six dataframes. I want to have Streamlit download all dataframes in one excel file and get each dataframe a seperate sheet.

So for example;
df1 = excel sheet 1
df2 = excel sheet 2

So far I have only been succesful to download one df to excel. Could somebody help me out here?

Help is much appreciated!

[mathcatsand]

This sounds like a pandas question. The function to read excel files in pandas has a parameter to specify the sheet for extraction. You can specify it by name (a string) or an index (an integer). If not specified, it defaults to 0, which means it reads in the first sheet.
https://pandas.pydata.org/docs/reference/api/pandas.read_excel.html

There are a couple examples on StackExchange here:

Using Pandas to pd.read_excel() for multiple worksheets of the same workbook

Edit: I just realized from re-reading the post title that I may have interpreted your question in reverse. That being the case, there is that functionality in reverse.
https://pandas.pydata.org/docs/reference/api/pandas.ExcelWriter.html#pandas.ExcelWriter

[snehankekre]

This question has been previously answered by me with a working solution:

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

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]})…

[sehitkaradag]

Thank you so much!

[Navish_Khadooa]

This is really cool. But is it possible to download the excel file without the row indices in the first column?

[snehankekre]

That’s really a Pandas question. I’d suggest reading through the pandas.DataFrame.to_excel docs:

import streamlit as st
import pandas as pd
import io

buffer = io.BytesIO()

df1 = pd.DataFrame([["a", "b"], ["c", "d"]], columns=["col1", "col2"])

with pd.ExcelWriter(buffer, engine="xlsxwriter") as writer:
    df1.to_excel(writer, sheet_name="Sheet1", index=False)
    writer.save()

    st.download_button(
        label="Download Excel worksheet without index",
        data=buffer,
        file_name="df1.xlsx",
        mime="application/vnd.ms-excel",
    )

[haider_ali]

streamlit not allowing to write multiple excel file at a time. i have seven dataframe in a file. when i ran on jupyter notebook i get seven sheets. When i ran this on streamlit it gave just one sheet
code:
for i, df in enumerate(finalname):
df.to_excel(w, sheet_name=f’sheet{i}')
w.save()

[Goyo]

Please format your code properly and include code that defines all variables. finalname and w are undefined and the behavior depends on how you define them.

This code will save a workbook with seven sheets, no matter if you run it in a strealit app or not.

import pandas as pd
import streamlit as st


finalname = [pd.DataFrame()] * 7

with pd.ExcelWriter("df.xlsx") as w:
    for i, df in enumerate(finalname):
        df.to_excel(w, sheet_name=f"sheet{i}")