i export file xlsx using phpexcel, data have 32 cols and many rows. Each day data increase so data will very big. This is my code:
$filename="data.xlsx";
$cacheMethod = PHPExcel_CachedObjectStorageFactory:: cache_to_phpTemp;
$cacheSettings = array( 'memoryCacheSize' => '128MB');
PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
ini_set('max_execution_time', 123456);
$objPHPExcel = new PHPExcel();
$objPHPExcel->setActiveSheetIndex(0);
$i = 2;
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Header1');
$objPHPExcel->getActiveSheet()->setCellValue('B1', 'Header2');
$objPHPExcel->getActiveSheet()->setCellValue('C1', 'Header3');
$objPHPExcel->getActiveSheet()->setCellValue('D1', 'Header4');
$objPHPExcel->getActiveSheet()->setCellValue('E1', 'Header5');
$objPHPExcel->getActiveSheet()->setCellValue('F1', 'Header6');
$objPHPExcel->getActiveSheet()->setCellValue('G1', 'Header7');
$objPHPExcel->getActiveSheet()->setCellValue('H1', 'Header8');
$objPHPExcel->getActiveSheet()->setCellValue('I1', 'Header9');
$objPHPExcel->getActiveSheet()->setCellValue('J1', 'Header10');
$objPHPExcel->getActiveSheet()->setCellValue('K1', 'Header11');
$objPHPExcel->getActiveSheet()->setCellValue('L1', 'Header12');
$objPHPExcel->getActiveSheet()->setCellValue('M1', 'Header13');
$objPHPExcel->getActiveSheet()->setCellValue('N1', 'Header14');
$objPHPExcel->getActiveSheet()->setCellValue('O1', 'Header15');
$objPHPExcel->getActiveSheet()->setCellValue('P1', 'Header16');
$objPHPExcel->getActiveSheet()->setCellValue('Q1', 'Header17');
$objPHPExcel->getActiveSheet()->setCellValue('R1', 'Header18');
$objPHPExcel->getActiveSheet()->setCellValue('S1', 'Header19');
$objPHPExcel->getActiveSheet()->setCellValue('T1', 'Header20');
$objPHPExcel->getActiveSheet()->setCellValue('U1', 'Header21');
$objPHPExcel->getActiveSheet()->setCellValue('V1', 'Header22');
$objPHPExcel->getActiveSheet()->setCellValue('W1', 'Header23');
$objPHPExcel->getActiveSheet()->setCellValue('X1', 'Header24');
$objPHPExcel->getActiveSheet()->setCellValue('Y1', 'Header25');
$objPHPExcel->getActiveSheet()->setCellValue('Z1', 'Header26');
$objPHPExcel->getActiveSheet()->setCellValue('AA1', 'Header27');
$objPHPExcel->getActiveSheet()->setCellValue('AB1', 'Header28');
$objPHPExcel->getActiveSheet()->setCellValue('AC1', 'Header29');
$objPHPExcel->getActiveSheet()->setCellValue('AD1', 'Header30');
$objPHPExcel->getActiveSheet()->setCellValue('AE1', 'Header31');
$objPHPExcel->getActiveSheet()->setCellValue('AF1', 'Header32');
foreach ($data as $value) {
$objPHPExcel->getActiveSheet()->setCellValue('A'.$i, $value['1']);
$objPHPExcel->getActiveSheet()->setCellValue('B'.$i, $value['2']);
$objPHPExcel->getActiveSheet()->setCellValue('C'.$i, $value['3']);
$objPHPExcel->getActiveSheet()->setCellValue('D'.$i, $value['4']);
$objPHPExcel->getActiveSheet()->setCellValue('E'.$i, $value['5']);
$objPHPExcel->getActiveSheet()->setCellValue('F'.$i, $value['6']);
$objPHPExcel->getActiveSheet()->setCellValue('G'.$i, $value['7']);
$objPHPExcel->getActiveSheet()->setCellValue('H'.$i, $value['8']);
$objPHPExcel->getActiveSheet()->setCellValue('I'.$i, $value['9']);
$objPHPExcel->getActiveSheet()->setCellValue('J'.$i, $value['10']);
$objPHPExcel->getActiveSheet()->setCellValue('K'.$i, $value['11']);
$objPHPExcel->getActiveSheet()->setCellValue('L'.$i, $value['12']);
$objPHPExcel->getActiveSheet()->setCellValue('M'.$i, $value['13']);
$objPHPExcel->getActiveSheet()->setCellValue('N'.$i, $value['14']);
$objPHPExcel->getActiveSheet()->setCellValue('O'.$i, $value['15']);
$objPHPExcel->getActiveSheet()->setCellValue('P'.$i, $value['16']);
$objPHPExcel->getActiveSheet()->setCellValue('Q'.$i, $value['17');
$objPHPExcel->getActiveSheet()->setCellValue('R'.$i, $value['18']);
$objPHPExcel->getActiveSheet()->setCellValue('S'.$i, $value['19']);
$objPHPExcel->getActiveSheet()->setCellValue('T'.$i, $value['20']);
$objPHPExcel->getActiveSheet()->setCellValue('U'.$i, $value['21']);
$objPHPExcel->getActiveSheet()->setCellValue('V'.$i, $value['22']);
$objPHPExcel->getActiveSheet()->setCellValue('W'.$i, $value['23']);
$objPHPExcel->getActiveSheet()->setCellValue('X'.$i, $value['24']);
$objPHPExcel->getActiveSheet()->setCellValue('Y'.$i, $value['25']);
$objPHPExcel->getActiveSheet()->setCellValue('Z'.$i, $value['26']);
$objPHPExcel->getActiveSheet()->setCellValue('AA'.$i, $value['27']);
$objPHPExcel->getActiveSheet()->setCellValue('AB'.$i, $value['28']);
$objPHPExcel->getActiveSheet()->setCellValue('AC'.$i, $value['29']);
$objPHPExcel->getActiveSheet()->setCellValue('AD'.$i, $value['30']);
$objPHPExcel->getActiveSheet()->setCellValue('AE'.$i, $value['31']);
$objPHPExcel->getActiveSheet()->setCellValue('AF'.$i, $value['32']);
$i++;
}
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
ob_end_clean();
header("Content-type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
header("Content-Disposition: attachment; filename=".$filename);
header('Cache-Control: max-age=0');
$objWriter->setUseDiskCaching(true);
$objWriter->save("php://output");
two days ago, it's run good. But today, its throw a exception: Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 85 bytes) in /..../Classes/PHPExcel/CachedObjectStorage/CacheBase.php on line 155
As another point to note, you're building the PHPExcel object by looping through an array called $data.... a 2d array, that I'm guessing is built from looping through the results of a database query.
That $data array is also going to be using a large part of your memory, more and more memory each day as the number of results grows.
It would be more efficient if, instead of looping through the database resultset to build a large array and then looping through that array to build the PHPExcel data, you looped through the database resultset and built the PHPExcel data directly. That eliminates the memory overhead of $data, and reduces 2 loops to 1.