How do I determine the end of the file using PHPExcel library in chunk loop?

Question

Using the PHPExcel library, I am attempting to iterate over around 1500 rows, each row has about 25 columns.

I am using this code (taken from PHPExcel runs out of 256, 512 and also 1024MB of RAM):

/**  Create a new Reader of the type defined in $inputFileType  **/
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
/**  Define how many rows we want to read for each "chunk"  **/ 
$chunkSize = 20;
/**  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  **/ 
/**  $startRow is set to 2 initially because we always read the headings in row #1  **/
for ($startRow = 2; $startRow <= 65536; $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); 
//    Do some processing here 

//    Free up some of the memory 
$objPHPExcel->disconnectWorksheets(); 
unset($objPHPExcel);

}

I want to process as many rows have data. I tried using the method getHighestRow() from the Worksheet object, but it just kept returning A1

I also tried to check if the next retrieved row was empty by writing this little function:

function _emptyRow($row) {
    $empty=true;

    foreach($row as $r) {
    if ($r!='') {
        $empty = false;
        }
    }

    return $empty;

}

So if _emptyRow() I will break out of the loop . This didnt work for me.

Can anyone suggest a way to only retrieve all the records which have data? When I run this it even though only about 1500 rows have data it gets to about 23000 before timing out ( with set_time_limit(240));

score 4 · Answer 1 · answered Apr 02 '12 at 07:36

I would normally do this:

$objPHPExcel = $objReader->load($inputFileName);    
$rows = count($objPHPExcel->getActiveSheet()->toArray());    
for ($start_row = 1; $start_row < $rows; $start_row ++)
// ...

$excel->getActiveSheet()->toArray() will only return each row (with data) in an array.

score 3 · Answer 2 · answered Apr 02 '12 at 08:19

For version 1.7.6 and below, Christopher Mullins has written a patch that allows you to read worksheet information before reading the entire file. This information includes the row and column counts for each worksheet, so you could use this to retrieve the rowcount before "chunk reading" the actual workbook data.

This patch has now been incorporated into the latest SVN code, so in the future it will be part of the PHPExcel core.

How do I determine the end of the file using PHPExcel library in chunk loop?

2 Answers2

Linked

Related