Build a Streamlit App to Upload and Download Excel Files

Many Excel automation scripts end up used by people who do not run Python: a colleague who wants the monthly summary without asking you for it, a team that needs one cleaning step applied to a file. A small web app solves that, and Streamlit turns a script into one without HTML or JavaScript. The app below uploads a workbook, lets the user pick a sheet and columns, shows a summary and offers it as a new .xlsx. This guide, part of Serving Excel Files from Python Web Apps, also covers what makes such apps feel slow and how to test them. Every behaviour was checked with Streamlit's AppTest harness.

Prerequisites

pip install streamlit pandas openpyxl pytest

Run the app with streamlit run app.py; it opens in a browser at http://localhost:8501.

Step 1 — Put the Excel logic in plain functions

Keep reading, processing and writing in ordinary functions that know nothing about the page. They are easier to test, and the same functions can serve a command-line script:

# app.py
import io

import pandas as pd
import streamlit as st

XLSX = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"

@st.cache_data(show_spinner=False)
def read_workbook(data: bytes) -> dict[str, pd.DataFrame]:
    return pd.read_excel(io.BytesIO(data), sheet_name=None)

def summarise(frame: pd.DataFrame, by: str, value: str) -> pd.DataFrame:
    return (frame.groupby(by, as_index=False)[value].sum()
                 .sort_values(value, ascending=False, ignore_index=True))

def to_xlsx(sheets: dict[str, pd.DataFrame]) -> bytes:
    buffer = io.BytesIO()
    with pd.ExcelWriter(buffer, engine="openpyxl") as writer:
        for name, frame in sheets.items():
            frame.to_excel(writer, sheet_name=name, index=False)
    return buffer.getvalue()

read_workbook takes the file's bytes rather than the upload object. st.cache_data keys its cache on the arguments, so the same file uploaded again — or the same file during the many reruns described below — is read only once. sheet_name=None reads every sheet into a dictionary, which lets the user choose between them.

Step 2 — Build the page

A Streamlit script runs from top to bottom on every interaction. st.stop() ends a run early, which keeps the "nothing uploaded yet" and "unreadable file" paths short and readable:

st.title("Sales summariser")
uploaded = st.file_uploader("Upload a sales workbook", type=["xlsx"])
if uploaded is None:
    st.info("Upload an .xlsx file to begin.")
    st.stop()

try:
    sheets = read_workbook(uploaded.getvalue())
except Exception as error:
    st.error(f"Could not read {uploaded.name} as an Excel workbook: {error}")
    st.stop()

sheet = st.selectbox("Sheet", list(sheets))
frame = sheets[sheet]
st.dataframe(frame.head(50))

numeric = frame.select_dtypes("number").columns.tolist()
text = frame.select_dtypes(exclude="number").columns.tolist()
if not numeric or not text:
    st.warning("The sheet needs at least one text column and one number column.")
    st.stop()
by = st.selectbox("Group by", text)
value = st.selectbox("Sum of", numeric)
summary = summarise(frame, by, value)
st.dataframe(summary)

st.download_button(
    "Download summary.xlsx",
    data=lambda: to_xlsx({"Summary": summary, "Data": frame}),   # built on click
    on_click="ignore",
    file_name=f"{uploaded.name.removesuffix('.xlsx')}_summary.xlsx",
    mime=XLSX,
)

The column choices come from the sheet's own types, so the app adapts to whatever workbook is uploaded, and a sheet without suitable columns gets a warning instead of a traceback. The upload is read from memory — uploaded.getvalue() returns its bytes — so nothing is written to the server's disk.

Step 3 — Keep reruns fast

Because every widget change reruns the script, anything expensive in it happens again and again. Two things dominate an Excel app: parsing the upload and building the download. With a 50,000-row workbook, changing one selector took:

st.cache_data handles the parsing. For the download, recent Streamlit versions accept a function as data; it runs only when the button is clicked, which the lambda in Step 2 uses. on_click="ignore" stops the click from triggering yet another rerun. On older versions, cache the conversion function instead, so it runs once per distinct result.

Step 4 — Set limits and handle bad files

Streamlit accepts uploads up to 200 MB by default. A workbook that size can take minutes and gigabytes of memory to parse, so set the limit to what the app handles comfortably:

# .streamlit/config.toml
[server]
maxUploadSize = 25

type=["xlsx"] filters the file picker, but it checks the extension, not the contents. A text file renamed to .xlsx still arrives, which is why the read is wrapped in try/except: the user sees "Could not read notes.xlsx as an Excel workbook" instead of a stack trace. Deeper checks — expected columns, types, row limits — are covered in Validate an Uploaded Excel File Before Processing It in a Web App.

Step 5 — Test the app without a browser

streamlit.testing.v1.AppTest runs the script headlessly, lets tests set widget values — including uploads — and exposes what the page would show:

# test_app.py — run with pytest
import io

import pandas as pd
from streamlit.testing.v1 import AppTest

XLSX = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"

def workbook_bytes():
    buffer = io.BytesIO()
    pd.DataFrame({"region": ["North", "South", "North", "East"],
                  "amount": [100.0, 40.0, 60.0, 25.0]}).to_excel(buffer, index=False, sheet_name="Orders")
    return buffer.getvalue()

def test_asks_for_a_file():
    at = AppTest.from_file("app.py").run()
    assert at.info[0].value == "Upload an .xlsx file to begin."

def test_summarises_upload():
    at = AppTest.from_file("app.py").run()
    at.file_uploader[0].set_value(("sales.xlsx", workbook_bytes(), XLSX)).run()
    assert not at.exception
    summary = at.dataframe[1].value
    assert summary["region"].tolist() == ["North", "South", "East"]
    assert summary["amount"].tolist() == [160, 40, 25]
    assert at.download_button[0].label == "Download summary.xlsx"

def test_rejects_a_non_workbook():
    at = AppTest.from_file("app.py").run()
    at.file_uploader[0].set_value(("notes.xlsx", b"not a workbook", XLSX)).run()
    assert at.error[0].value.startswith("Could not read notes.xlsx")

pytest ran all three in about 1.3 seconds. Tests like these catch the common breakages: a renamed column, a widget that moved, an error path that raises instead of showing a message.

Step 6 — Accept several workbooks at once

A common next request is "can I drop in all the regional files together?". accept_multiple_files=True turns the upload into a list, and each file can be read through the same cached function and tagged with its name before combining:

import io

import pandas as pd
import streamlit as st

@st.cache_data(show_spinner=False)
def read_first_sheet(data: bytes) -> pd.DataFrame:
    return pd.read_excel(io.BytesIO(data))

files = st.file_uploader("Upload regional workbooks", type=["xlsx"], accept_multiple_files=True)
if not files:
    st.stop()
combined = pd.concat(
    [read_first_sheet(f.getvalue()).assign(source=f.name) for f in files],
    ignore_index=True,
)
st.caption(f"{len(files)} files, {len(combined):,} rows")
st.dataframe(combined.groupby("source")["amount"].sum())

In an AppTest run with two uploads of three and five rows, the caption read "2 files, 8 rows" and the totals were 30 and 50, one per source file. The source column matters more than it looks: when a total is wrong, it tells the user which file to open. Because the cache is keyed per file, adding a third file to the list reads only that file; the first two come from the cache. Files that do not share the same columns will combine with gaps, so apply the checks from Step 4 to each file before concatenating, and name the offending file in the message.

Common pitfalls

• Reading the upload twice. An UploadedFile is a stream; after one read() the next returns nothing. Use getvalue(), which always returns all the bytes.
• Global state between users. Every browser session reruns the script separately, but module-level variables and st.cache_resource objects are shared by all users. Keep per-user data in local variables or st.session_state.
• Formatting lost on the way out. to_excel writes plain cells. When the download should look like a report, style it with openpyxl or XlsxWriter before returning the bytes.
• No access control. streamlit run serves anyone who can reach the port. Put the app behind your organisation's sign-in, or use a hosting option that provides it, before real data goes near it.

Conclusion

Keep the Excel work in plain functions, read uploads from memory with getvalue(), cache the read on the file's bytes with st.cache_data, build the download only when it is clicked, stop early with clear messages on bad input, cap the upload size, and test the whole page with AppTest — uploads included.

Frequently asked questions

How do I read an uploaded Excel file in Streamlit? st.file_uploader returns an UploadedFile, a file-like object in memory. Pass its bytes to pandas with pd.read_excel(io.BytesIO(uploaded.getvalue())), or pass the object itself. Nothing is written to disk.

How do I let users download a DataFrame as an Excel file in Streamlit? Write the DataFrame to an io.BytesIO buffer with pandas.ExcelWriter, and pass the bytes to st.download_button with the xlsx MIME type and a file name. In recent Streamlit versions you can pass a function instead, so the file is built only when the button is clicked.

Why is my Streamlit Excel app slow every time I change a setting? Streamlit reruns the whole script on every interaction, so it re-reads the workbook and rebuilds the download each time. Cache the reading function with st.cache_data and build the download lazily; in a test with 50,000 rows, a rerun fell from 1.33 seconds to 0.01 seconds.

What is the largest file Streamlit accepts? 200 MB by default. Change it with server.maxUploadSize in .streamlit/config.toml. Large Excel files also take a long time to parse, so lower the limit to what the app can process comfortably.

Related

• Up one level: Serving Excel Files from Python Web Apps — Excel in and out of web applications.
• Validate an Uploaded Excel File Before Processing It in a Web App — checks to run before trusting an upload.
• Handle an Uploaded Excel File in Flask and FastAPI — the same upload in a conventional web framework.
• Create Excel Test Fixtures in Memory for pytest — workbooks for tests like the ones above.