2

I'm using phpExcel to read a rather large XML file. I'm trying to do this in chunks, as is indicated in the examples. But no matter what I try, the "$objPHPExcel = $objReader->load($inputFileName)" always fails and results in an error saying that memory is exhausted.

I have seen http://phpexcel.codeplex.com/discussions/242712?ProjectName=phpexcel and How to read large worksheets from large Excel files (27MB+) with PHPExcel? but every code I tried failed at the same load method.

This is a part of the code I'm currently using: 

set_include_path(get_include_path() . PATH_SEPARATOR . '../../../Classes/');

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


$inputFileType = 'Excel5';
//  $inputFileType = 'Excel2007';
//  $inputFileType = 'Excel2003XML';
//  $inputFileType = 'OOCalc';
//  $inputFileType = 'Gnumeric';
$inputFileName = 'testfile.xls';


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

    private $_endRow = 0;

    /**  We expect a list of the rows that we want to read to be passed into the constructor  */
    public function __construct($startRow, $chunkSize) {
        $this->_startRow    = $startRow;
        $this->_endRow      = $startRow + $chunkSize;
    }

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


$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objReader->setReadDataOnly(true);
echo '<hr />';


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


/**  Loop to read our worksheet in "chunk size" blocks  **/
for ($startRow = 2; $startRow <= 240; $startRow += $chunkSize) {
    echo 'Loading WorkSheet using configurable filter for headings row 1 and for rows ',$startRow,' to ',($startRow+$chunkSize-1),'<br />';
    /**  Create a new Instance of our Read Filter, passing in the limits on which rows we want to read  **/
    $chunkFilter = new chunkReadFilter($startRow,$chunkSize);
    /**  Tell the Reader that we want to use the new Read Filter that we've just Instantiated  **/
    $objReader->setReadFilter($chunkFilter);
    /**  Load only the rows that match our filter from $inputFileName to a PHPExcel Object  **/
    echo "before load <br />";
    $objPHPExcel = $objReader->load($inputFileName);
    echo "after load";
    //  Do some processing here

    $sheetData = $objPHPExcel->getActiveSheet()->toArray(null,true,true,true);
    var_dump($sheetData);
    echo '<br /><br />';
}

So I wonder if anyone has an idea why none of the solutions in the other topics work?

Thanks,

Grtz

Community
  • 1
  • 1
user1254962
  • 153
  • 5
  • 15
  • what's the value of memory_limit in your php.ini – Mark Basmayor Jun 25 '13 at 20:11
  • 1
    You probably want to disconnect the worksheet and unset the PHPExcel object at the end of your loop (as described in the documentation); and you might also want to look at cell caching – Mark Baker Jun 25 '13 at 20:23
  • Ok the caching seems to have done the job, it does not crash anymore (it is however still quite slow, but that may be normal?). Thanks for the quick responses! – user1254962 Jun 25 '13 at 21:25
  • 1
    Caching is a trade-off between memory and speed: moving data out of PHP memory (or shrinking objects in PHP memory) adds extra processing overhead - the different caching options offer different trade-offs between memory and speed - personally, I tend to use SQLite/SQLite3 as an optimum; but that is a personal preference – Mark Baker Jun 25 '13 at 22:19
  • By varying the chunk size I'm able to get a decent reading speed. Thank you for the quick responses. – user1254962 Jun 26 '13 at 07:15

0 Answers0