Write a Million Rows to Excel with xlsxwriter

There is a point where writing an Excel file stops being a formatting problem and becomes a memory problem. In its default mode, xlsxwriter holds every written cell until close(), which is fast and flexible up to a few hundred thousand rows and then starts consuming gigabytes. constant_memory mode changes that: each row is serialised as soon as the next one begins, so peak memory stays flat whether the sheet has ten thousand rows or a million.

The mode costs something real, and knowing exactly what makes the difference between using it well and fighting it. This guide, part of Building Excel Reports with xlsxwriter, covers the trade, the write-order rules, the format's hard limits, and the question worth asking before any of it.

Prerequisites

pip install xlsxwriter

You also want somewhere with free disk space for the temporary files — constant-memory mode trades RAM for a spooled temporary directory, and a full /tmp is the most common way this fails on a server.

Step 1: Turn the mode on

It is a workbook option, set at construction:

import xlsxwriter

wb = xlsxwriter.Workbook("detail.xlsx", {
    "constant_memory": True,
    "tmpdir": "/var/tmp",              # somewhere with room; defaults to the system temp
    "default_date_format": "yyyy-mm-dd",
})
ws = wb.add_worksheet("Detail")

money = wb.add_format({"num_format": '#,##0.00'})
ws.set_column("A:A", 12)
ws.set_column("B:B", 18)
ws.set_column("C:C", 14, money)        # column formats still work

ws.write_row(0, 0, ["order_id", "region", "amount"])

for i, (oid, region, amount) in enumerate(source_rows(), start=1):
    ws.write_number(i, 0, oid)
    ws.write_string(i, 1, region)
    ws.write_number(i, 2, amount)

wb.close()

set_column is still available and still cheap, which matters more here than in the default mode: with no ability to revisit cells, column-level formatting is the main styling tool you have left. default_date_format is worth setting for the same reason — it applies to every date written without an explicit format, saving you from passing one on every call.

Step 2: Respect the write order

The rule is simple and unforgiving: rows must be written in increasing order, and once you start row n+1, row n is gone.

ws.write(0, 0, "header")
ws.write(1, 0, "first")
ws.write(2, 0, "second")
ws.write(1, 1, "late")          # silently lost — row 1 was already flushed

There is no exception and no warning. In practice this rules out three habits that are normal in the default mode:

• A total row at the top. Compute it first from the source data, or put it on a separate summary sheet written before the detail sheet.
• Measured column widths. You cannot look at the data and then size the columns, because by the time you have seen it the rows are written. Size from the schema — a known-length ID, a currency column — or take one pass over a sample to estimate.
• Post-hoc highlighting. Decide the format as each row is written, from the values you already have in hand.

Within a row, cells can be written in any column order; it is only the row index that must not go backwards. Writing to a different sheet is fine at any time — each worksheet keeps its own current row — which is exactly what makes the summary-sheet pattern work.

Step 3: Put the totals somewhere they can exist

Because the detail sheet cannot be revisited, accumulate as you stream and write the summary afterwards on its own sheet:

from collections import defaultdict

wb = xlsxwriter.Workbook("detail.xlsx", {"constant_memory": True})
detail = wb.add_worksheet("Detail")
summary = wb.add_worksheet("Summary")          # created now, written later

bold = wb.add_format({"bold": True})
money = wb.add_format({"num_format": '#,##0.00'})

detail.write_row(0, 0, ["order_id", "region", "amount"], bold)
detail.set_column("C:C", 14, money)

totals, count = defaultdict(float), 0
for i, (oid, region, amount) in enumerate(source_rows(), start=1):
    detail.write_number(i, 0, oid)
    detail.write_string(i, 1, region)
    detail.write_number(i, 2, amount)
    totals[region] += amount
    count += 1

summary.write_row(0, 0, ["Region", "Amount"], bold)
summary.set_column("B:B", 16, money)
for r, (region, total) in enumerate(sorted(totals.items()), start=1):
    summary.write_string(r, 0, region)
    summary.write_number(r, 1, total)
summary.write_string(len(totals) + 2, 0, f"{count:,} detail rows", bold)

wb.close()

A dictionary of running totals costs a few kilobytes regardless of how many rows pass through it, so the summary is effectively free. This is also better reporting than a total row on a million-row sheet, which nobody will ever scroll to.

Step 4: Handle the format's hard limits

A worksheet holds at most 1,048,576 rows and 16,384 columns. That is the .xlsx specification, not a library restriction, so the only answer is to split:

ROWS_PER_SHEET = 1_000_000          # leave headroom below the 1,048,576 limit


def write_split(rows, path, headers):
    wb = xlsxwriter.Workbook(path, {"constant_memory": True})
    bold = wb.add_format({"bold": True})
    ws, sheet_no, row_no = None, 0, 0

    for row in rows:
        if ws is None or row_no > ROWS_PER_SHEET:
            sheet_no += 1
            ws = wb.add_worksheet(f"Detail {sheet_no}")
            ws.write_row(0, 0, headers, bold)
            ws.set_column(0, len(headers) - 1, 16)
            row_no = 1
        ws.write_row(row_no, 0, row)
        row_no += 1

    wb.close()
    return sheet_no

Splitting across sheets keeps everything in one file, which is convenient but produces a workbook that takes a long time to open. Splitting across files — one per region, per month, per whatever the reader actually filters by — is nearly always the better experience, and it is what Generate One Excel Report per Region in a Loop covers.

Step 5: Know where the time goes

Constant-memory mode fixes memory, not speed. At a million rows the cost breaks down roughly like this:

The practical lever is columns, not rows: dropping four unused columns from a million-row export removes four million cells and takes a proportional slice off every stage. The second lever is write_row over three separate write_number calls, which cuts the Python-side overhead noticeably at this scale.

If the source is a database, stream it rather than materialising it — Export SQL Query Results to Excel with Python covers stream_results and chunked reads, and the two techniques compose: a chunked read feeding a constant-memory write holds neither side in memory.

Common pitfalls and gotchas

Performance and scale notes

As a rule of thumb on ordinary hardware, a million rows of three or four simple columns writes in the low tens of seconds with flat memory in the tens of megabytes; the same write in the default mode is faster per row but climbs into gigabytes and eventually swaps, at which point it is slower by a wide margin. The crossover where constant memory starts winning is usually somewhere between 200,000 and 500,000 rows, depending on column count and how much formatting is attached.

The honest scale note, though, is about the reader. A one-million-row .xlsx takes a long time to open, makes filtering sluggish and cannot be emailed. Before optimising the write, check whether anyone opens the detail at all — Convert Excel to CSV with Python covers the flat-file alternative, and a summary workbook alongside it is almost always what people actually wanted.

Conclusion

constant_memory makes a workbook of any size writable in flat memory, at the cost of random access: rows go out in order and never come back. Design around that — column formats instead of per-cell styling, totals accumulated while streaming and written to a summary sheet, widths chosen from the schema — and give the spool a tmpdir with room. Then step back and check the file is one anybody can open, because splitting by the dimension people filter on, or shipping a summary plus a flat file, usually beats the million-row sheet you were about to generate.

Frequently asked questions

What is the row limit for one sheet? 1,048,576 rows and 16,384 columns — a hard limit of the .xlsx format, not of any library. Past that you must split across sheets or files.

What exactly does constant_memory give up? Random access. Each row is serialised when the next one starts, so rows must be written top to bottom and a written row cannot be revisited — no late totals, no measured column widths, no going back.

Can I still format cells in constant memory mode? Yes, at write time. Column formats via set_column and per-cell formats passed to the write call both work; what you cannot do is restyle a row after moving past it.

Should I be writing a million rows to Excel at all? Usually not. A workbook that large is slow to open and unusable to filter. Ship a summary workbook plus a CSV or Parquet file for the detail, and reserve the giant sheet for cases where a tool downstream genuinely requires .xlsx.

Related

Up to the parent guide:

• Building Excel Reports with xlsxwriter — the write-once model this mode makes stricter.

Related guides:

• Write Large DataFrames to Excel with Write-Only Mode — openpyxl's equivalent streaming write.
• Read Large Excel Files in Chunks with pandas — the reading half of the same problem.
• Convert Excel to CSV with Python — the format that stays fast when the row count does not stop growing.
• Write a Formatted Excel Report with xlsxwriter — the styling that is still available at this scale.