Generate Large Excel Exports in a Background Job

An export button that works for a month's orders times out for a year's. The browser waits, the reverse proxy gives up after thirty or sixty seconds, the user clicks again, and now two copies of the same heavy query are running. The fix is to stop building the file inside the request. The request starts a job and returns immediately with its ID; a worker builds the workbook in the background, streaming rows so memory stays flat; the page polls a status endpoint and shows a download link when the file is ready. This page, part of Serving Excel Files from Python Web Apps, builds that flow in Flask with a thread pool as the worker, tests it end to end, and shows where Celery or RQ slot in.

Prerequisites

pip install flask xlsxwriter pandas openpyxl

shop.db is a SQLite database with 60,000 orders standing in for the application's database. The web app is Flask, and the background worker is a ThreadPoolExecutor so the whole flow runs in one process for the test; in production the worker is a separate process — Celery, RQ or a cloud task queue — and nothing else in the design changes.

Step 1 — A worker function that streams

The export function reads the query result in chunks with fetchmany and writes each row straight into an xlsxwriter workbook opened in constant_memory mode, which flushes every finished row to disk. Neither the result set nor the worksheet is ever held in memory whole:

import sqlite3
from pathlib import Path
import xlsxwriter

EXPORT_DIR = Path("exports")
EXPORT_DIR.mkdir(exist_ok=True)

def build_export(job, region=None, chunk=5000):
    con = sqlite3.connect("shop.db")
    where, params = ("WHERE region = ?", [region]) if region else ("", [])
    total = con.execute(f"SELECT COUNT(*) FROM orders {where}", params).fetchone()[0]
    cur = con.execute(f"SELECT * FROM orders {where} ORDER BY order_id", params)
    path = EXPORT_DIR / f"orders_{job['id']}.xlsx"
    wb = xlsxwriter.Workbook(path, {"constant_memory": True})
    ws = wb.add_worksheet("Orders")
    money = wb.add_format({"num_format": "#,##0.00"})
    ws.write_row(0, 0, [d[0] for d in cur.description], wb.add_format({"bold": True}))
    ws.set_column(4, 4, 12, money)
    written = 0
    while rows := cur.fetchmany(chunk):
        for row in rows:
            written += 1
            ws.write_row(written, 0, row)
        job["progress"] = round(100 * written / total)
    wb.close()
    con.close()
    return path, written

In constant_memory mode rows must be written in order, top to bottom — which a streaming export does naturally. The function updates job["progress"] after each chunk, which is what the status endpoint reports. Column formats are set with set_column before any data is written, because per-cell formats are the expensive part of large sheets. In a test with 200,000 rows on this machine, this streaming version peaked at about 3 MB of Python memory, while reading the same rows into a DataFrame and calling to_excel peaked at around 220 MB — and streaming was also faster. Write a Million Rows to Excel with xlsxwriter Constant Memory covers the mode in depth.

Step 2 — The web endpoints

Three routes: one starts a job, one reports its status, one serves the finished file. The job store is a dictionary here; in production it is Redis or a database table, so that any web process can answer status requests:

import uuid
from concurrent.futures import ThreadPoolExecutor
from flask import Flask, abort, jsonify, request, send_file, url_for

app = Flask(__name__)
executor = ThreadPoolExecutor(max_workers=2)
JOBS = {}

def run_job(job, region):
    job["status"] = "running"
    try:
        path, rows = build_export(job, region)
        job.update(status="done", path=str(path), rows=rows)
    except Exception as err:
        job.update(status="failed", error=str(err))

@app.post("/exports")
def start_export():
    region = request.json.get("region") if request.is_json else None
    job = {"id": uuid.uuid4().hex, "status": "queued", "progress": 0}
    JOBS[job["id"]] = job
    executor.submit(run_job, job, region)
    return jsonify(id=job["id"], status_url=url_for("export_status", job_id=job["id"])), 202

@app.get("/exports/<job_id>")
def export_status(job_id):
    job = JOBS.get(job_id) or abort(404)
    body = {k: job[k] for k in ("id", "status", "progress") if k in job}
    if job["status"] == "done":
        body["download_url"] = url_for("export_file", job_id=job_id)
        body["rows"] = job["rows"]
    if job["status"] == "failed":
        body["error"] = job["error"]
    return jsonify(body)

@app.get("/exports/<job_id>/file")
def export_file(job_id):
    job = JOBS.get(job_id)
    if not job or job["status"] != "done":
        abort(404)
    return send_file(job["path"], as_attachment=True, download_name="orders_export.xlsx")

The start route returns 202 Accepted — "your request is being processed" — with the job's status URL, in well under a second whatever the export's size. Failures inside the job are caught and recorded with their message, so the status endpoint can tell the user the export failed instead of leaving them polling for ever. The file route refuses to serve anything that is not finished.

Step 3 — Test the whole flow

Flask's test client drives the three routes exactly as a browser would — start, poll, download — and the downloaded bytes are opened as a workbook:

import io
import time
import pandas as pd

client = app.test_client()
started = client.post("/exports", json={"region": "West"})
print(started.status_code, started.json["status_url"])

while True:
    status = client.get(started.json["status_url"]).json
    if status["status"] in ("done", "failed"):
        break
    time.sleep(0.2)
print(status)

file = client.get(status["download_url"])
export = pd.read_excel(io.BytesIO(file.data))
file.close()
print(file.headers["Content-Disposition"], export.shape, export["region"].unique().tolist())

The request returns 202 immediately, the status moves from queued or running to done with the row count, and the downloaded workbook contains only the West orders — about a quarter of the 60,000. In the browser, a few lines of JavaScript do the polling and show a progress bar from the progress field; in Dash or Streamlit, an interval component does the same.

Step 4 — Clean up old files

Every export leaves a file behind. Delete finished exports after a day, from a scheduled task, and forget their jobs:

from datetime import datetime, timedelta

def clean_exports(max_age=timedelta(hours=24), now=None):
    now = now or datetime.now()
    removed = 0
    for job_id, job in list(JOBS.items()):
        path = Path(job.get("path", ""))
        if job["status"] == "done" and path.exists():
            if now - datetime.fromtimestamp(path.stat().st_mtime) > max_age:
                path.unlink()
                del JOBS[job_id]
                removed += 1
    return removed

print(clean_exports(), "removed now;", clean_exports(now=datetime.now() + timedelta(days=2)), "removed two days later")

Two things follow from files expiring: the status endpoint should answer 404 or "expired" for an old job, and the email or page that shows the link should say how long it is valid. When files are stored in object storage rather than on the web server's disk, a lifecycle rule on the bucket does the clean-up, and a signed URL replaces the file route — see Upload an Excel Report to Amazon S3 with boto3.

Moving to a real queue

The thread pool keeps jobs inside the web process, so they are lost if it restarts and cannot run on another machine. In production, executor.submit(run_job, ...) becomes a task queue call — build_export_task.delay(job_id, region) with Celery, or queue.enqueue(...) with RQ — the job store moves to Redis or the database, and exports are written to shared or object storage so any web process can serve them. Schedule Excel Reports with Celery Beat sets up the Celery side, and the same worker can run the nightly clean-up.

Common pitfalls

• Building large files inside the request. Proxies time out long before big exports finish; return 202 and a job ID.
• Loading everything into a DataFrame. Stream rows with fetchmany into constant_memory mode.
• Jobs that fail silently. Catch exceptions in the job and record them for the status endpoint.
• A job store in one process. Status requests may hit another process; keep job state in Redis or the database.
• Files kept for ever. Expire them and say so next to the link.

Conclusion

Answer the export request with 202 Accepted and a job ID, and build the workbook in a background worker that streams database rows with fetchmany into xlsxwriter's constant_memory mode, updating progress as it goes. Expose a status endpoint with progress, errors and a download link, serve only finished files, test the start-poll-download cycle with Flask's test client, expire old files on a schedule, and move the worker and job store to Celery or RQ and Redis in production.

Frequently asked questions

How do I stop an Excel export from timing out in Flask or Django? Do not build the file inside the request. Return 202 with a job ID, build the workbook in a background worker, and let the page poll a status endpoint until a download link is available.

How do I write a very large Excel file without running out of memory? Read rows from the database in chunks with fetchmany and write them one by one to an xlsxwriter workbook opened with {"constant_memory": True}, which flushes each finished row to disk.

How do I show progress for a background export? Count the rows first, update a progress value in the job's state after each chunk is written, and return it from the status endpoint for the page to display.

What should do the background work in production? A task queue such as Celery or RQ with workers in separate processes, a shared job store such as Redis or a database table, and shared or object storage for the finished files.

Related

• Up one level: Serving Excel Files from Python Web Apps — Excel in and out of web applications.
• Stream an Excel File from a FastAPI Endpoint — when the file is small enough to send directly.
• Download an Excel File from a Dash App — the export button side.
• Stream Excel Rows into a Database with openpyxl — streaming in the other direction.