How to fix memory getting exhausted with PHPExcel?

Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 1078799 bytes) in D:\xampplite\htdocs\Scraper\PHPExcel\Reader\Excel2007.php on line 269

My 128M PHP memory limit quickly gets exhausted even when I am only trying to open a small excel file of ~350 KB with PHPExcel.

Although, I can increase the memory limit in the configuration but it'll be great to see if there are any alternatives to fix th开发者_JAVA百科is.

---

File size isn't a good measure for workbook files when working with PHPExcel. The number of rows and columns (ie cells) is more important.

The PHPExcel code itself has a footprint of between 10 and 25MB, depending on which components are being accessed.

At present, each cell in a workbook takes on average 1k of memory (without any caching) or 1.6k on 64-bit PHP - I'll assume 32-bit PHP for the moment - so (for example) a worksheet of 8000 lines with 31 columns (248,000 cells) will be about 242MB. With cell cacheing (such as php://temp or DiskISAM), that can be reduced to about a third, so the 8000 lines by 31 columns will require about 80MB.

There are a number of options available to help you reduce the memory usage:

Are you using cell caching with PHPExcel?

require_once './Classes/PHPExcel.php';

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

$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objPHPExcel = $objReader->load("test.xlsx");

If you only need to access data in your worksheets, and don't need access to the cell formatting, then you can disable reading the formatting information from the workbook:

$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load("test.xlsx");

If you only need to access some, but not all of the worksheets in the workbook, you can load only those worksheets:

$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objReader->setLoadSheetsOnly( array("Worksheet1", "Worksheet2") );
$objPHPExcel = $objReader->load("test.xlsx");

if you only want to read certain cells within worksheets, you can add a filter:

class MyReadFilter implements PHPExcel_Reader_IReadFilter
{
    public function readCell($column, $row, $worksheetName = '') {
        // Read title row and rows 20 - 30
        if ($row == 1 || ($row >= 20 && $row <= 30)) {
            return true;
        }

        return false;
    }
}

$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objReader->setReadFilter( new MyReadFilter() );
$objPHPExcel = $objReader->load("test.xlsx");

All of these techniques can significantly reduce the memory requirements.

---

PHPExcel is known for memory leaks. I advise you to use the following which need a FRACTION of the memory that PHPExcel uses.:

1) For Reading: PHP-Excel-Reader

2) For Writing: Pear Spreadsheet Excel Writer

---

Just because the data file is only X bytes, doesn't mean it uses X bytes of ram. For example only 4K of data in a $_SESSION array uses 64K of ram when loaded up. It just depends what the code is doing with that data. The correct answer is to increase the amount of ram.

Also if this is a XLSX file, they are ZIP'd XML documents. Text files zip up far tighter than 1/2, so your 350K XLSX file is easily a 1MB Excel file.

---

Xdebug is profiler/debugger for php and can help you trace through memory usage and functional calls to figure out where the problem lies. And its easy to install, most linux distributions have it in repository, "yum install xdebug", "apt-get install xdebug".

---

One thing you should note too is empty cells.

I had the same issue, only 800 rows of data and the script was not even able to read. Both timeouts and out of memory errors when either was fixed.

Something @Mark Baker said about cells got me thinking. I noticed that I had loads of empty cells and copying only the cells with data to a new workbook got it to run in a second.

Hope this helps someone.

---

If you want to find out, you can use xhprof. According to this link, you can trace memory usage with it...

---

$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load("test.xlsx");

This much code is enough

---

This post talks about how to close a PHPExcel reader: How to close excel file in php-excel-reader If you're opening multiple Excel files simultaneously, but actually not need them all at the same time, then you could try to open and close(i.e. unset) the readers for each file one by one. Btw, using the same variable name for readers would suffice for the "unsetting" operation.