CODEWITHSUNDEEP
phpexcel
Imdad Sarkar
Imdad Sarkar

Reputation: 1235

PHP: Reading huge amount of excel file causing fatal error

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

?>

Upvotes: 2

Views: 3360

Answers (4)

Imdad Sarkar
Imdad Sarkar

Reputation: 1235

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();

}

?>

Upvotes: 0

Mark Baker
Mark Baker

Reputation: 212412

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)

Upvotes: 0

Vyktor
Vyktor

Reputation: 20997

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.

Upvotes: 2

Kami
Kami

Reputation: 19407

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.

Upvotes: 0

Related Questions