Excel's 1,048,576-row limit: what your export library does when you cross it, and how to ship more rows

Excel's grid ends at row 1,048,576. That is a property of the file format since Excel 2007, not of your machine or your library, and no amount of memory raises it. What varies is what your library does on row 1,048,577, and the answer, measured across four of them, is that none of them stops you.

What each library does at row 1,048,577

One column, one row past the limit, on the same machine, and then the zip opened to see what actually landed in xl/worksheets/sheet1.xml:

  openpyxl, write_only       saves with no error     row 1048577 is in the file
  XlsxWriter, constant_memory  closes with no error    row 1048577 is NOT in the file
  ExcelJS, WorkbookWriter    commits with no error   row 1048577 is in the file
  SheetJS, writeFile         writes with no error    row 1048577 is in the file; !ref says A1:A1048577

Three of the four produce a workbook with a row the grid cannot hold. Excel has to repair such a file or refuse it, and either way the rows past the line are gone when it opens. The fourth, XlsxWriter, checks the limit and then tells you in the quietest way a library can: write_number returns -1, which no example code ever checks, and raises no warning. The row simply never existed.

So the failure is discovered by whoever opens the file, which is the worst place for it: your export job reported success, the file is on the share, and a customer is reading a table that ends somewhere short of the truth.

Fix 1: split across worksheets

A workbook holds as many sheets as you like, each with its own 1,048,576 rows. The loop is the same shape in every library: count rows, start a new sheet at the limit, repeat the header. XlsxWriter, with constant_memory so the split does not undo the memory fix:

import xlsxwriter

LIMIT = 1_048_576 - 1                      # minus the header row

wb = xlsxwriter.Workbook("orders.xlsx", {"constant_memory": True})
header = ["Customer", "Amount", "Issued", "Status"]

sheet_no, row = 0, LIMIT                   # forces a new sheet on the first row
for order in orders:                       # a cursor, not a list
    if row == LIMIT:
        sheet_no += 1
        ws = wb.add_worksheet(f"Orders {sheet_no}")
        ws.write_row(0, 0, header)
        row = 0
    row += 1
    ws.write_row(row, 0, [order.customer, order.amount, order.issued, order.status])

wb.close()

ExcelJS is the same loop with addWorksheet and commit(); openpyxl's is create_sheet and append. Name the sheets with a number and keep the header on each: a reader who lands on sheet three without a header has no idea what column B is.

Say in the file that it was split. A total on sheet one that only covers sheet one is a wrong number with a confident face. If there is a summary sheet, put it first and make it cover all of them.

Fix 2: ask whether it needs to be Excel

A million rows is not something a person reads. If the file is going into a pipeline, a BI tool, pandas, or anything that is not a human with a mouse, CSV has no row limit, writes in a fraction of the time, and every consumer reads it. Parquet is smaller and typed. The question is worth asking before the split is written, because the answer is often that the spreadsheet was never the point and the row limit was the format telling you so.

Fix 3: do not ship the rows

The export that hits the limit is usually a raw table somebody asked for because the summary they wanted did not exist. A sheet of monthly totals with the raw rows in a CSV beside it serves the reader better than a workbook they cannot scroll, and it is the only version that stays under the limit as the data grows. This is the fix least often taken and the one that lasts.

What we would do

Split across sheets with XlsxWriter, or with ExcelJS's streaming writer in Node. This page is the one where Jarrah does nothing the libraries cannot, and it would be dishonest to pretend otherwise.

What Jarrah does differently is refuse, early and by name, rather than write a file that will not open. A JSON submission with too many rows in one sheet is rejected before anything is rendered, with a JSON Pointer to the sheet:

{
  "type": "https://docs.jarrah.sh/errors/invalid-workbook",
  "title": "Workbook failed validation",
  "status": 422,
  "errors": [
    { "pointer": "/sheets/0/rows",
      "message": "1048577 rows including the header; a worksheet holds at most 1048576" }
  ]
}

Splitting is then the same five lines in the body you build: several entries in sheets, a million rows each, one request. A streamed NDJSON job goes into one sheet, so a stream past the limit has to be cut into separate jobs on your side; that job is accepted and then fails on its first attempt with the row named. When this page was first measured it took three attempts, a bug in our queue that the measurement found and that has since been fixed. For the size of file Jarrah will build without holding it in your process, see the other pages here. For this limit, we share it with everyone.