How to fix a memory error in PHP?

Question

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

This error happens even when I just open a excel file of ~350 KB using PHPExcel. I am puzzled why 128 MB get exhausted when I just try to open a small excel file?

I can increase memory limit in PHP ini file but I still would like to any alternatives available.

Answer 1

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

Answer 2

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.

Answer 3

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".

Answer 4

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

Answer 5

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.