Reading Excel Files in PHP With PhpSpreadsheet Efficiently

Read and write Excel in PHP with PhpSpreadsheet: load sheets, typed cells and dates, read filters and chunks for memory, exports and formula injection.

7 min read

Summarize in AI

Quick Summary

Install phpoffice/phpspreadsheet with Composer, load with IOFactory and a reader set to read data only, convert the sheet with toArray or iterate rows, and convert Excel dates with Date::excelToDateTimeObject. For large files read in chunks with a read filter or use OpenSpout, and set memory limits.

Key takeaways

PhpSpreadsheet reads and writes xlsx, xls, ods and csv, and IOFactory picks the reader from the file.
It builds the whole workbook in memory, so a file of modest size can exhaust PHP's memory limit unless the read is limited to what is needed.
Setting readDataOnly, loading only named sheets and using a read filter for row chunks reduce memory far more than raising the limit.
Dates come as serial numbers and must be converted with the Date helper, with the workbook's calendar in mind.
OpenSpout streams rows with constant memory and suits imports and exports of large files that need no styling.

PHP applications read spreadsheets for the usual reasons: an admin uploads a product list, a finance user imports a ledger, a report is exported for download. PhpSpreadsheet is the standard library for it and the successor to PHPExcel. It is capable, and it is also hungry: it creates an object for every cell, and PHP's default memory limit is soon exceeded. This post shows how to read and write, how to keep memory under control, how to deal with dates and types, and what to do with files from users. It is a spoke of reading and writing Excel from JavaScript, Java and C Sharp.

Install

Shell

composer require phpoffice/phpspreadsheet

It needs the PHP extensions zip, xml, gd (for images, optional) and mbstring. Autoload with Composer's vendor/autoload.php.

Read a sheet

PHP

<?php
require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\IOFactory;

$reader = IOFactory::createReaderForFile('orders.xlsx');
$reader->setReadDataOnly(true);            // skip styles, which saves memory
$reader->setLoadSheetsOnly(['Orders']);    // skip other sheets
$spreadsheet = $reader->load('orders.xlsx');

$sheet = $spreadsheet->getActiveSheet();
$rows = $sheet->toArray(null, true, true, false);   // nullValue, calculateFormulas, formatData, returnCellRef
$header = array_shift($rows);

IOFactory::createReaderForFile chooses the reader (Xlsx, Xls, Ods, Csv) from the file. setReadDataOnly(true) ignores formatting, which also means cell formats are not applied to values; a date cell is a number. toArray's third argument, formatData, controls whether values are formatted as Excel displays them: true returns display strings like 1,234.50, false returns raw values. For data work, raw values are what you want, with explicit conversions.

Unlike some libraries, toArray returns all rows up to the used range, including trailing empty rows with formatting, so filter them:

PHP

$rows = array_filter($rows, fn($r) => count(array_filter($r, fn($v) => $v !== null && $v !== '')) > 0);

Iterate instead of converting the whole sheet when you want to process rows one by one:

PHP

foreach ($sheet->getRowIterator(2) as $row) {          // start at row 2
    $cells = $row->getCellIterator();
    $cells->setIterateOnlyExistingCells(false);
    $values = [];
    foreach ($cells as $cell) { $values[] = $cell->getValue(); }
}

Rows and columns are one-based, as in Excel. $sheet->getCell('B2')->getValue() reads a single cell, and getCalculatedValue() returns the result of a formula, calculated by PhpSpreadsheet's engine, which supports many but not all functions. getOldCalculatedValue() returns the cached result saved in the file. For a formula cell, getValue() returns the formula text (=SUM(A1:A5)), a frequent surprise (handling Excel formulas and cached values).

Dates and types

Excel dates are serial numbers. Convert with the helper:

PHP

use PhpOffice\PhpSpreadsheet\Shared\Date;

$serial = $sheet->getCell('B2')->getValue();            // 45730.0
$dt = Date::excelToDateTimeObject($serial);             // DateTime, UTC
echo $dt->format('Y-m-d');                              // 2025-03-14

Check that the cell is a date before converting: Date::isDateTime($cell) looks at the cell's number format (and so needs styles loaded, which setReadDataOnly(true) skips). With data-only reads, convert columns that you know are dates. A workbook using the 1904 date system shifts every date; the helper takes the calendar into account when you give it the workbook's setting (Date::setCalendar(Date::CALENDAR_MAC_1904) before converting). Identifiers should be read as strings: a cell stored as text stays text, while a numeric cell 123 is a float 123.0. If you need leading zeros preserved from a numeric cell, you cannot recover them; ask for the column to be formatted as text.

Empty cells return null. Booleans return true or false. Errors such as #N/A come back as strings in some paths; test with $cell->getDataType() (DataType::TYPE_ERROR).

Memory: the main problem

PhpSpreadsheet loads every cell into memory as an object. A file with 100,000 rows and ten columns is a million cells, and it can use hundreds of megabytes. PHP's default memory_limit of 128 MB fails long before that. Raising the limit to a gigabyte fixes the symptom on your machine and fails under concurrent users. Better, reduce what you load.

Read data only and only the sheets you need, as above.

Read in chunks with a read filter. A filter tells the reader which cells to load, so you load a block of rows, process it, free it and load the next:

PHP

use PhpOffice\PhpSpreadsheet\Reader\IReadFilter;

class ChunkFilter implements IReadFilter {
    private int $start = 0; private int $end = 0;
    public function setRows(int $start, int $size): void { $this->start = $start; $this->end = $start + $size; }
    public function readCell(string $columnAddress, int $row, string $worksheetName = ''): bool {
        return $row == 1 || ($row >= $this->start && $row < $this->end);
    }
}

$filter = new ChunkFilter();
$reader = IOFactory::createReader('Xlsx');
$reader->setReadDataOnly(true);
$reader->setReadFilter($filter);

for ($start = 2; $start <= 100000; $start += 5000) {
    $filter->setRows($start, 5000);
    $spreadsheet = $reader->load('big.xlsx');
    $rows = $spreadsheet->getActiveSheet()->toArray(null, false, false, false);
    process($rows);
    $spreadsheet->disconnectWorksheets();
    unset($spreadsheet);
}

This keeps memory flat, but each load re-parses the XML from the start, so the time is quadratic in the number of chunks. For files beyond a few hundred thousand rows, use a streaming reader instead.

Use cell caching. PhpSpreadsheet can store cells in a PSR-16 cache (Settings::setCache(...)), trading speed for memory.

Use OpenSpout. The openspout/openspout package reads and writes xlsx, ods and csv as streams with constant memory, with no formulas and little styling:

PHP

use OpenSpout\Reader\XLSX\Reader;

$reader = new Reader();
$reader->open('big.xlsx');
foreach ($reader->getSheetIterator() as $sheet) {
    foreach ($sheet->getRowIterator() as $row) {
        $values = $row->toArray();      // cells as PHP values
    }
}
$reader->close();

For imports of large lists, it is the practical choice (reading a 500 MB Excel file without running out of memory covers the general problem).

Write a workbook

PHP

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Shared\Date;

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setTitle('Report');
$sheet->fromArray(['Order', 'Date', 'Amount'], null, 'A1');

$sheet->setCellValueExplicit('A2', '0007', DataType::TYPE_STRING);        // keep leading zeros
$sheet->setCellValue('B2', Date::PHPToExcel(new DateTime('2025-03-14')));
$sheet->getStyle('B2')->getNumberFormat()->setFormatCode('yyyy-mm-dd');
$sheet->setCellValue('C2', 1234.5);
$sheet->getStyle('C2')->getNumberFormat()->setFormatCode('#,##0.00');

$sheet->getStyle('A1:C1')->getFont()->setBold(true);
$sheet->freezePane('A2');
$sheet->setAutoFilter('A1:C2');
foreach (['A', 'B', 'C'] as $col) { $sheet->getColumnDimension($col)->setAutoSize(true); }

(new Xlsx($spreadsheet))->save('report.xlsx');

setCellValue guesses the type from the value, so a string that looks like a number may be stored as a number, and a string starting with = becomes a formula. Use setCellValueExplicit with a type when it matters. That formula behavior is a security issue for user data: a cell value such as =HYPERLINK("http://evil.test", "Click") or a DDE payload turns into a live formula when someone opens the file. Strip or prefix any leading =, +, - or @ in user-supplied text, or write it explicitly as a string (escaping formula characters in a CSV export describes the same attack in CSV).

To send a download without saving to disk, set headers and write to php://output:

PHP

header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment; filename="report.xlsx"');
(new Xlsx($spreadsheet))->save('php://output');

Files from users

Treat uploaded workbooks as untrusted input. Limit the upload size and the number of rows, validate the extension and the first bytes (a zip signature for xlsx), read with setReadDataOnly(true), disable external entity loading (PhpSpreadsheet does this by default in recent versions; keep the library updated, since XML parsing issues have been fixed in releases), and run the parse in a queue worker with its own memory limit and a timeout, not in the web request. Never trust the file name: generate your own. Check the structure (sheet name, header row, column count) before processing and return a clear error.

Check the result

Compare the count of rows and a control total with Excel. Look at dates, a formula cell, an empty cell, an identifier column and a cell with an error. Try a file saved by Excel, one from LibreOffice and one that a script produced.