openpyxl: MemoryError, or the worker killed, saving a large workbook

openpyxl builds the whole workbook as Python objects and serialises it when you call save(). A MemoryError, or more often a worker that simply disappears with Killed in the log, is not a leak or a bug in your loop. It is the library working as designed, on more rows than the process has memory for, and openpyxl ships the fix itself.

Why it happens

Every value you write becomes a Cell: an object with its value, its row and column, a style index and a reference back to the worksheet. The worksheet keeps them in a dictionary keyed by coordinate. None of it can be released before save(), because the writer needs the complete sheet to compute dimensions and the shared string table before it emits a byte of XML.

That works out at roughly 1.5 KB per row of four plain cells, about 370 bytes a cell. Measured on Python 3.13 with openpyxl 3.1.5, writing a string, a float, a date string and a status:

  10,000 rows     51 MB peak    0.4s
  20,000 rows     64 MB peak    0.7s
  50,000 rows    110 MB peak    1.9s
 100,000 rows    184 MB peak    3.7s
 200,000 rows    331 MB peak    7.4s

Python with openpyxl imported and nothing else is 37 MB, so the growth is real and it is linear. Python itself has no memory limit, so where this fails depends on where the process runs. At that rate a 512 MB container has room for roughly 300,000 rows and a 2 GB Celery worker for a little over a million, and when the limit arrives it is the OOM killer that enforces it, taking every other task on that worker too, which is why the log so often shows nothing at all.

A wide sheet makes it worse in proportion. Twenty columns is five times the cells, so the ceiling is a fifth of the rows. Styling a cell does not add another object, because styles are interned, but a formula does, and so does a datetime.

Fix 1: write-only mode, which is already installed

openpyxl has a second kind of workbook that writes each row to a temporary file as you append it and keeps nothing:

from openpyxl import Workbook

wb = Workbook(write_only=True)
ws = wb.create_sheet("Invoices")           # create_sheet, not wb.active
ws.append(["Customer", "Amount", "Issued", "Status"])

for invoice in invoices:                  # a cursor, not a list
    ws.append([invoice.customer, invoice.amount, invoice.issued, invoice.status])

wb.save("invoices.xlsx")

Same data, same machine:

  Workbook()              200,000 rows   331 MB   7.4s
  write_only=True         200,000 rows    38 MB   7.6s
  write_only=True       1,000,000 rows    38 MB  39.2s

Memory is flat because nothing accumulates: a million rows costs the same 38 MB as fifty thousand, and 37 of that is the interpreter. The time does not improve, because the per-cell work is the same; it just no longer has to fit.

The trade is that the sheet is write-only in the literal sense. There is no ws.cell(), no ws["A1"], no reading back a row you have appended, and rows go in order. Styles are applied to a WriteOnlyCell before it is appended rather than to a coordinate afterwards. Column widths and freeze panes are set before the first row. Charts and images still work. If your code computes a totals row by reading the cells above it, compute the total as you go instead.

Fix 2: XlsxWriter, if you are only writing

If the file is new, which an export always is, XlsxWriter is faster than openpyxl at the same job and has the same trick under a different name:

pip install xlsxwriter

import xlsxwriter

wb = xlsxwriter.Workbook("invoices.xlsx", {"constant_memory": True})
ws = wb.add_worksheet("Invoices")
money = wb.add_format({"num_format": "#,##0.00"})

ws.write_row(0, 0, ["Customer", "Amount", "Issued", "Status"])
for i, invoice in enumerate(invoices, start=1):
    ws.write_row(i, 0, [invoice.customer, invoice.amount, invoice.issued, invoice.status])
    ws.write_number(i, 1, invoice.amount, money)

wb.close()

  XlsxWriter, default         200,000 rows   163 MB    3.3s
  XlsxWriter, default       1,000,000 rows   717 MB   17.0s
  constant_memory             200,000 rows    20 MB    3.4s
  constant_memory           1,000,000 rows    20 MB   17.3s

constant_memory writes a row the moment you start the next one, so rows must be written top to bottom and a write to an earlier row is discarded without a word. Formats, formulas, charts and merged cells all work. XlsxWriter cannot open an existing file at all, which is the reason to stay on openpyxl if you are editing a template rather than producing a new file.

Fix 3: move it off the request

Whichever writer you use, a large export should not run inside a web request. Seven seconds of CPU in a Django or Flask view is a gunicorn worker held hostage, and three people clicking at once is the pool. Queue it, write to storage, and hand back a link.

What we would do

If the export is a new file and a data table, use XlsxWriter with constant_memory. If you have to stay on openpyxl, use write_only. Either is the honest answer and costs an hour.

If the same export has to come out of a Python service and a Node one identically, or the formatting is elaborate enough that you would rather describe it than write it, or you would rather not own the memory problem at all, that is what Jarrah is for. Post the rows and get back a styled .xlsx built outside your process.

Write them as NDJSON: a header line describing the workbook, then one line per row, from a generator so your side never holds more than one row either:

import json, os, requests

def ndjson(invoices):
    yield (json.dumps({"sheets": [{"name": "Invoices", "columns": [
        {"header": "Customer"},
        {"header": "Amount", "style": "money"},
        {"header": "Issued"},
        {"header": "Status"},
    ]}]}) + "\n").encode()
    for invoice in invoices:              # a cursor, or a generator
        yield (json.dumps([invoice.customer, invoice.amount,
                           invoice.issued, invoice.status]) + "\n").encode()

job = requests.post(
    "https://api.jarrah.sh/v1/jobs",
    data=ndjson(invoices),                # a generator: requests sends it chunked
    headers={
        "Authorization": f"Bearer {os.environ['JARRAH_KEY']}",
        "Content-Type": "application/x-ndjson",
    },
).json()                                  # poll /v1/jobs/{id}, or take a webhook

requests sends a generator body with chunked transfer encoding, one yield at a time. Measured against a local sink: 28 MB at 200,000 rows, and still 28 MB at a million.

For small exports there is a synchronous endpoint that hands the file straight back. See the quickstart. It has a per-plan cell ceiling, so a 200,000-row sheet takes the streaming path above.