CODEWITHSUNDEEP
phpmemoryphpexcel
Marco
Marco

Reputation: 527

phpexcel memory exhausted with 128Mb memory reading only first row of a big file

I've a memory problem with an xlsx file of about 95.500 rows and 28 columns. To handle such big file (more than 10 MB xlsx) i wrote below code but when i execute the code and calling the load method i receive a memory exhausted error even with only one row read! (I've assigned only 128Mb to php interpreter)

Please consider that:

Please, don't simply suggest to increment memory of php process. I alredy know that this is possible but this code run on VPS shared server with only 512Mb of RAM maximum and I need to maintain the memory use lowest as possible

there is solution? please find below code that i use:

/**  Define a Read Filter class implementing PHPExcel_Reader_IReadFilter  to read file in "chunks" */
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;
    }
}

function loadXLSFile($inputFile){
   // Initiate cache
   $cacheMethod = PHPExcel_CachedObjectStorageFactory:: cache_to_sqlite3;    
   if (!PHPExcel_Settings::setCacheStorageMethod($cacheMethod)) {
       echo date('H:i:s'), " Unable to set Cell Caching using ", $cacheMethod,
                                     " method, reverting to memory", EOL;
   }
    $inputFileType = PHPExcel_IOFactory::identify($inputFile);
    $objReader = PHPExcel_IOFactory::createReader($inputFileType);
    $chunkFilter = new chunkReadFilter();
    //  Tell the Read Filter, the limits on which rows we want to read this iteration
    $chunkFilter->setRows(1,1);
    // Tell the Reader that we want to use the Read Filter that we've Instantiated
    $objReader->setReadFilter($chunkFilter);
    $objReader->setReadDataOnly(true);
    $objPHPExcel = $objReader->load($inputFile);
}

UPDATE

Below the error returned as requested by pamelus 

PHP Fatal error:  Allowed memory size of 134217728 bytes exhausted (tried to allocate 112 bytes) in /vendor/phpoffice/phpexcel/Classes/PHPExcel/Reader/Excel2007.php on line 471
PHP Stack trace:
PHP   1. {main}() dataimport.php:0
PHP   2. loadFileToDb($inputFile = *uninitialized*, $tabletoupdate = *uninitialized*) dataimport.php:373
PHP   3. PHPExcel_Reader_Excel2007->load($pFilename = *uninitialized*) dataimport.php:231

Upvotes: 1

Views: 1127

Answers (1)

Adrien
Adrien

Reputation: 1947

Given the low memory limit you have, I can suggest you an alternative to PHPExcel that would solve your problem once and for all: Spout. It only requires 10MB of memory, so you should be good!

Your loadXLSXFile() function would become:

use Box\Spout\Reader\ReaderFactory;
use Box\Spout\Common\Type;

function loadXLSFile($inputFile) {
    $reader = ReaderFactory::create(Type::XLSX);
    $reader->open($inputFile);

    foreach ($reader->getSheetIterator() as $sheet) {
        foreach ($sheet->getRowIterator() as $row) {
            // $row is the first row of the sheet. Do something with it
            break; // you won't read any other rows
        }
        break; // if you only want to read the first sheet
    }

    $reader->close();
}

It's that simple! No need for caching, filters, and other optimizations :)

Upvotes: 2

Related Questions