php - How to fix memory getting exhausted with PHPExcel?

Question

Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 1078799 bytes) in D:xampplitehtdocsScraperPHPExcelReaderExcel2007.php on line 269

My 128M PHP memory limit quickly gets exhausted even when I am only trying to open a small excel file of ~350 KB with PHPExcel.

Although, I can increase the memory limit in the configuration but it'll be great to see if there are any alternatives to fix this.

Answer 1

File size isn't a good measure for workbook files when working with PHPExcel. The number of rows and columns (ie cells) is more important.

The PHPExcel code itself has a footprint of between 10 and 25MB, depending on which components are being accessed.

At present, each cell in a workbook takes on average 1k of memory (without any caching) or 1.6k on 64-bit PHP - I'll assume 32-bit PHP for the moment - so (for example) a worksheet of 8000 lines with 31 columns (248,000 cells) will be about 242MB. With cell cacheing (such as php://temp or DiskISAM), that can be reduced to about a third, so the 8000 lines by 31 columns will require about 80MB.

There are a number of options available to help you reduce the memory usage:

Are you using cell caching with PHPExcel?

require_once './Classes/PHPExcel.php';

$cacheMethod = PHPExcel_CachedObjectStorageFactory:: cache_to_phpTemp;
$cacheSettings = array( ' memoryCacheSize ' => '8MB');
PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);

$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objPHPExcel = $objReader->load("test.xlsx");

If you only need to access data in your worksheets, and don't need access to the cell formatting, then you can disable reading the formatting information from the workbook:

$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load("test.xlsx");

If you only need to access some, but not all of the worksheets in the workbook, you can load only those worksheets:

$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objReader->setLoadSheetsOnly( array("Worksheet1", "Worksheet2") );
$objPHPExcel = $objReader->load("test.xlsx");

if you only want to read certain cells within worksheets, you can add a filter:

class MyReadFilter implements PHPExcel_Reader_IReadFilter
{
    public function readCell($column, $row, $worksheetName = '') {
        // Read title row and rows 20 - 30
        if ($row == 1 || ($row >= 20 && $row <= 30)) {
            return true;
        }

        return false;
    }
}

$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objReader->setReadFilter( new MyReadFilter() );
$objPHPExcel = $objReader->load("test.xlsx");

All of these techniques can significantly reduce the memory requirements.