Memory exceeding problem

Topics: User Forum
Apr 13, 2011 at 9:09 PM
Edited Apr 14, 2011 at 5:01 AM

I am using Linux OS, PHP version 5.2.8 and PHPExcel version 1.7.6. I am using PHPExcel to read data from Excel files. I understood from the documentation and the discussions here that it is possible to set read filters to read only chunks of rows from an Excel file at a time thereby restricting memory usage. I read that no matter what the size of the Excel file is, only the memory needed for the size of the chunk I am setting will be used. I have an Excel file with 60000 rows in the first sheet. I am reading only the first sheet and I have set the chunk size to 5000 rows. When I ran the program, the memory usage increased with each iteration of the read loop and it exceeded the 512 MB memory limit I have set when it went past the 30000th row of the Excel file. I can't understand why the memory usage kept increasing when the loop progressed because PHPExcel is supposed to use only memory needed for the 5000 chunk size throughout the loop. I am giving here the code I used. Please help me to know where I went wrong. Thanks.

 

The code:

<?php

error_reporting(E_ALL);
set_time_limit(0);

date_default_timezone_set('Europe/London');

?>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />

<title>PHPExcel Reader Example </title>

</head>
<body>

<h1>PHPExcel Reader Test</h1>
<?php
 
/** Include path **/
set_include_path(get_include_path() . PATH_SEPARATOR . '../../../Classes/');
 
/** PHPExcel_IOFactory */
include '../classes/PHPExcel/IOFactory.php';

/**  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;
    }
}


// Echo memory peak usage
echo date('H:i:s') . " Peak memory usage: " . (memory_get_peak_usage(true) / 1024 / 1024) . " MB\r<br>";

/**  Define how many rows we want to read for each "chunk"  **/
$chunkSize = 5000;

/**  Create a new Instance of our Read Filter  **/
$chunkFilter = new chunkReadFilter();
$inputFileType = 'Excel5';
$inputFileName = 'SKTR.xls';


$objReader = PHPExcel_IOFactory::createReader($inputFileType);

//  Read the list of Worksheet Names from the Workbook file  /
 $worksheetNames = $objReader->listWorksheetNames($inputFileName);
$objReader->setLoadSheetsOnly($worksheetNames[0]);

 /**  Tell the Reader that we want to use the Read Filter that we've Instantiated  **/
$objReader->setReadFilter($chunkFilter);
$objReader->setReadDataOnly(true);
 
for ($startRow = 1; $startRow <= 60000; $startRow += $chunkSize) {

    /**  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);
   
    //    Get the sheet values in an array
    $sheetData = $objPHPExcel->getActiveSheet()->toArray();
           
    echo date('H:i:s') . " Peak memory usage: " . (memory_get_peak_usage(true) / 1024 / 1024) . " MB\r<br>";
    $objPHPExcel->disconnectWorksheets();
    unset($objPHPExcel);
}

// Echo memory peak usage
echo date('H:i:s') . "last Peak memory usage: " . (memory_get_peak_usage(true) / 1024 / 1024) . " MB\r\<br>n";

?>
</body>
</html>

Coordinator
Apr 19, 2011 at 10:25 PM
$sheetData = $objPHPExcel->getActiveSheet()->toArray(); 

will read the entire worksheet into the array. On the first iteration of the loop, this will be rows 1 to 5000, on the second iteration it will be rows 1 to 10000 (even though only row 1 and rows 5001 to 10000 actually contain any data).

Try using

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

instead. This will only return the cells read during that chunk.

Oct 12, 2011 at 10:22 AM

Hello,

I am new to the PHPExcel.

 

I am trying to use the above code.

getting the error on the

//  Read the list of Worksheet Names from the Workbook file  /
 $worksheetNames = $objReader->listWorksheetNames($inputFileName);
$objReader->setLoadSheetsOnly($worksheetNames[0]);

listWorksheetNames

no defined.

Please help me out thanks