致命错误：允许的内存大小为xxx字节耗尽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');

Answer 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.