PHPExcel 1.8.0 memory exhausted on load, chunk read, and iterator

Question

I have already set my php configuration setttings to upload 12800M files, inifinite size files, and upload time infinity for testing, but I have been stuck on this common PHPExcel fatal memory exhausted error. I receive the common error message below:

Fatal error: Allowed memory size of 536870912 bytes exhausted (tried to allocate 32 bytes) in .../Worksheet.php on line 1220.

When I use chunk read filters or iterators, as the .xlsx file is read further into the rows, the memory usage increases instead of staying the same as some have reported from what I read from PHPExcel developers I believe.

I am on PHPExcel 1.8.0. I might try an older version, since I have read that reading large files have been performing better. I started with a regular load the file and read it into an array, use chunk read filtering as in the example, and iterator at this URL: PHPExcel - memory leak when I go through all rows. I would think that it wouldn't matter if it was version 1.8.0 or older.

<?php

/** Error reporting */
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
date_default_timezone_set('America/Los_Angeles');

define('EOL',(PHP_SAPI == 'cli') ? PHP_EOL : '<br />');

/** Include PHPExcel_IOFactory */
require_once dirname(__FILE__) . '/../Classes/PHPExcel/IOFactory.php';


if (!file_exists("Test.xlsx")) {
    exit("Please check if Test.xlsx exists first.\n");
}

echo date('H:i:s') , " Load workbook from Excel5 file" , EOL;
$callStartTime = microtime(true);

$objPHPExcel = PHPExcel_IOFactory::load("Order_Short.xls");

$callEndTime = microtime(true);
$callTime = $callEndTime - $callStartTime;

echo 'Call time to load Workbook was ' , sprintf('%.4f',$callTime) , " seconds" , EOL;
// Echo memory usage
echo date('H:i:s') , ' Current memory usage: ' , (memory_get_usage(true) / 1024 / 1024) , " MB" , EOL;


//http://runnable.com/Uot2A2l8VxsUAAAR/read-a-simple-2007-xlsx-excel-file-for-php

//  Read your Excel workbook
$inputFileName="Drybar Client Data for Tableau Test.xlsx";
$table = "mt_company2_project2_table144_raw";

try {
    echo date('H:i:s') , " Load workbook from Excel5 file" , EOL;
    $callStartTime = microtime(true);

    $inputFileType = PHPExcel_IOFactory::identify($inputFileName);
    $objReader = PHPExcel_IOFactory::createReader($inputFileType);
    $objPHPExcel = $objReader->load($inputFileName);

    $callEndTime = microtime(true);
    $callTime = $callEndTime - $callStartTime;

    echo 'Call time to load Workbook was ' , sprintf('%.4f',$callTime) , " seconds\r\n" , EOL;
    // Echo memory usage
    echo date('H:i:s') , ' Current memory usage: ' , (memory_get_usage(true) / 1024 / 1024) , " MB" , EOL;
} catch (Exception $e) {
    die('Error loading file "' . pathinfo($inputFileName, PATHINFO_BASENAME)
        . '": ' . $e->getMessage());
}

// Get worksheet dimensions
$sheet = $objPHPExcel->getSheet(0);

// http://asantillan.com/php-excel-import-to-mysql-using-phpexcel/
$worksheetTitle = $sheet->getTitle();

$highestRow = $sheet->getHighestRow();
$highestColumn = $sheet->getHighestColumn();
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);

// Calculationg Columns
$nrColumns = ord($highestColumn) - 64;

echo "File ".$worksheetTitle." has ";
echo $nrColumns . ' columns';
echo ' x ' . $highestRow . ' rows.<br />';

//  Loop through each row of the worksheet in turn
for ($row = 1; $row <= $highestRow; $row++) {
    //  Read a row of data into an array
    $rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row,
                                    NULL, FALSE, FALSE);

    var_dump($rowData);

    foreach ($rowData[0] as $k => $v) {
        echo "Row: " . $row . "- Col: " . ($k + 1) . " = " . $v . "<br />";
    }

}

I am including chunk read filter modified from PHPExcel Reader Example #12 that still gives me the same fatal memory exhausted since it's memory usage is increasing still as it read further down the rows?

<?php

error_reporting(E_ALL);
set_time_limit(0);

define('EOL',(PHP_SAPI == 'cli') ? PHP_EOL : '<br />');

date_default_timezone_set('America/Los_Angeles');

/**  Set Include path to point at the PHPExcel Classes folder  **/
set_include_path(get_include_path() . PATH_SEPARATOR . '../../../Classes/');

/**  Include PHPExcel_IOFactory  **/
include 'PHPExcel/IOFactory.php';

$inputFileName = 'Test.xlsx';


/**  Define a Read Filter class implementing PHPExcel_Reader_IReadFilter  */
class chunkReadFilter implements PHPExcel_Reader_IReadFilter
{
    private $_startRow = 0;

    private $_endRow = 0;

    /**  Set the list of rows that we want to read  */
    public function setRows($startRow, $chunkSize) {
        $this->_startRow    = $startRow;
        $this->_endRow      = $startRow + $chunkSize;
    }

    public function readCell($column, $row, $worksheetName = '') {
        //  Only read the heading row, and the rows that are configured in $this->_startRow and $this->_endRow
        if (($row == 1) || ($row >= $this->_startRow && $row < $this->_endRow)) {
            return true;
        }
        return false;
    }
}

$inputFileType = PHPExcel_IOFactory::identify($inputFileName);

$callStartTime = microtime(true);

echo 'Loading file ',pathinfo($inputFileName,PATHINFO_BASENAME),' using IOFactory with a defined reader type of ',$inputFileType,'<br />';

// Call time

$callEndTime = microtime(true);
$callTime = $callEndTime - $callStartTime;
echo 'Call time to read Workbook was ' , sprintf('%.4f',$callTime) , " seconds" , EOL;

// Echo memory usage
echo date('H:i:s') , ' Current memory usage: ' , (memory_get_usage(true) / 1024 / 1024) , " MB" , EOL;

/**  Create a new Reader of the type defined in $inputFileType  **/
$objReader = PHPExcel_IOFactory::createReader($inputFileType);

echo '<hr />';


/**  Define how many rows we want to read for each "chunk"  **/
$chunkSize = 100;
/**  Create a new Instance of our Read Filter  **/
$chunkFilter = new chunkReadFilter();

/**  Tell the Reader that we want to use the Read Filter that we've Instantiated  **/
$objReader->setReadFilter($chunkFilter);
/**  Loop to read our worksheet in "chunk size" blocks  **/
for ($startRow = 2; $startRow <= 26000; $startRow += $chunkSize) {
    echo 'Loading WorkSheet using configurable filter for headings row 1 and for rows ',$startRow,' to ',($startRow+$chunkSize-1),'<br />';
    /**  Tell the Read Filter, the limits on which rows we want to read this iteration  **/
    $chunkFilter->setRows($startRow,$chunkSize);
    /**  Load only the rows that match our filter from $inputFileName to a PHPExcel Object  **/
    $objPHPExcel = $objReader->load($inputFileName);

    //  Do some processing here

    $sheetData = $objPHPExcel->getActiveSheet()->toArray(null,true,true,true);

    echo '<br /><br />';

    // Call time

    $callEndTime = microtime(true);
    $callTime = $callEndTime - $callStartTime;
    echo 'Call time to read Workbook was ' , sprintf('%.4f',$callTime) , " seconds" , EOL;

    // Echo memory usage
    echo date('H:i:s') , ' Current memory usage: ' , (memory_get_usage(true) / 1024 / 1024) , " MB" , EOL;

    // Echo memory peak usage
    echo date('H:i:s') , " Peak memory usage: " , (memory_get_peak_usage(true) / 1024 / 1024) , " MB" , EOL;
    echo '<hr />';
}

?>
<body>
</html>

Answer 1

You do have a major problem here:

$sheetData = $objPHPExcel->getActiveSheet()->toArray(null,true,true,true);

which will try to build an array for the entire size of the worksheet regardless of whether you chunk loaded or not.... toArray() uses the expected size of the spreadsheet based on the file that you're loading, not the filtered set of cells that you're loading

Try getting only the range of cells that you've loaded through the chunk using rangeToArray() instead

$sheetData = $objPHPExcel->getActiveSheet()
    ->rangeToArray(
        'A'.$startRow.':'.$objPHPExcel->getActiveSheet()->getHighestColumn().($startRow+$chunkSize-1),
        null,
        true,
        true,
        true
    );

Even then, building PHP arrays in memory uses a lot of memory; your code will be a lot less memory-hungry if it can process that worksheet data one row at a time rather than populating a large array