[ACCEPTED]-PHPExcel runs out of 256, 512 and also 1024MB of RAM-phpexcel

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

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

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

If 37 you have multiple worksheets, but don't 36 need to load all of them, then you can limit 35 the worksheets that the Reader will load 34 using the setLoadSheetsOnly() method. To 33 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 32 specify several worksheets with one call 31 to setLoadSheetsOnly() by passing an array 30 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 29 of a worksheet, then you can define a Read 28 Filter to identify just which cells you 27 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 26 can also read a workbook in "chunks", so 25 that only a single chunk is memory-resident 24 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 23 formatting information, but only the worksheet 22 data, then the setReadDataOnly() method 21 will tell the reader only to load cell values, ignoring 20 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 19 is a method for reducing the PHP memory 18 that is required for each cell, but at a 17 cost in speed. It works by storing the cell 16 objects in a compressed format, or outside 15 of PHP's memory (eg. disk, APC, memcache)... but 14 the more memory you save, the slower your 13 scripts will execute. You can, however, reduce 12 the memory required by each cell to about 11 300bytes, so the hypothetical 5M cells would 10 require about 1.4GB of PHP memory.

Cell caching 9 is described in section 4.2.1 of the Developer 8 Documentation

EDIT

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

I had the same memory issue problem with 10 PHPExcel and actually all the other libraries. Reading 9 the data in chunks, as Mark Baker suggested 8 could fix the issue (caching works too), but 7 it turned out that the memory issue became 6 a time issue. The reading and writing time 5 was exponential so for large spreadsheets, it 4 was not a good fit.

PHPExcel and others are 3 not meant to handle large files so I created 2 a library that solves this problem. You 1 can check it out here: https://github.com/box/spout

Hope that helps!

There are plenty of measures you can take 8 to reserve less memory when working with 7 PHPExcel. I recommend you to take the following 6 actions to optimize memory usage before 5 modifying your server's memory limit in 4 Apache.

/* Use the setReadDataOnly(true);*/
    $objReader->setReadDataOnly(true);

/*Load only Specific Sheets*/
    $objReader->setLoadSheetsOnly( array("1", "6", "6-1", "6-2", "6-3", "6-4", "6-5", "6-6", "6-7", "6-8") );

/*Free memory when you are done with a file*/
$objPHPExcel->disconnectWorksheets();
   unset($objPHPExcel);

Avoid using very large Exel files, remember 3 it is the file size that makes the process 2 run slowly and crash.

Avoid using the getCalculatedValue(); function 1 when reading cells.

Ypu can try PHP Excel http://ilia.ws/archives/237-PHP-Excel-Extension-0.9.1.html Its an C extension 2 for php and its very fast. (Also uses less 1 memory than PHP implementations)

In my case, phpexcel always iterated through 6 19999 rows. no matter, how many rows actually 5 were filled. So 100 rows of data always 4 ended up in a memory error.

Perhaps you just 3 have to check, if the cells in the current 2 row are empty and then "continue" oder break 1 the loop, that iterates the rows.

Just reposting my post from another thread. It 27 describes different approach to serverside 26 generating or editing of Excel spreadsheets 25 that should be taken in account. For large 24 amount of data I would not recommend tools 23 like PHPExcel or ApachePOI (for Java) because 22 of their memory requirements. There is another 21 quite convenient (although maybe little 20 bit fiddly) way to inject data into spreadsheets. Serverside 19 generation or updating of Excel spreadsheets 18 can be achieved thus simple XML editing. You 17 can have XLSX spreadsheet sitting on the 16 server and every time data is gathered from 15 dB, you unzip it using php. Then you access 14 specific XML files that are holding contents 13 of worksheets that need to be injected and 12 insert data manually. Afterwards, you compress 11 spreadsheet folder in order to distribute 10 it as an regular XLSX file. Whole process 9 is quite fast and reliable. Obviously, there 8 are few issues and glitches related to inner 7 organisation of XLSX/Open XML file (e. g. Excel 6 tend to store all strings in separate table 5 and use references to this table in worksheet 4 files). But when injecting only data like 3 numbers and strings, it is not that hard. If 2 anyone is interested, I can provide some 1 code.

I ran into this problem and unfortunately 11 none of the suggested solutions could help 10 me. I need the functionality that PHPExcel 9 provides (formulas, conditional styling, etc) so 8 using a different library was not an option.

What 7 I eventually did was writing each worksheet 6 to an individual (temporary) file, and then 5 combining these separate files with some 4 special software I wrote. This reduced my 3 memory consumption from >512 Mb to well 2 under 100 Mb. See https://github.com/infostreams/excel-merge if you have the same 1 problem.

PHPExcel is archived, and other options 9 as of today would be:

• box/spout (or FastExcel wrapper for Laravel)
• PhpSpreadsheet

For working with very 8 large datasets (5-20k rows etc), you would 7 need chunking support which is absent in 6 spout. You will have to implement some solution 5 yourself (like write a custom limit into 4 a file, open that in the next iteration, create 3 a new file with new data appended ...)

Alternatively, PhpSpreadsheet 2 has chunking support by default. However, it 1 costs more resources than spout.