PHPexcel Runs Out of 256, 512 and Also 1024Mb of Ram

PHPExcel runs out of 256, 512 and also 1024MB of RAM

There's plenty been written about the memory usage of PHPExcel on the PHPExcel forum; so reading through some of those previous discussions might give you a few ideas. PHPExcel holds an "in memory" representation of a spreadsheet, and is susceptible to PHP memory limitations.

The physical size of the file is largely irrelevant... it's much more important to know how many cells (rows*columns on each worksheet) it contains.

The "rule of thumb" that I've always used is an average of about 1k/cell, so a 5M cell workbook is going to require 5GB of memory. However, there are a number of ways that you can reduce that requirement. These can be combined, depending on exactly what information you need to access within your workbook, and what you want to do with it.

If you have multiple worksheets, but don't need to load all of them, then you can limit the worksheets that the Reader will load using the setLoadSheetsOnly() method.
To load a single named worksheet:

$inputFileType = 'Excel5'; 
$inputFileName = './sampleData/example1.xls';
$sheetname = 'Data Sheet #2'; 
/**  Create a new Reader of the type defined in $inputFileType  **/
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
/**  Advise the Reader of which WorkSheets we want to load  **/ 
$objReader->setLoadSheetsOnly($sheetname); 
/**  Load $inputFileName to a PHPExcel Object  **/
$objPHPExcel = $objReader->load($inputFileName);

Or you can specify several worksheets with one call to setLoadSheetsOnly() by passing an array of names:

$inputFileType = 'Excel5'; 
$inputFileName = './sampleData/example1.xls';
$sheetnames = array('Data Sheet #1','Data Sheet #3'); 
/** Create a new Reader of the type defined in $inputFileType **/ 
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
/** Advise the Reader of which WorkSheets we want to load **/ 
$objReader->setLoadSheetsOnly($sheetnames); 
/**  Load $inputFileName to a PHPExcel Object  **/
$objPHPExcel = $objReader->load($inputFileName);

If you only need to access part of a worksheet, then you can define a Read Filter to identify just which cells you actually want to load:

$inputFileType = 'Excel5'; 
$inputFileName = './sampleData/example1.xls';
$sheetname = 'Data Sheet #3'; 

/**  Define a Read Filter class implementing PHPExcel_Reader_IReadFilter  */ 
class MyReadFilter implements PHPExcel_Reader_IReadFilter {
    public function readCell($column, $row, $worksheetName = '') {
        //  Read rows 1 to 7 and columns A to E only 
        if ($row >= 1 && $row <= 7) {
           if (in_array($column,range('A','E'))) { 
              return true;
           }
        } 
        return false;
    }
}

/**  Create an Instance of our Read Filter  **/ 
$filterSubset = new MyReadFilter(); 
/** Create a new Reader of the type defined in $inputFileType **/ 
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
/**  Advise the Reader of which WorkSheets we want to load 
     It's more efficient to limit sheet loading in this manner rather than coding it into a Read Filter  **/ 
$objReader->setLoadSheetsOnly($sheetname); 
echo 'Loading Sheet using filter';
/**  Tell the Reader that we want to use the Read Filter that we've Instantiated  **/ 
$objReader->setReadFilter($filterSubset); 
/**  Load only the rows and columns that match our filter from $inputFileName to a PHPExcel Object  **/
$objPHPExcel = $objReader->load($inputFileName);

Using read filters, you can also read a workbook in "chunks", so that only a single chunk is memory-resident at any one time:

$inputFileType = 'Excel5'; 
$inputFileName = './sampleData/example2.xls';

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

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

If you don't need to load formatting information, but only the worksheet data, then the setReadDataOnly() method will tell the reader only to load cell values, ignoring any cell formatting:

$inputFileType = 'Excel5';
$inputFileName = './sampleData/example1.xls';
/** Create a new Reader of the type defined in $inputFileType **/ 
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
/** Advise the Reader that we only want to load cell data, not formatting **/ 
$objReader->setReadDataOnly(true);
/**  Load $inputFileName to a PHPExcel Object  **/
$objPHPExcel = $objReader->load($inputFileName);

Use cell caching. This is a method for reducing the PHP memory that is required for each cell, but at a cost in speed. It works by storing the cell objects in a compressed format, or outside of PHP's memory (eg. disk, APC, memcache)... but the more memory you save, the slower your scripts will execute. You can, however, reduce the memory required by each cell to about 300bytes, so the hypothetical 5M cells would require about 1.4GB of PHP memory.

Cell caching is described in section 4.2.1 of the Developer Documentation

EDIT

Looking at your code, you're using the iterators, which aren't particularly efficient, and building up an array of cell data. You might want to look at the toArray() method, which is already built into PHPExcel, and does this for you. Also take a look at this recent discussion on SO about the new variant method rangeToArray() to build an associative array of row data.

phpexcel Allowed memory size of 134217728 bytes exhausted

There's a lot been written about PHPExcel and memory use, and I'm not going to repeat it all here.

Try reading some of the threads on the PHPExcel discussion board discussing the issue, such as this one; or previous answers here on SO such as this one or this one

PHPExcel gives me Allowed 134MB ... Tried 16MB, Allowed memory size exhausted even on 2MB excel

You might need to profile your script because PHPexcel tends to be a bit liberal with memory use, but in the meantime, if you are stuck for time, you can try a garbage collect with gc_collect_cycles() in your loops, since PHP doesnt garbage collect until a function or script finishes. It will slow it down, but it may finish.

public function runImport($file){   
     $errors = array();
            $objPHPExcel = Config::getExcel($file);
            foreach($objPHPExcel->getSheet(0)->getRowIterator(2, $objPHPExcel->getSheet(0)->getHighestDataRow()) as $row ){
                $i = 0;
                $_data = array();
                foreach($row->getCellIterator() as $cell ){
                    $i++;
                    if ($i > count($columns)) break;
                    array_push($_data,(string)$cell->getValue());
                    gc_collect_cycles();
                }
                foreach ($_data as $item){
                    if ($item != null && $item != "" && strlen($item) > 0){
                        $jdata = new Jobsdata();
                        $jdata->jobId = $this->jobId;
                        $jdata->data = $_data;
                        $jdata->save();
                        break;
                    }
                    gc_collect_cycles();
                }
                gc_collect_cycles();
            }
            $this->save();
            return $errors;
    }

Allowed memory size of 134217728 bytes exhausted in PhpExcel

As another point to note, you're building the PHPExcel object by looping through an array called $data.... a 2d array, that I'm guessing is built from looping through the results of a database query.

That $data array is also going to be using a large part of your memory, more and more memory each day as the number of results grows.

It would be more efficient if, instead of looping through the database resultset to build a large array and then looping through that array to build the PHPExcel data, you looped through the database resultset and built the PHPExcel data directly. That eliminates the memory overhead of $data, and reduces 2 loops to 1.

Reading .xls file via PHPExcel throws Fatal error: allowed memory size... even with chunk reader

So i found interesting solution here How to read large worksheets from large Excel files (27MB+) with PHPExcel?

as Addendum 3 in question

edit1: also with this solution, i came to chokepoint with my favourite errr message, but i found something about caching, so i implemented this

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

recently i tested it only for xls files lesser than 10MB, but it seems like to work (also i set $objReader->setReadDataOnly(true);) and it seems like balanced enough to achieve speed and memory consumption. (i will follow my thorny path more, if its possible)

edit2:
So i made some further research and found chunk reader unnecessary in my way. (seems like to me, memory issue is same with chunk reader and without it.) So my final answer to my question is something like that, which reads .xls file (only data from cells, without formating, even filtering out formulas). When i use cache_tp_php_temp im able to read xls files (tested to 10MB) and about 10k rows and multiple columns in matter of seconds and without memory issue

function parseXLS($fileName){

/** PHPExcel_IOFactory */
    require_once dirname(__FILE__) . './sphider_design/include/Excel/PHPExcel/IOFactory.php';
    require_once dirname(__FILE__) . './sphider_design/include/Excel/PHPExcel/ChunkReadFilter.php';
    require_once dirname(__FILE__) . './sphider_design/include/Excel/PHPExcel.php';

    $inputFileName = $fileName;
    $fileContent = "";

    //get inputFileType (most of time Excel5)
    $inputFileType = PHPExcel_IOFactory::identify($inputFileName);

    //initialize cache, so the phpExcel will not throw memory overflow
    $cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
    $cacheSettings = array(' memoryCacheSize ' => '8MB');
    PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);

    //initialize object reader by file type
    $objReader = PHPExcel_IOFactory::createReader($inputFileType);

    //read only data (without formating) for memory and time performance
    $objReader->setReadDataOnly(true);

    //load file into PHPExcel object
    $objPHPExcel = $objReader->load($inputFileName);

    //get worksheetIterator, so we can loop sheets in workbook
    $worksheetIterator = $objPHPExcel->getWorksheetIterator();

    //loop all sheets
    foreach ($worksheetIterator as $worksheet) {    

            //use worksheet rowIterator, to get content of each row
            foreach ($worksheet->getRowIterator() as $row) {
                //use cell iterator, to get content of each cell in row
                $cellIterator = $row->getCellIterator();
                //dunno
                $cellIterator->setIterateOnlyExistingCells(false);      

                //iterate each cell
                foreach ($cellIterator as $cell) {
                    //check if cell exists
                    if (!is_null($cell)) {
                        //get raw value (without formating, and all unnecessary trash)
                        $rawValue = $cell->getValue();
                        //if cell isnt empty, print its value
                        if ((trim($rawValue) <> "") and (substr(trim($rawValue),0,1) <> "=")){
                            $fileContent .= $rawValue . " ";                                            
                        }
                    }
                }       
            }       
    }

    return $fileContent;
}

Speed of PHP Excel

One of those things where you spend hours finding the problem and then submit the question and 1 minute later find an answer. My Excel file had 2 sheets - the second sheet was enormous... Click here to see Mark mentioning this idea:

PHPExcel runs out of 256, 512 and also 1024MB of RAM

Fatal error: Allowed memory size of xxx bytes exhausted Php excel

If you don't have enough memory to handle a large number of cells, then the recommended method of reducing memory usage is to use cell caching, as described in section 4.2.1. of the developer documentation.

PHPexcel Runs Out of 256, 512 and Also 1024Mb of Ram