Building very large spreadsheet with PHPSpreadsheet

Building very large spreadsheet with PHPSpreadsheet

  1. Blog
  2. 15 November 2026
  3. 4 min read

Stefan Bogdanescu

Founder & Senior Architect

Building very large spreadsheet with PHPSpreadsheet

When you attempt to generate a massive Excel file using PHPSpreadsheet, the default behavior is often immediate and destructive: your script hits memory_limit before it finishes writing a single row. This isn't a bug; it's a consequence of how the library constructs objects. PHPSpreadsheet builds an in-memory representation of the entire workbook, populating cells, styles, and formulas before flushing anything to disk. For small datasets, this is convenient. For large spreadsheets, it is unsustainable.

The core issue lies in the object graph. Every cell you define becomes a PHP object. If you are generating a report with 100,000 rows and 50 columns, you are instantiating millions of objects. These objects hold references to style objects, formula parsers, and data types. As the memory usage climbs, PHP's garbage collector struggles to keep up because these objects remain alive in the scope until the script ends. Increasing memory_limit is a band-aid that eventually leads to a fatal error or an Out Of Memory (OOM) crash on the server side, which can even kick off swap space and slow down the entire server.

To handle large files without blowing up memory, you need to shift from an in-memory build process to a streaming approach. This requires breaking away from the standard Writer->save() pattern that expects the whole object tree to exist at once. Instead, you must leverage the underlying engine's ability to write directly to the file system as data is generated.

The solution involves using the Xlsx writer with specific configuration options that allow for progressive writing. While PHPSpreadsheet's high-level API encourages building the sheet entirely first, the library supports a mode where cells are written immediately upon creation if configured correctly. This mimics the behavior of older, lower-level libraries where you could open a file handle and write bytes as they arrived.

Here is the conceptual shift required in your code structure:

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

// Configure the writer to handle large files
$writer = new Xlsx($spreadsheet);

// Enable the feature that allows writing without buffering the entire file
$writer->setUseZip(false); // Optional: Sometimes helps with memory, but not always recommended for .xlsx
// More importantly, we need to ensure we aren't holding huge arrays in memory.

// Instead of looping and adding cells to an array then saving,
// we add them and save incrementally if the underlying implementation supports it.
// However, PHPSpreadsheet's standard Xlsx writer buffers the zip content.

// The most robust approach for massive files often requires a different strategy:
// Do not use the standard Writer class for the entire file at once.
// Instead, consider generating chunks and concatenating them, or 
// utilizing a lower-level engine like PHPSpreadsheet's internal ZipStream capabilities
// if available in your version, though native streaming support is limited.

// A practical workaround for "progressive" writing with PHPSpreadsheet:
// 1. Create the workbook and writer.
// 2. Loop through your data source.
// 3. For every N rows (e.g., 500), call $writer->save() to that specific chunk 
//    or use a temporary file strategy if the library doesn't support partial saves natively.

// Note: PHPSpreadsheet does not natively support "open handle, write row, flush" 
// out of the box for .xlsx files because .xlsx is essentially a zip archive.
// To achieve true streaming without memory spikes, you often need to:
// - Write the file in chunks (e.g., 10k rows at a time).
// - Concatenate the resulting ZIP files into one final file using shell commands 
//   or stream copying logic after processing batches.

$writer->save('/tmp/large_report.xlsx'); // This will still load everything for the whole file

As seen in the code above, calling $writer->save() typically forces PHPSpreadsheet to buffer the entire zip archive into memory before writing it out. This is the exact mechanism that causes the OOM issue. To circumvent this, you must adopt a chunking strategy. You build a section of your spreadsheet (e.g., 5,000 rows), save it as a temporary file, clear the memory by destroying the workbook object, and repeat for the next batch. Finally, you concatenate these temporary files into the master spreadsheet using standard file operations or a tool like cat.

This approach introduces trade-offs. The resulting file will have multiple sheets if not handled carefully (you'd need to merge them programmatically), or you must write directly to the archive stream of the final file in chunks. If your goal is strictly progressive writing to a single .xlsx file without intermediate concatenation steps, PHPSpreadsheet's current architecture makes this difficult without significant custom engineering around its ZipStream integration.

If your use case demands true streaming where rows are appended to an open file handle immediately, you might find yourself looking at alternative libraries like PhpSpreadsheet's internal stream writers or switching to a format like CSV, which is inherently streamable and doesn't require complex zip manipulation. For .xlsx specifically, the "chunk-and-concatenate" method remains the most reliable way to scale beyond standard PHP memory limits without upgrading hardware.

Our network