Fatal error: Allowed memory size of xxx bytes exhausted Php excel

Question

I have made a script and i am getting the following error. If have upgrade the memory size to 7g still the error. So i tried to append 10000 records and save it and go for the next 10000 to solve it, works, but still getting the error.

Is there any way to flush the workbook or other methods?

Thnx!

The awnser:

PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);   
$cacheMethod = PHPExcel_CachedObjectStorageFactory:: cache_to_discISAM;
$cacheSettings = array( 
    'dir' => '/home/domains/xxxx.nl/public_html/xxxxx'
);



require_once 'PHPExcel.php';
require_once 'PHPExcel/Writer/Excel2007.php';
require_once 'PHPExcel/IOFactory.php';



$counter = $result->num_rows;//159920
$spreadsheet = new PHPExcel();
$spreadsheet->setActiveSheetIndex(0);
$worksheet = $spreadsheet->getActiveSheet();


//header
$worksheet->setCellValueExplicit('A' . 1, 'Filename', PHPExcel_Cell_DataType::TYPE_STRING);
etc
etc

$rownr = 2;
$counterN = 0;
while ($row = mysqli_fetch_assoc($result)) {  
    $worksheet->setCellValueExplicit('A' . $rownr, utf8_encode($row['filename']), PHPExcel_Cell_DataType::TYPE_STRING);
    etc
    etc

    if( $counterN == 10000 ){

        $objWriter = new PHPExcel_Writer_Excel2007($spreadsheet);
        $objWriter->save('test.xlsx');

        $objPHPExcel = PHPExcel_IOFactory::load("test.xlsx");
        $objPHPExcel->setActiveSheetIndex(0);
        $row = $objPHPExcel->getActiveSheet()->getHighestRow()+1;
        $counterN = 0;
    }
    $counterN++;

}

$objWriter = new PHPExcel_Writer_Excel2007($spreadsheet);
$objWriter->save('test.xlsx');

I get the same error trying to output an excel with 4k rows, I think PHPExcel has a leak memory with things with more than 2 o 3k records — Emilio Gort, Feb 09 '14 at 05:33
http://stackoverflow.com/questions/4817651/phpexcel-runs-out-of-256-512-and-also-1024mb-of-ram — Emilio Gort, Feb 09 '14 at 05:40
Thanks, i tried it ($objReader->setReadDataOnly(true);) but that doesnt work... — Bas, Feb 09 '14 at 06:09
I dont know how to fix it, what I did, was export as csv file since I used plain data, no excel funcionality — Emilio Gort, Feb 09 '14 at 06:11
Are you trying to create a new __Excel file__ for every 1000 records, or a new __worksheet__ — Mark Baker, Feb 09 '14 at 11:24
@emilio-gort - PHPExcel doesn't have a __memory leak__, but it is an __in memory__ representation of a spreadsheet, so it does use a lot of memory to hold all that spreadsheet data.... cell caching was introduced as a way of reducing the memory footprint — Mark Baker, Feb 09 '14 at 12:20

score 1 · Accepted Answer · answered Feb 09 '14 at 11:22

1

If you don't have enough memory to handle a large number of cells, then the recommended method of reducing memory usage is to use cell caching, as described in section 4.2.1. of the developer documentation.

answered Feb 09 '14 at 11:22

Mark Baker

209,507
32
346
385

I'll try...thanks for the tip, and the english lesson **memory leak** – Emilio Gort Feb 09 '14 at 18:46

Fatal error: Allowed memory size of xxx bytes exhausted Php excel

1 Answers1