How Can I Optimize PHPExcel to Prevent Memory Exhaustion When Loading Large XLSX Files?

PHPExcel Overloads Memory When Loading Large Spreadsheets

Despite having 1024MB of RAM allocated, PHPExcel still encounters memory exhaustion when loading a 3MB XSLX file. The root cause lies in the memory-intensive process involved in representing large spreadsheets.

Optimizing PHPExcel Memory Usage

To address this issue, several optimization techniques can be employed:

Selective Sheet Loading

If only specific worksheets need to be loaded, use setLoadSheetsOnly() to limit the number of sheets read by the reader.

$objReader->setLoadSheetsOnly([
    'Data Sheet #1',
    'Data Sheet #3'
]);

Read Filters

To only retrieve specific cells or rows, a read filter can be used.

class MyReadFilter implements PHPExcel_Reader_IReadFilter {
    // ... implementation
}

$objReader->setReadFilter(new MyReadFilter());

Chunking

Reading large spreadsheets in chunks can minimize the memory footprint by only loading a portion of the data at a time.

$chunkSize = 20;
$chunkFilter = new chunkReadFilter();

for ($startRow = 2; $startRow < 65536; $startRow += $chunkSize) {
    $chunkFilter->setRows($startRow, $chunkSize);
    $objPHPExcel = $objReader->load($inputFileName);
}

Read Data Only

Setting readDataOnly to true loads only cell values without formatting data, reducing memory usage.

$objReader->setReadDataOnly(true);

Cell Caching

Cell caching stores cell objects in a compressed format, significantly reducing memory consumption but sacrificing speed.

Alternative Methods

Instead of using iterators and manually building an array, consider using PHPExcel's toArray() or rangeToArray() methods for efficient array generation.

The above is the detailed content of How Can I Optimize PHPExcel to Prevent Memory Exhaustion When Loading Large XLSX Files?. For more information, please follow other related articles on the PHP Chinese website!