Exporting a million rows to XLSX in plain PHP: flat memory instead of memory_limit = 2G

There are two kinds of PHP developers: those who have not yet tried to export a large report to Excel, and those who have already seen this:

Fatal error: Allowed memory size of 536870912 bytes exhausted

The scenario is always the same. The customer asks for an “export to Excel” button. On 500 rows of test data everything flies. Six months later the table holds 800,000 rows, and the export dies with an OOM. This is not a rare case: in the PhpSpreadsheet tracker people report that exporting 25,000 rows × 75 columns eats gigabytes of memory (issues #1011, #638), and in Laravel Excel a similar thread (#1079) is one of the most discussed.

The usual remedies:

• ini_set('memory_limit', '-1') — good until the first truly large report, or the second concurrent request;
• split the export into chunks and stitch several files together — it works, but the user wanted one file;
• move the export to a queue on a separate server with 16 GB of RAM — expensive, and it does not address the cause.

All of that fights the symptom. In this article we look at the cause and show how to export a million rows with flat memory use in plain PHP using avadim/fast-excel-writer. Everything below was verified against version 6.16.

Why general-purpose libraries fall over#

PhpSpreadsheet is an excellent library, and that is not sarcasm. It reads and writes a dozen formats, evaluates formulas, edits existing files. But versatility has a price: before saving a file it builds a complete object model of the workbook in memory. Every cell is an object with a value, a type, coordinates, and a style reference. A million rows by ten columns is ten million objects alive at the same time, plus the collections, indexes, and caches around them.

For the task of “open a file, change three cells, save,” that model is necessary. For the task of “pour the result of a SQL query into an XLSX,” it is pure overhead: you pay memory for editing capabilities you never use.

The order of magnitude, from the benchmark in the FastExcelWriter README (generation without styles):

Look not at the exact numbers (they depend on the hardware) but at the shape of the curve: with PhpSpreadsheet memory grows linearly with the cell count, with FastExcelWriter it does not grow at all. Extrapolate the first column to a million rows and it becomes clear why no memory_limit will save you.

The streaming architecture: row → temp file → ZIP#

An XLSX is a ZIP archive of XML files, and the sheet XML is strictly sequential: row after row, top to bottom. FastExcelWriter takes that literally:

1. You write a row with writeRow() — it lives in memory as an ordinary array.
2. When you move on to the next row, the previous one is serialized to XML and flushed into the sheet's temporary file. It disappears from memory.
3. On save() the library appends the auxiliary XML parts (styles, metadata, relationships) and packs the temp files into a ZIP.

At any moment memory holds only the current row plus the shared structures (the style registry, column widths). That is why peak memory does not depend on the number of rows: a file with a million rows needs as much memory as a file with a thousand.

The price of this architecture is forward-only writing: you cannot go back to a row that has already been flushed. For data exports that limitation is nearly invisible, but it is worth knowing up front — more on that in the limitations section.

A minimal example#

composer require avadim/fast-excel-writer

use avadim\FastExcelWriter\Excel;

$excel = Excel::create(['Report']);
$sheet = $excel->sheet();

// Header with column formats: A — integer, B — string, C — date, D — money
$sheet->writeHeader([
    'ID'     => '@integer',
    'Name'   => '@string',
    'Date'   => '@date',
    'Amount' => '0.00',
]);

foreach ($rows as $row) {
    $sheet->writeRow($row); // the row went to the temp file, memory is free
}

$excel->save('report.xlsx');
// or send it straight to the browser:
// $excel->download('report.xlsx');

No flush(), no “economy modes,” no special wrappers — streaming here is not an option but the only way the library works.

A generator over PDO: a million rows without batches#

The classic advice to “read from the database in chunks of 5,000 rows” is only needed when the consumer accumulates data in memory. Here the consumer accumulates nothing, so it is enough to connect an unbuffered database cursor to writeRow() through a generator:

use avadim\FastExcelWriter\Excel;

/**
 * Yields result rows one by one without loading the whole result set into memory
 *
 * @return \Generator
 */
function fetchOrders(PDO $pdo)
{
    // For MySQL it is essential to turn off client-side result buffering,
    // otherwise the entire query result ends up in PHP memory
    $pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);

    $stmt = $pdo->query(
        'SELECT id, customer, created_at, status, amount FROM orders ORDER BY id'
    );
    while ($row = $stmt->fetch(PDO::FETCH_NUM)) {
        yield $row;
    }
}

$pdo = new PDO('mysql:host=localhost;dbname=shop;charset=utf8mb4', 'user', 'pass');

$excel = Excel::create(['Orders']);
$sheet = $excel->sheet();

$sheet->writeHeader(
    ['ID' => '@integer', 'Customer' => '@string', 'Created' => '@datetime', 'Status' => '@string', 'Amount' => '0.00'],
    ['font-style' => 'bold']
);

foreach (fetchOrders($pdo) as $row) {
    $sheet->writeRow($row);
}

$excel->save('orders.xlsx');

The chain runs end to end: a row leaves the database cursor, passes through the generator, gets serialized to XML, and goes to disk. Nowhere along the way does data pile up — memory is flat for any number of rows.

Check the numbers yourself#

Do not trust benchmarks from other people's READMEs (including this one) — a measurement script takes a minute to write. Here is a slightly simplified version of demo/demo-99-199k-rows.php from the library's repository:

use avadim\FastExcelWriter\Excel;

$rowCount = 1000000;
$timer = microtime(true);

$excel = Excel::create(['1M']);
$sheet = $excel->getSheet();
$sheet->setColFormats(['@integer', '@string', '0.00', '@string']);

for ($i = 1; $i <= $rowCount; $i++) {
    $sheet->writeRow([$i, 'item-' . ($i % 1000), ($i % 100) / 100, date('Y-m-d')]);
}

$excel->save('million.xlsx');

printf(
    "Rows: %d\nTime: %.1f sec\nPeak memory: %.1f MB\n",
    $rowCount,
    microtime(true) - $timer,
    memory_get_peak_usage(true) / 1024 / 1024
);

The order of magnitude you will see: a few megabytes of peak memory (essentially the buffers and the style registry) and minutes of time — throughput on simple data is measured in thousands of rows per second. A measurement on PHP 8.4: a million rows of four columns took about eight minutes and 4 MB of peak memory. The real test is to change $rowCount from 25,000 to a million: the time grows more than fortyfold, while the “Peak memory” line still shows the same 4 MB. That is exactly what separates streaming writing from an “optimized” object model.

The only thing that does grow is the temporary files on disk. If the system temp directory is small or sits in the wrong place, set your own: Excel::setTempDir('/path/to/tmp') before Excel::create().

Practical techniques#

Style by rows, not by cells. writeRow() and writeHeader() take a second argument — the style of the whole row. One style array per row is noticeably cheaper than individual styles for separate cells:

// Good: one style for the entire row
$sheet->writeRow($rowData, ['font-color' => '#900', 'fill-color' => '#fee']);

// More expensive: per-cell styles as the third argument of writeRow()
$sheet->writeRow($rowData, null, ['B' => ['fill-color' => '#fee']]);

Cheaper still are column formats via setColFormats() or writeHeader(['Col' => '@date']): they are set once for the whole sheet, and inside the write loop you need not think about styles at all.

Inline strings versus shared strings. By default the library writes strings directly into the sheet XML (inline) — that is faster and requires no string dictionary in memory. The shared strings table classic for XLSX is enabled with Excel::create([], ['shared_string' => true]): the file comes out more compact when values repeat often (statuses, city names), but the dictionary of unique strings lives in memory right up to save(). For the most predictable memory use, keep the default mode.

What to avoid. Do not collect data into an array before writing (“first I'll fetch everything from the database, then write it”) — that brings linear memory growth back, only now on your side. And do not use beginArea()/makeArea() areas for the main body of data: they are buffered in memory as a whole, and their place is in headers.

Honest limitations#

So there are no surprises on day two:

• Writing only, XLSX only. The library does not read or edit existing files. For reading there is the companion fast-excel-reader, for templates fast-excel-templator.
• Forward only. After writeRow() the previous row is already in the temp file — writing into it throws an exception. A “Total: N” line cannot be prepended to the top of the sheet after the fact: either compute the aggregates in advance (with a separate SQL query — that is cheap), or write the totals at the end of the sheet, or use formulas like =SUM(...) that Excel will compute itself.
• Not everything in the workbook is streamed. Rows go to the temp file immediately, but some entities live in memory until save(): area cells, merges, hyperlinks, notes, data validation and conditional formatting rules, charts, and — with shared_string — the string dictionary as well. The order of magnitude is a fraction of a kilobyte per element, so a flat export of a million rows costs almost nothing, while a million rows where every one gets a note, a hyperlink, or its own merge brings linear growth back. The cure is the same as in Excel itself: one rule per range instead of thousands of rules per cell.
• Headers with complex layout go through areas. If the report header must be filled in an arbitrary order, declare a buffered area: $area = $sheet->beginArea('A1'); or $sheet->makeArea('A1:F5');, write into it in any order via $area->setValue(), then flush it to the file with $sheet->writeAreas() — and continue row by row afterwards. An area is held in memory as a whole, so it suits a header of a few dozen rows, not a million rows of data.

If your task is “edit an existing file” or “evaluate formulas on the PHP side,” take PhpSpreadsheet and spare yourself the trouble. But for data export — which is most real-world Excel work on the web — a streaming writer is several times faster, and the memory gap widens with file size: in the table above it is a sevenfold difference at 1,000 × 25 and already a hundredfold at 10,000 × 50.

Summary#

• An OOM during an Excel export is not a reason to raise memory_limit and build queues around it: it is an architectural property of the object model, not a hardware shortage.
• Streaming “row → temp file → ZIP” gives flat memory: a million rows costs as many megabytes as a thousand.
• A generator over an unbuffered PDO cursor plus writeRow() — and batches are not needed at all.
• Row styles and column formats are cheap; areas and accumulating arrays are expensive.
• The forward-only limitation is the price of speed; for headers there is beginArea().

composer require avadim/fast-excel-writer

The library runs on PHP 7.4+ and needs only the standard zip, json, and mbstring extensions (plus the small avadim/fast-excel-helper package that the writer shares with the reader). Documentation and examples are in the GitHub repository and on Read the Docs.