How to Fix Memory Getting Exhausted with PHPexcel

How to fix memory getting exhausted with PHPExcel?

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 Allowed memory size of exhausted

If you cant increase memory limit try another libs

PHP-Export-Data by Eli Dickinson

simple excel

PHP_XLSXWriter

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

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 :)

PHPExcel 1.8.0 memory exhausted on load, chunk read, and iterator

You do have a major problem here:

$sheetData = $objPHPExcel->getActiveSheet()->toArray(null,true,true,true);

which will try to build an array for the entire size of the worksheet regardless of whether you chunk loaded or not.... toArray() uses the expected size of the spreadsheet based on the file that you're loading, not the filtered set of cells that you're loading

Try getting only the range of cells that you've loaded through the chunk using rangeToArray() instead

$sheetData = $objPHPExcel->getActiveSheet()
    ->rangeToArray(
        'A'.$startRow.':'.$objPHPExcel->getActiveSheet()->getHighestColumn().($startRow+$chunkSize-1),
        null,
        true,
        true,
        true
    );

Even then, building PHP arrays in memory uses a lot of memory; your code will be a lot less memory-hungry if it can process that worksheet data one row at a time rather than populating a large array

---

Related Topics