Python Excel AutomationPractical guides for developers
Start learning
Guide
Automating Reporting WorkflowsDeep dive

Return an Excel File from a Flask Download Endpoint

A complete Flask export route: build the workbook in memory, set the download headers, format the sheet, handle empty results and errors, and test the response.

An "Export to Excel" button in a Flask app is about twenty lines, and roughly five of them are where people get stuck: the buffer must be closed before it is read, rewound before it is sent, and labelled with two headers before a browser treats it as a download. This guide builds the endpoint completely — formatting, empty results, error handling, safe filenames and a test — so it can be dropped into a real application. It is the Flask branch of Serving Excel Files from Python Web Apps.

The four steps inside a Flask export route The route scopes a query to the caller, writes a workbook into a memory buffer, closes the writer and rewinds, then hands the buffer to send_file with a download name. Four steps, and the one that breaks is always step three 1. query scoped to the user 2. write into BytesIO 3. close, seek(0) skip it and the file breaks 4. send_file as_attachment xlsxwriter finalises the zip on close — a buffer read before that is truncated

Prerequisites

Bash
pip install flask pandas xlsxwriter openpyxl

xlsxwriter writes the workbook; openpyxl is here only so the test at the end can read it back.

The smallest working endpoint

Python
"""app.py — a minimal Excel export route."""
import io

import pandas as pd
from flask import Flask, send_file

app = Flask(__name__)
XLSX = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"

@app.get("/exports/sales.xlsx")
def export_sales():
    df = pd.DataFrame({
        "region": ["North", "South", "East", "West"],
        "revenue": [128_400.50, 96_220.00, 51_130.25, 74_905.75],
    })

    buffer = io.BytesIO()
    with pd.ExcelWriter(buffer, engine="xlsxwriter") as writer:
        df.to_excel(writer, index=False, sheet_name="Sales")
    buffer.seek(0)

    return send_file(buffer, mimetype=XLSX, as_attachment=True,
                     download_name="sales.xlsx")

if __name__ == "__main__":
    app.run(debug=True)

Run it and open /exports/sales.xlsx; the browser saves a workbook. The with block is what closes the writer — xlsxwriter finalises the zip at that moment, and reading the buffer before it produces the truncated file that opens as gibberish.

Format the sheet while you have the writer

An export nobody has to reformat is worth the extra ten lines. writer.book and writer.sheets expose the xlsxwriter objects:

Python
def build_workbook(df: pd.DataFrame, sheet: str = "Sales") -> io.BytesIO:
    buffer = io.BytesIO()
    with pd.ExcelWriter(buffer, engine="xlsxwriter") as writer:
        df.to_excel(writer, index=False, sheet_name=sheet)
        book, ws = writer.book, writer.sheets[sheet]

        header = book.add_format({"bold": True, "bg_color": "#DDEBF7", "border": 1})
        money = book.add_format({"num_format": "#,##0.00"})

        for col, name in enumerate(df.columns):
            ws.write(0, col, name, header)
            width = max(len(str(name)) + 2, 12)
            if pd.api.types.is_numeric_dtype(df[name]):
                ws.set_column(col, col, width, money)
            else:
                ws.set_column(col, col, width)

        ws.freeze_panes(1, 0)
        ws.autofilter(0, 0, len(df), len(df.columns) - 1)
    buffer.seek(0)
    return buffer

Separating the build from the route also makes the workbook testable without a request context, which is the shape the test at the end depends on.

Scope the data to the caller

The export must not become a way to read someone else's rows. Filters may come from the request; identity must come from the session:

Python
from flask import abort, request, session

@app.get("/exports/orders.xlsx")
def export_orders():
    user_id = session.get("user_id")
    if not user_id:
        abort(401)

    region = request.args.get("region")            # narrows only
    rows = fetch_orders(owner_id=user_id, region=region)
    if not rows:
        df = pd.DataFrame({"message": ["No orders matched the selected filters."]})
    else:
        df = pd.DataFrame(rows)

    return send_file(build_workbook(df, "Orders"), mimetype=XLSX,
                     as_attachment=True, download_name=export_name("orders", region))

Note the empty case. Returning a workbook that says why it is empty is far better than returning a file with a header row and nothing under it, which reads as a bug.

Generate a filename worth keeping

Python
import re
from datetime import date

SAFE = re.compile(r"[^A-Za-z0-9._-]+")

def export_name(report: str, scope: str | None = None) -> str:
    parts = [report, scope or "all", f"{date.today():%Y-%m-%d}"]
    stem = SAFE.sub("-", "-".join(parts)).strip("-")
    return f"{stem[:80]}.xlsx"

Sanitising is not cosmetic: a newline or quote reaching the Content-Disposition header is a header-injection bug, and a scope value taken from data is exactly where one would come from.

Which parts of an export request may be trusted Identity and permissions come from the session, filters from query parameters may only narrow the result, and the filename is generated rather than echoed from input. Where each part of the response comes from session who is asking what they may see trusted query parameters region, date range may narrow only validated filename never echoed back generated and sanitised untrusted input A parameter that can widen the query is an authorisation bug wearing a filter's clothes

Handle errors before the response starts

Once Flask begins sending a file, it is too late to return an error page. Build the bytes first, then respond:

Python
from flask import abort

@app.get("/exports/sales.xlsx")
def export_sales():
    try:
        df = pd.DataFrame(fetch_sales())
        buffer = build_workbook(df)
    except Exception:
        app.logger.exception("sales export failed")
        abort(500)
    return send_file(buffer, mimetype=XLSX, as_attachment=True,
                     download_name=export_name("sales"))

Log the exception with context — user, filters, row count — so a failure is diagnosable without reproducing it. The logging setup is the same one scheduled jobs use in Log Python Excel script output to a file.

Escape values Excel would treat as formulas

Any text cell whose first character is =, +, - or @ becomes a live formula when the workbook is opened. If the data came from users, that is a spreadsheet-injection risk:

Python
def escape_formula(value):
    if isinstance(value, str) and value[:1] in ("=", "+", "-", "@"):
        return "'" + value
    return value

df = df.map(escape_formula)          # DataFrame.applymap on pandas < 2.1

Test the endpoint by reading the file back

Python
"""test_exports.py"""
import io

import pandas as pd
import pytest

from app import app

@pytest.fixture
def client():
    app.config["TESTING"] = True
    return app.test_client()

def test_sales_export(client):
    resp = client.get("/exports/sales.xlsx")
    assert resp.status_code == 200
    assert resp.headers["Content-Type"].endswith("spreadsheetml.sheet")
    assert "attachment" in resp.headers["Content-Disposition"]

    df = pd.read_excel(io.BytesIO(resp.data), engine="openpyxl")
    assert list(df.columns) == ["region", "revenue"]
    assert len(df) == 4

Round-tripping through pandas is what catches the truncated-buffer bug: a file that is 200 bytes short still has the right headers and status code, and only fails when something tries to read it.

Return several sheets from one route

A summary plus its detail is the most requested export shape, and it costs one extra to_excel call inside the same writer:

Python
def build_multi_sheet(summary: pd.DataFrame, detail: pd.DataFrame) -> io.BytesIO:
    buffer = io.BytesIO()
    with pd.ExcelWriter(buffer, engine="xlsxwriter") as writer:
        summary.to_excel(writer, index=False, sheet_name="Summary")
        detail.to_excel(writer, index=False, sheet_name="Detail")
        for name, frame in (("Summary", summary), ("Detail", detail)):
            ws = writer.sheets[name]
            ws.freeze_panes(1, 0)
            ws.autofilter(0, 0, len(frame), len(frame.columns) - 1)
    buffer.seek(0)
    return buffer

Sheet names are subject to Excel's rules — 31 characters, no / \ ? * [ ] — so sanitise any name derived from data before passing it. A workbook that breaks those rules opens with a repair prompt rather than an error in your code, which is covered in Fix "Excel found unreadable content" after writing with Python.

Offer the same data as CSV

Not every consumer wants a workbook. A second route costs a few lines, streams row by row, and takes the largest exports off the memory-hungry path entirely:

Python
from flask import Response

@app.get("/exports/sales.csv")
def export_sales_csv():
    def rows():
        yield "region,revenue\r\n"
        for region, revenue in fetch_sales():
            yield f"{region},{revenue:.2f}\r\n"

    return Response(
        rows(),
        mimetype="text/csv",
        headers={"Content-Disposition": f'attachment; filename="{export_name("sales")[:-5]}.csv"'},
    )

Because this is a generator, Flask streams it: memory stays flat regardless of row count, and the first bytes reach the client immediately. That is the one thing an .xlsx cannot do, since the zip is only finalised at the end — the trade-off set out in Convert Excel to CSV with Python.

Why CSV streams and xlsx cannot CSV rows leave the server as they are produced, while an xlsx must be fully built and zipped before the first byte can be sent. First byte out, by format CSV rows out rows out rows out xlsx build the whole workbook in memory zip, then send The zip's directory lives at the end of the file, so nothing can leave early

Common pitfalls and gotchas

  • Reading the buffer inside the with block. The zip is not finished until the writer closes.
  • Forgetting seek(0). The response is then empty, because the read starts at the end of the buffer.
  • attachment_filename on Flask 2+. It is download_name now.
  • Returning df.to_excel("file.xlsx"). Writing to disk in a web process creates a race between concurrent requests and leaves files behind.
  • Building a slow report inline. Past a few seconds, move it to a queue before a proxy times the request out.

Performance and scale notes

xlsxwriter holds the workbook in memory, so peak usage per request is roughly the DataFrame plus the finished file. Under concurrency that multiplies by the number of simultaneous exports, which is the number to cap — either with a row limit on inline exports or by moving large ones to a background worker. If a single export is genuinely large, construct the workbook with {"constant_memory": True} and accept its restrictions, as described in Write a million rows to Excel with xlsxwriter constant memory. Also turn off proxy buffering for the export route so nginx does not spool the whole download before forwarding it.

Conclusion

The Flask half of an Excel export is four steps: scope the query to the session, write into a BytesIO, close and rewind, then hand the buffer to send_file with a mimetype and a download name. Everything else — formatting, empty results, filename generation, formula escaping — is a few lines each and turns a working endpoint into one you can leave in production. Test it by reading the response back with pandas, and the two failure modes that matter cannot ship unnoticed.

Frequently asked questions

Why does the downloaded file open as gibberish or refuse to open? Almost always the buffer was read before the writer closed, or it was not rewound. Close the ExcelWriter (a with block does it), then call seek(0) before handing the buffer to send_file.

Should I use send_file or make_response?send_file when you have a buffer and want Flask to set the headers; make_response when you already have bytes and prefer to set Content-Type and Content-Disposition yourself. Both produce the same download.

Is download_name or attachment_filename correct?download_name, since Flask 2.0. attachment_filename is the old name and still appears in older tutorials; using it on a current Flask raises a TypeError.

How do I keep two users from getting each other's data? Build the query from the session's identity, not from request parameters. Parameters may narrow the result; they must never widen it beyond what the caller is allowed to see.

Can I return a workbook with several sheets? Yes. Write each DataFrame with a different sheet_name through the same ExcelWriter before the with block closes; the response code is unchanged.