Cache Generated Excel Files in a Web App

An "Export to Excel" button is cheap to add and can be expensive to run. Each click queries the database, builds a DataFrame, writes a styled workbook and sends it — a few seconds of work for a big export, repeated every time anyone clicks, even when ten people download the same month's report in the same morning and the data has not changed since midnight. Caching the generated file fixes that: build it once, keep it on disk, and hand the same bytes to everyone who asks for the same thing until the data changes. The details are what make it correct — a cache key that changes when the data changes, files that are never served half-written, browsers told when their copy is still current, and a limit on how much disk the cache may use. This page builds that cache for a Flask download endpoint and tests it with Flask's test client. It belongs to Serving Excel Files from Python Web Apps.

Prerequisites

pip install flask pandas openpyxl

The same design works in FastAPI or Django: only the request and response objects differ. The example data lives in SQLite, with an updated_at column that the cache uses to notice changes.

Step 1 — A cache key that changes when the data does

import hashlib, json, sqlite3
from contextlib import closing

DB = "sales.sqlite"

def data_version():
    """Cheap fingerprint of the data: last update time and row count."""
    with closing(sqlite3.connect(DB)) as con:
        return con.execute("SELECT MAX(updated_at) || '/' || COUNT(*) FROM sales").fetchone()[0]

def cache_key(report, params, version, code_version="v3"):
    payload = json.dumps({"report": report, "params": params, "data": version, "code": code_version}, sort_keys=True)
    return hashlib.sha256(payload.encode()).hexdigest()[:32]

v = data_version()
print("data version:", v)
print(cache_key("sales", {"month": "2026-09", "region": "North"}, v))
print(cache_key("sales", {"region": "North", "month": "2026-09"}, v))     # same key, order does not matter

The cache key decides when a cached file can be reused, so it must include everything that changes the file's content. That is three things: the request parameters (which month, which region), a version of the data, and a version of the code that builds the workbook. The data version here is the latest updated_at plus the row count — a cheap query that changes whenever rows are added, changed or deleted, without reading the data itself. The code version is a constant you bump when the layout of the export changes, so a new column or new styling is not hidden behind old cached files. Serialising the parameters with sort_keys=True before hashing makes the key independent of the order the parameters arrived in.

Step 2 — Build once, write atomically, one builder per key

import os, tempfile, threading, time
from pathlib import Path
import pandas as pd

CACHE = Path("export_cache")
CACHE.mkdir(exist_ok=True)
_locks, _locks_guard = {}, threading.Lock()
builds = []                                     # for the demonstration: which keys were actually built

def build_workbook(params, target):
    with closing(sqlite3.connect(DB)) as con:
        df = pd.read_sql("SELECT region, month, revenue FROM sales WHERE month = ? AND region = ?", con,
                         params=(params["month"], params["region"]))
    fd, tmp = tempfile.mkstemp(dir=CACHE, suffix=".tmp")
    with os.fdopen(fd, "wb") as f:              # a file object: openpyxl would reject the .tmp name
        df.to_excel(f, index=False, engine="openpyxl")
    os.replace(tmp, target)                     # atomic: readers see the old file or the whole new one

def cached_export(params):
    key = cache_key("sales", params, data_version())
    path = CACHE / f"{key}.xlsx"
    if not path.exists():
        with _locks_guard:
            lock = _locks.setdefault(key, threading.Lock())
        with lock:                              # only one request builds a given key
            if not path.exists():
                builds.append(key)
                build_workbook(params, path)
    os.utime(path)                              # touch: records last use for eviction
    return key, path

Two requests for the same uncached export can arrive within the same second. Without coordination both would build it, doubling the work at exactly the moment the server is busiest. A lock per cache key makes the second request wait for the first and then find the finished file — the second path.exists() check, inside the lock, is what makes that work. The lock is per key, so exports for different months still build in parallel. With several worker processes, a per-key file lock replaces the thread lock, as in Prevent Overlapping Runs of an Excel Report Job.

The workbook is written to a temporary file in the cache folder and then renamed into place with os.replace, which is atomic on the same file system. A request that arrives while a build is in progress therefore never finds a half-written file under the final name, and a build that crashes leaves only a stray .tmp file behind, never a corrupt cache entry. Touching the file on every use records when it was last needed, which the eviction step relies on.

Step 3 — The endpoint, with ETag and 304

from flask import Flask, request, send_file

app = Flask(__name__)

@app.get("/exports/sales.xlsx")
def sales_export():
    params = {"month": request.args["month"], "region": request.args.get("region", "North")}
    key, path = cached_export(params)
    if request.if_none_match.contains(key):
        return "", 304, {"ETag": f'"{key}"', "Cache-Control": "private, max-age=0, must-revalidate"}
    response = send_file(path, as_attachment=True, download_name=f"sales_{params['month']}_{params['region']}.xlsx",
                         mimetype="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", etag=False)
    response.set_etag(key)
    response.headers["Cache-Control"] = "private, max-age=0, must-revalidate"
    return response

client = app.test_client()
t0 = time.perf_counter(); r1 = client.get("/exports/sales.xlsx?month=2026-09&region=North"); t1 = time.perf_counter() - t0
t0 = time.perf_counter(); r2 = client.get("/exports/sales.xlsx?month=2026-09&region=North"); t2 = time.perf_counter() - t0
r3 = client.get("/exports/sales.xlsx?month=2026-09&region=North", headers={"If-None-Match": r1.headers["ETag"]})
print(r1.status_code, f"{len(r1.data):,} bytes in {t1 * 1000:.0f} ms | repeat {r2.status_code} in {t2 * 1000:.0f} ms | "
      f"conditional {r3.status_code}, {len(r3.data)} bytes | builds: {len(builds)}")
for r in (r1, r2, r3):
    r.close()                                   # releases the file handle send_file opened

The cache key doubles as the HTTP ETag: a short identifier for this exact version of the file. The browser stores it with the download, and when it asks again it can send If-None-Match with that value; if the key is unchanged, the endpoint replies 304 Not Modified with no body, and the browser uses its copy. The key is used rather than a hash of the file's bytes on purpose: openpyxl writes the creation time into every workbook, so two builds of identical data produce different bytes, and an ETag based on bytes would change on every rebuild. private keeps shared proxies from storing a file that may contain one user's data; must-revalidate with max-age=0 tells the browser to check each time, which costs a tiny request and guarantees nobody keeps a stale export.

The test client shows the three cases: the first request builds the file, the repeat is served from disk much faster, and the conditional request gets a 304 with an empty body — and the workbook was built only once.

The timings are from a small test export; the saving grows with the size and complexity of the workbook, since the cached path only reads a file from disk whatever went into building it.

Step 4 — Notice data changes, and evict old files

with closing(sqlite3.connect(DB)) as con, con:
    con.execute("UPDATE sales SET revenue = revenue + 1, updated_at = '2026-10-02T08:15:00' WHERE rowid = 1")
r4 = client.get("/exports/sales.xlsx?month=2026-09&region=North", headers={"If-None-Match": r1.headers["ETag"]})
print("after a data change:", r4.status_code, "| new ETag differs:", r4.headers["ETag"] != r1.headers["ETag"], "| builds:", len(builds))
r4.close()

def evict(max_age_s=7 * 86400, max_total_bytes=500 * 1024**2):
    files = sorted(CACHE.glob("*.xlsx"), key=lambda p: p.stat().st_mtime)      # least recently used first
    now, total = time.time(), sum(p.stat().st_size for p in files)
    removed = 0
    for p in files:
        if now - p.stat().st_mtime > max_age_s or total > max_total_bytes:
            total -= p.stat().st_size
            p.unlink(missing_ok=True)
            removed += 1
    for tmp in CACHE.glob("*.tmp"):
        if now - tmp.stat().st_mtime > 3600:
            tmp.unlink(missing_ok=True)
    return removed

print("evicted:", evict(max_total_bytes=1))

When a row changes, the data version changes, so the cache key and the ETag change too: the browser's If-None-Match no longer matches, it receives the fresh file with a 200, and the workbook is built a second time. Nothing has to be cleared by hand — old entries simply stop being asked for. They still use disk, which is what eviction is for: a small function, run every hour or so from a scheduler, deletes files not used for a week and, if the cache is still over its size limit, the least recently used files until it fits. It also removes .tmp files left by crashed builds. Calling it with a tiny limit, as here, empties the cache — both versions of the export.

When not to cache

Caching suits exports that many people request with the same parameters, that are slow to build, and whose data changes less often than they are downloaded — monthly reports, reference lists, dashboards over yesterday's data. It suits per-user exports with unique filters much less: every request has its own key and the cache only fills the disk. A data version also needs a cheap and reliable change signal; if the data has no updated_at column or row count that changes on edits, a short time-based expiry is the honest alternative. For exports that take minutes rather than seconds, caching alone is not enough — the first request still waits — and the work belongs in a background job, as in Generate Large Excel Exports in a Background Job.

Common pitfalls

• A key without the data version. Users get old figures after the data changes.
• ETags from the file bytes. openpyxl's timestamps change the bytes on every build.
• Writing straight to the final name. Write to a temp file and os.replace it.
• Duplicate builds under load. Lock per key and check again inside the lock.
• No eviction. Delete by last use and total size, and clean up .tmp files.

Conclusion

To cache Excel exports in a web app, key each file on the report, its parameters, a cheap data version and a code version; build it once under a per-key lock and write it atomically; serve it with that key as the ETag so browsers get 304 Not Modified while it is current; and evict files by last use and total size. A change in the data changes the key, so nobody ever downloads stale figures from the cache.

Frequently asked questions

How do I cache a generated Excel file in Flask? Compute a key from the parameters and data version, save the workbook under that key in a cache folder, and serve it with send_file on later requests.

How do ETags help with Excel downloads? The browser sends the ETag back in If-None-Match; if the file has not changed, the server replies 304 with no body and the browser reuses its copy.

Why does the same Excel file have different bytes each time? openpyxl and most writers store the creation time in the workbook properties, so ETags should come from the cache key, not from the file contents.

How do I stop two requests building the same export at once? Use a lock per cache key and check whether the file exists again after acquiring it.

Related

• Up one level: Serving Excel Files from Python Web Apps — Excel downloads from web apps.
• Return an Excel File from a Flask Download Endpoint — the basic endpoint.
• Write Excel Files Atomically in Python — the replace pattern.
• Cache API Responses When Building Excel Reports — caching the inputs instead.