pandas DataFrame.to_excel is slow, and runs out of memory on a large frame

to_excel is slow because it walks the frame in Python one cell at a time, and it is memory-hungry because the engine underneath buffers every cell until the file is closed. Which engine that is depends on what happens to be installed, which is why the same line of code behaves differently on two machines.

Why it happens

to_csv hands the whole frame to a C writer and is done. to_excel cannot: the format is a zip of XML documents, so pandas iterates the frame in Python, formats each value as a cell, and calls the engine for every one. Four columns by 200,000 rows is 800,000 calls before a byte is zipped.

Then the engine holds what it was given. openpyxl keeps a cell object per value until save(); XlsxWriter keeps the rows as lists until close(). Measured on Python 3.13, pandas 3.0.6, a frame of a string, a float, a date string and a status:

                               50,000 rows          200,000 rows
  the DataFrame alone           77 MB   0.02s       114 MB   0.1s
  to_excel, openpyxl           153 MB   2.3s        396 MB   9.7s
  to_excel, xlsxwriter         107 MB   1.5s        225 MB   6.2s
  to_csv                        81 MB   0.08s       115 MB   0.3s

Subtract the frame and the writer's own cost is about 1.4 KB per row through openpyxl and 0.55 KB through XlsxWriter. Neither has a row limit of its own; the limit is whatever memory the process has, and a worker that crosses it is killed rather than told. The traceback, if there is one, points at openpyxl/cell/cell.py or a zip write, which is where the last allocation happened rather than where the memory went.

Fix 1: pick the engine, and the mode

XlsxWriter has a constant_memory mode that writes each row to a temporary file as soon as the next row starts, so nothing accumulates. pandas passes it through engine_kwargs:

pip install xlsxwriter

with pd.ExcelWriter(
    "invoices.xlsx",
    engine="xlsxwriter",
    engine_kwargs={"options": {"constant_memory": True}},
) as writer:
    df.to_excel(writer, sheet_name="Invoices", index=False)

Same frame, same machine:

  to_excel, openpyxl                 200,000 rows   396 MB   9.7s
  to_excel, xlsxwriter               200,000 rows   225 MB   6.2s
  xlsxwriter + constant_memory       200,000 rows   114 MB   4.5s   (the frame, and nothing else)

Peak memory is now the DataFrame itself. The frame is the floor, because you built it before calling to_excel; nothing in the writer can get under it. The time improves too, because XlsxWriter's row path is cheaper than openpyxl's cell objects, but it is still seconds: the per-cell loop in pandas is the same regardless of engine.

The trade is that constant_memory writes rows in order and never looks back. to_excel itself is fine with that. Anything you do to the sheet afterwards through writer.sheets["Invoices"] that touches cells already written, such as autofitting columns from their contents or writing a totals row above the data, silently does nothing. Column widths, formats and freeze panes set before the data are fine.

Fix 2: ask whether it needs to be Excel

If the file is consumed by a program, a pipeline, or a person who will load it into pandas again, to_csv is thirty times faster and costs nothing over the frame. A surprising number of "Excel exports" are CSV with a worse extension. The question is worth asking before the engine is.

Fix 3: do not build the frame at all

If the data comes from a database and the frame exists only to be written out, the frame is the cost. Iterate the query with a server-side cursor and write rows straight into XlsxWriter with constant_memory, and the process holds one row. That is a different page, on openpyxl and XlsxWriter without pandas, where the same export ran at a flat 20 MB.

What we would do

If you have a frame and need an .xlsx, use XlsxWriter with constant_memory. That is the honest answer and it is one keyword argument.

If the file needs number formats, a chart, or a second sheet styled the way a finance team expects, to_excel will not produce it and you are about to write a hundred lines of XlsxWriter formatting code that lives in a data pipeline. That is what Jarrah is for. Hand it the rows and get back a styled .xlsx built outside your process.

Write them as NDJSON straight out of the frame, one row per line, so nothing beyond the frame is ever held:

import json, os, requests

def ndjson(df):
    yield (json.dumps({"sheets": [{"name": "Invoices", "columns": [
        {"header": "Customer"},
        {"header": "Amount", "style": "money"},
        {"header": "Issued"},
        {"header": "Status"},
    ]}]}) + "\n").encode()
    for record in df.itertuples(index=False, name=None):
        yield (json.dumps(record) + "\n").encode()

job = requests.post(
    "https://api.jarrah.sh/v1/jobs",
    data=ndjson(df),                      # 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: 123 MB at 200,000 rows, which is the frame plus the requests import and nothing that grows with the data. The two seconds are json.dumps on 800,000 values, which is the same per-cell loop as to_excel without the XML.

For small frames 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.