PHP: Reading huge amount of excel file causing fatal error

Question

i am facing a problem.

I need to read a .xls file of about 10MB. i write a php code that works fine when i read small .xls file. but when i try to read large file then the browser shows "Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 1032 bytes) in C:\wamp\www\student\ExcelRes\PHPExcel\Cell.php on line 1126"

Here is my code.

<?php 

    ini_set('memory_limit', '128M');

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

    include 'PHPExcel/IOFactory.php';
    $inputFileName = 'ru_unit_H_all.xls'; 
    $objPHPExcel = PHPExcel_IOFactory::load($inputFileName);
    $sheetData = $objPHPExcel->getActiveSheet()->toArray(null,true,true,true);


    echo $sheetData['20007'][ 'K'];          //row colomn

?>

As far as I'm concerned, memory_limit cannot be overwritten by ini_set(). — Cobra_Fast
– Cobra_Fast, Commented Dec 3, 2012 at 12:15
memory management come to be a real pain in php when parsing files, if you can, optimize PHPExcel to consume less memory. Try increasing memory_limit up to 256M, if you stil have a memory limit error, then parsing may have gone in an infinite loop or recursion — SmasherHell
– SmasherHell, Commented Dec 3, 2012 at 12:17

Vyktor · Accepted Answer · 2012-12-03 12:22:27Z

2

Error message should be self explaining:

"Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 1032 bytes) in C:\wamp\www\student\ExcelRes\PHPExcel\Cell.php on line 1126"

You just simply ran out of memory reserved for execution of one script.

You may increase your memory_limit using ini_set() to solve this issue.

Note: using 128MB isn't enough, because 134217728B = ~128MB still causes that error. Try using 512MB.

There's no memory effective implementation of Excel reader/writer for PHP that I know of.

edited Dec 3, 2012 at 12:22

answered Dec 3, 2012 at 12:15

Vyktor

21.1k6 gold badges69 silver badges98 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Imdad Sarkar Over a year ago

I have already used ini_set() function. but still causing problem

Vyktor Over a year ago

@ImdadSarkar so use 5GB or different implementation... No much other options there.

Mark Baker Over a year ago

@Vyktor - COM would be memory efficient, because the Excel workbooks are loaded outside of PHP memory... PHPExcel does have some settings for reducing memory requirements, such as cell caching, or loading only parts of the spreadsheet, or loading only data only without the style information; but is still constrained by PHP memory limits

Umar Adil Over a year ago

I am writing excel files with a template load and getting this error --- "Fatal error: Allowed memory size of 268435456 bytes exhausted (tried to allocate 704384 bytes) in /home/envdev/public_html/app/application/third_party/PHPExcel/PHPExcel/Writer/Excel2007.php on line 304 "

Kami · Accepted Answer · 2012-12-03 12:17:32Z

0

The memory available to the script has been exausted. By default I believe each script has a limit of 8MB of memory allocated to it. You are attempting to read 10MB file, and as such, there is not enough memory to process the requst and it fails.

You can try increasing the amount of memory available, by using memory_limit setting.

This can be done globally for all scripts in the php.ini settings file or on a per script basis using

ini_set('memory_limit','16M');

Where 16M is 16 Megabytes of memory.

answered Dec 3, 2012 at 12:17

Kami

19.5k4 gold badges53 silver badges65 bronze badges

Comments

Mark Baker · Accepted Answer · 2012-12-03 12:33:37Z

0

In addition to possibly increasing memory, you should also be looking at the cell caching options provided by PHPExcel for precisely this purpose, as described in the section of the developer documentation entitled "cell caching" (section 4.2.1)

EDIT

And your use of toArray() is going to build the array you're requesting in PHP memory as well, adding extra overhead - consider iterating over the worksheet a row at a time rather than loading it twice into memory (once as a PHPExcel object and once as your array)

edited Dec 3, 2012 at 12:33

answered Dec 3, 2012 at 12:28

Mark Baker

213k34 gold badges354 silver badges390 bronze badges

Comments

Imdad Sarkar · Accepted Answer · 2012-12-03 15:46:38Z

Finally i solve my problem by using example12 code.

<?php

set_include_path(get_include_path() . PATH_SEPARATOR . 'ExcelRes/');
include 'PHPExcel/IOFactory.php';

$inputFileType = 'Excel5'; 
$inputFileName = 'ru_unit_H_all.xls'; 

class chunkReadFilter implements PHPExcel_Reader_IReadFilter
{
    private $_startRow = 0;
    private $_endRow = 0;

    public function setRows($startRow) 
    {
        $this->_startRow    = $startRow; 
    }

    public function readCell($column, $row, $worksheetName = '')
    { 
        if (($row == 1) || ($row >= $this->_startRow )) 
        {
            return true;
        }
        return false;
    }
}

$objReader = PHPExcel_IOFactory::createReader($inputFileType);

$chunkFilter = new chunkReadFilter();

$objReader->setReadFilter($chunkFilter);

$chunkFilter->setRows(22000); 
$objPHPExcel = $objReader->load($inputFileName);

echo $objPHPExcel->getActiveSheet()->getCell('K22000')->getValue();

}

?>

Collectives™ on Stack Overflow

PHP: Reading huge amount of excel file causing fatal error

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related