Allowed memory size of 134217728 bytes exhausted (PHPSpreadsheet)

PHPSpreadsheet keeps the entire workbook in memory as an object graph. The error is not a leak or a misconfiguration. It is the library working as designed, on more rows than the design allows for.

Why it happens

Every cell you write becomes a Cell object holding its value, data type, a style index and a reference back to its parent worksheet. Rows and columns carry their own dimension objects. None of it is released until the workbook is saved, because the writer needs the whole thing: the shared string table, the style table and the dimensions are all computed across every cell before a single byte of XML is produced.

That works out at roughly 700–800 bytes per cell in practice. Measured on PHP 8.4 with PHPSpreadsheet 5.9, writing four simple columns of strings and numbers:

  10,000 rows   (40,000 cells)    53 MB peak
  20,000 rows   (80,000 cells)    80 MB peak
  50,000 rows  (200,000 cells)   160 MB peak   ← fails on the 128 MB default

So the default memory_limit = 128M gets you to about 20,000 rows of plain data. Add styling, a formula per row, or twenty columns instead of four, and it is considerably fewer. The traceback usually points at StringTable.php or a writer class, which is misleading: that is simply where the last allocation happened, not where the memory went.

Fix 1: move cells out of PHP's heap

PHPSpreadsheet can store cells in a PSR-16 cache instead of memory. This is the fix that keeps your existing code: the only change is one line at startup.

composer require symfony/cache

use PhpOffice\PhpSpreadsheet\Settings;
use Symfony\Component\Cache\Adapter\FilesystemAdapter;
use Symfony\Component\Cache\Psr16Cache;

Settings::setCache(
    new Psr16Cache(new FilesystemAdapter('phpspreadsheet', 0, sys_get_temp_dir()))
);

// ... the rest of your export is unchanged

On the same 50,000-row export this takes peak memory from 160 MB to 66 MB, which fits comfortably inside the default limit.

Fix 2: use a writer that streams

PHPSpreadsheet has no streaming writer, and cannot easily have one: the shared string table and style table are computed across the whole workbook. If your export is genuinely large, the right answer in PHP is OpenSpout, which writes each row to the file as you add it and never holds more than one row.

composer require openspout/openspout

use OpenSpout\Common\Entity\Row;
use OpenSpout\Writer\XLSX\Writer;
use OpenSpout\Writer\XLSX\Options;

$writer = new Writer(new Options());
$writer->openToFile($path);          // or openToBrowser($filename)

foreach ($invoices as $invoice) {
    $writer->addRow(Row::fromValues([
        $invoice->customer,
        $invoice->amount,
        $invoice->issued_at->format('Y-m-d'),
        $invoice->status,
    ]));
}

$writer->close();

Same data, same machine, memory_limit=128M:

  PHPSpreadsheet            50,000 rows   160 MB    5s   (fails at 128 MB)
  PHPSpreadsheet + cache    50,000 rows    66 MB   50s
  OpenSpout                 50,000 rows     2 MB    1s
  OpenSpout              1,000,000 rows     2 MB   ~20s

Memory is flat because nothing accumulates. A million rows costs the same 2 MB as fifty thousand.

The trade is features. OpenSpout does styling, merged cells and multiple sheets, but not formulas, charts, conditional formatting, or reading the formatting of an existing file. If your export is a table of data, which most exports are, you lose nothing. If it is a formatted report with computed totals, you do.

Fix 3: raise the limit, or move off the web request

ini_set('memory_limit', '512M') is the fix everybody tries first, and it works right up until it doesn't. Memory use grows linearly with cells, so every fixed limit has a row count that exceeds it, and the limit you pick becomes the size of export a single user can trigger on a shared web server.

It is listed third because it is a stopgap rather than a fix. If you take it, take the other half too: move the export off the request and into a queued job, so a large one cannot occupy a PHP-FPM worker for a minute or exhaust the pool when three people click at once.

What we would do

If the export is a data table and you can change the writer, use OpenSpout. That is the honest answer and it costs an afternoon.

If you cannot change the writer, or the export is a formatted report, 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, so your side never holds more than one row either. Handing every row to json_encode at once would just move the same 160 MB out of PHPSpreadsheet and into the request body:

$fh = fopen('php://temp/maxmemory:2097152', 'w+');

fwrite($fh, json_encode(['sheets' => [[
    'name'    => 'Invoices',
    'columns' => [
        ['header' => 'Customer'],
        ['header' => 'Amount', 'style' => 'money'],
    ],
]]]) . "\n");

foreach ($invoices as $invoice) {          // a generator, or an unbuffered query
    fwrite($fh, json_encode([$invoice['customer'], $invoice['amount']]) . "\n");
}
rewind($fh);

$ch = curl_init('https://api.jarrah.sh/v1/jobs');
curl_setopt_array($ch, [
    CURLOPT_POST           => true,
    CURLOPT_UPLOAD         => true,   // read the body from INFILE, chunked
    CURLOPT_INFILE         => $fh,
    CURLOPT_CUSTOMREQUEST  => 'POST',
    CURLOPT_RETURNTRANSFER => true,
    CURLOPT_HTTPHEADER     => [
        'Authorization: Bearer ' . getenv('JARRAH_KEY'),
        'Content-Type: application/x-ndjson',
    ],
]);
$job = json_decode(curl_exec($ch), true);  // poll /v1/jobs/{id}, or take a webhook

php://temp holds the first 2 MB in memory and spills the rest to a temporary file; CURLOPT_UPLOAD makes curl read it back in chunks rather than loading it. Measured against a local sink: 4 MB at 50,000 rows, 8 MB at 200,000, and still 8 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 50,000-row sheet takes the streaming path above.