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

Export a Django Queryset to Excel

Turn a Django queryset into a formatted .xlsx download: values() and related fields, iterator() for large exports, permissions, admin actions and a view test.

A Django export view has two halves: getting the right rows out of the ORM efficiently, and turning them into a workbook a person can use. Both halves have a wrong way that works fine on a hundred rows and falls over on a hundred thousand — iterating model instances, and building the file on disk. This guide does both properly, adds an admin action for bulk exports, and covers the permission checks an export endpoint deserves. It is the Django branch of Serving Excel Files from Python Web Apps.

From queryset to download, without model instances A scoped queryset with select_related and values yields dictionaries, which are written straight into an in-memory workbook and returned as an attachment response. Rows, not objects queryset scoped to the user .values(...) dicts from the DB workbook built in BytesIO HttpResponse attachment Building model instances for an export is work you throw away one field later

Prerequisites

Bash
pip install django pandas xlsxwriter openpyxl

The examples assume a familiar shape: an Order model with a foreign key to Customer.

Query the columns, not the objects

values() returns dictionaries straight from the database. No model instances are constructed, and double-underscore paths pull in related fields in the same query:

Python
rows = (
    Order.objects
    .filter(owner=request.user, created__gte=start)
    .select_related("customer")
    .values("reference", "created", "customer__name", "region", "total")
    .order_by("-created")
)

select_related("customer") turns what would be one query per row into a single join — the N+1 problem that makes an export mysteriously slow in proportion to its size. If a column needs computing, do it in the database rather than in Python:

Python
from django.db.models import F, Value
from django.db.models.functions import Concat

rows = rows.annotate(
    net=F("total") - F("discount"),
    label=Concat("region", Value(" / "), "customer__name"),
)

Build the workbook

Keep the builder separate from the view so an admin action and a background job can reuse it:

Python
"""exports.py"""
import io

import pandas as pd

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

HEADERS = {
    "reference": "Reference",
    "created": "Created",
    "customer__name": "Customer",
    "region": "Region",
    "total": "Total",
}

def queryset_to_xlsx(rows, sheet: str = "Orders") -> bytes:
    df = pd.DataFrame.from_records(list(rows))
    if df.empty:
        df = pd.DataFrame({"message": ["No rows matched the selected filters."]})
    else:
        df = df.rename(columns=HEADERS)[list(HEADERS.values())]
        df["Created"] = pd.to_datetime(df["Created"]).dt.tz_localize(None)

    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"})
        date = book.add_format({"num_format": "yyyy-mm-dd"})

        for col, name in enumerate(df.columns):
            ws.write(0, col, name, header)
            fmt = money if name == "Total" else date if name == "Created" else None
            ws.set_column(col, col, max(len(name) + 2, 14), fmt)
        ws.freeze_panes(1, 0)
        ws.autofilter(0, 0, len(df), len(df.columns) - 1)
    return buffer.getvalue()

The tz_localize(None) line is not optional. Django stores timezone-aware datetimes when USE_TZ is on, and Excel has no way to represent an offset — openpyxl raises and xlsxwriter writes something misleading. Convert to the display timezone first if that matters, as covered in Handle timezones in Excel timestamps with Python.

Return it from a view

Python
"""views.py"""
from django.contrib.auth.decorators import login_required, permission_required
from django.http import HttpResponse

from .exports import XLSX, queryset_to_xlsx

@login_required
@permission_required("orders.view_order", raise_exception=True)
def export_orders(request):
    rows = (Order.objects
            .filter(owner=request.user)
            .select_related("customer")
            .values("reference", "created", "customer__name", "region", "total"))

    response = HttpResponse(queryset_to_xlsx(rows), content_type=XLSX)
    response["Content-Disposition"] = 'attachment; filename="orders.xlsx"'
    return response

Two decorators do a lot of work here: login_required blocks anonymous access, and permission_required makes the export subject to the same permission as viewing the model. The filter(owner=request.user) is the row-level half — a permission alone does not stop one user's export returning another's rows.

Two independent checks an export needs A model permission decides whether the user may export this kind of record at all, while a queryset filter decides which records they may see; both are required. Permission and scope answer different questions model permission may this user export orders? permission_required decorator answers yes or no once queryset scope which orders may they see? filter(owner=request.user) answers per row, every time

Keep memory flat on a large export

values() still materialises every dictionary. For a large queryset, iterator() streams from the database cursor instead, and writing rows directly with xlsxwriter avoids the DataFrame entirely:

Python
import io

import xlsxwriter

def stream_orders_to_xlsx(queryset, columns: dict[str, str]) -> bytes:
    buffer = io.BytesIO()
    with xlsxwriter.Workbook(buffer, {"in_memory": True}) as book:
        ws = book.add_worksheet("Orders")
        bold = book.add_format({"bold": True})
        ws.write_row(0, 0, list(columns.values()), bold)

        for r, row in enumerate(queryset.values(*columns).iterator(chunk_size=2_000), start=1):
            ws.write_row(r, 0, [row[key] for key in columns])
        ws.freeze_panes(1, 0)
    return buffer.getvalue()

chunk_size controls how many rows the database driver fetches at a time; a few thousand is a reasonable default. Beyond a few hundred thousand rows, stop serving inline altogether — queue the job, store the file, and hand back a link, as described in the parent topic.

Add an admin action

The same builder gives the Django admin a bulk export on any changelist:

Python
"""admin.py"""
from django.contrib import admin
from django.http import HttpResponse

from .exports import XLSX, queryset_to_xlsx

@admin.action(description="Export selected orders to Excel")
def export_selected(modeladmin, request, queryset):
    rows = queryset.select_related("customer").values(
        "reference", "created", "customer__name", "region", "total")
    response = HttpResponse(queryset_to_xlsx(rows), content_type=XLSX)
    response["Content-Disposition"] = 'attachment; filename="orders-selected.xlsx"'
    return response

@admin.register(Order)
class OrderAdmin(admin.ModelAdmin):
    list_display = ("reference", "customer", "region", "total")
    actions = [export_selected]

An admin action returning an HttpResponse sends it to the browser instead of redirecting, which is exactly the behaviour a download needs.

Escape values Excel would run

Any text cell beginning =, +, - or @ becomes a formula when the file opens, and in a Django app that text usually came from a user:

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

Apply it as rows are written, or with df.map(escape_formula) before the write.

Test the view

Python
"""tests/test_exports.py"""
import io

import pandas as pd
from django.test import TestCase
from django.urls import reverse

class ExportTests(TestCase):
    def test_orders_export(self):
        self.client.force_login(self.user)
        resp = self.client.get(reverse("export-orders"))

        self.assertEqual(resp.status_code, 200)
        self.assertIn("spreadsheetml.sheet", resp["Content-Type"])
        self.assertIn("attachment", resp["Content-Disposition"])

        df = pd.read_excel(io.BytesIO(resp.content), engine="openpyxl")
        self.assertEqual(list(df.columns)[:2], ["Reference", "Created"])

    def test_export_requires_login(self):
        resp = self.client.get(reverse("export-orders"))
        self.assertEqual(resp.status_code, 302)

The second test matters as much as the first: an export view that quietly works while logged out is a data leak, and it is the check most easily forgotten.

Let the user choose the columns, safely

Export screens usually offer a column picker, and that is a place where user input reaches the ORM. Validate the selection against an allow-list so a crafted parameter cannot pull a password hash or traverse a relation you did not intend:

Python
"""Only these paths may ever appear in an export."""
EXPORTABLE = {
    "reference": "Reference",
    "created": "Created",
    "customer__name": "Customer",
    "region": "Region",
    "total": "Total",
    "status": "Status",
}

def chosen_columns(request) -> dict[str, str]:
    wanted = request.GET.getlist("columns") or list(EXPORTABLE)
    picked = {key: EXPORTABLE[key] for key in wanted if key in EXPORTABLE}
    return picked or dict(list(EXPORTABLE.items())[:3])

Filtering against the dictionary rather than trusting the list is the whole safeguard: an unknown key is dropped rather than passed to values(), and the fallback guarantees the export is never empty of columns. The same principle applies to sort keys and filter fields — an allow-list, not a denial-list.

Column selection filtered through an allow-list Requested column names are intersected with a fixed set of exportable paths, so unknown or crafted names are dropped before they reach the queryset. Requested names never reach values() directly requested region, total, owner__password EXPORTABLE reference, created, customer, region, total, status exported region, total unknown key dropped

Common pitfalls and gotchas

  • Iterating model instances. for order in qs: builds an object per row to read four fields from it.
  • Missing select_related. A related column without it issues one query per row.
  • Timezone-aware datetimes. Strip or convert them before writing; Excel cannot store an offset.
  • Decimal columns. They arrive as Decimal and write as text in some paths; cast to float for numeric cells.
  • Exporting the whole table by accident. An unfiltered queryset in an admin action ignores the selection if you rebuild it from the model rather than using the queryset argument.

Performance and scale notes

The database side dominates for wide exports: values() plus select_related turns an N+1 pattern into one query, and iterator(chunk_size=...) keeps the result set from being materialised at once. The workbook side is bounded by memory, roughly the finished file plus the rows in flight. Between them, a hundred thousand rows is comfortable inline on a normal instance and a million is not — at that size, queue the build, store the result, and send a link. If the recipient is a system rather than a person, a streamed CSV response has neither limit; the comparison is in Convert Excel to CSV with Python.

Conclusion

Export from Django by asking the database for columns, not objects: values() with select_related, annotated where a value needs computing, filtered to what the caller may see. Hand those rows to a builder that returns bytes, and return them with the spreadsheet content type and an attachment disposition. Reuse the same builder for an admin action and a background job, strip timezones before writing, escape leading formula characters, and test that the view refuses anonymous requests.

Frequently asked questions

Should I use values() or iterate model instances?values() for exports. It returns dictionaries straight from the database without building model objects, which is both faster and lighter — and an export needs columns, not behaviour.

How do I include a related object's field? Use double-underscore paths in values(), for example customer__name, and add select_related for the same relations so the query stays a single join rather than one query per row.

What about a queryset with a million rows? Do not serve it inline. Use iterator(chunk_size=...) to keep memory flat, and move the whole job to a background task that stores the file, because the request will time out long before the workbook is ready.

Can I add this to the Django admin? Yes. An admin action receives the selected queryset and can return an HttpResponse, so the same builder function serves both a view and an admin bulk export.

Do I need pandas? No. xlsxwriter can write rows directly from the queryset, which avoids materialising a DataFrame. pandas is convenient when you also want to reshape or aggregate before exporting.