Multiple dataframes to specific excel sheets

Using Streamlit
,
1

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!

2

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:

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

1 Like
3

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

4

Thank you so much!

5

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

6

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",
    )
7

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()

8

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}")
1 Like
9

This topic was automatically closed 365 days after the last reply. New replies are no longer allowed.