After a lot of head banging, I reached at the optimum solution for exporting large volumes (upto 40k rows) of data to excel. Below is the code that can do wonders for you!
Requirements:
1. Install the Maatwebsite Laravel bundle ( This has the PHPExcel library which we will be using as it is faster than the bundle’s wrapper classes) :
https://github.com/Maatwebsite/Laravel-Excel
2. A basic understanding of the Laravel4 framework
public function getExportToExcel(){
$emp = new Employee(); // Create the model class object
$empList = $emp->getAllEmployees(array()); // get all the data to be //exported
// set php's memory limit from 128(default) to 500MB.
// Without this, it gives an error that file does not exist/ blank page
ini_set('memory_limit', '500M');
//Prevent your script from timing out
// This increases the excution time from 30 secs to 3000 secs.
set_time_limit ( 3000 );
$objPHPExcel = new PHPExcel();
$i = 1;
//looping through the result and writing each row onto the excel sheet
for ($i = 0; $i setActiveSheetIndex(0)
->setCellValue("A$i", $empList[$i][0])
->setCellValue("B$i", $empList[$i][1])
->setCellValue("C$i", $empList[$i][2])
->setCellValue("D$i", $empList[$i][3])
->setCellValue("E$i", $empList[$i][4])
->setCellValue("F$i", $empList[$i][5])
->setCellValue("G$i", $empList[$i][6])
->setCellValue("H$i", $empList[$i][7])
->setCellValue("I$i", $empList[$i][8])
->setCellValue("J$i", $empList[$i][9])
->setCellValue("K$i", $empList[$i][10])
->setCellValue("L$i", $empList[$i][11]);
$i++;
}
$objPHPExcel->getActiveSheet()->setTitle('Name of Sheet 1');
//echo memory_get_usage() . "\n"; -- Echo the memory used at this point
//echo memory_get_peak_usage() . "\n"; -- Echo the maximum memory used
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="result.xls"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output'); // To make the file downloadable via a popup in your browser
}
It took me around:
51secs, 245 MB of memory to export 15k rows and 12 columns.
65secs , 482MB to export 30k rows and 12 columns
Also, using the PHPExcel classes saves around 1-2% of time than using the maatwebsite bundle’s wrapper classes. So, I chose to that. However, if you may wish to use the bundle’s classes, here’s the code for you.
public function getExportToCsv(){
$excel = App::make('excel');
$emp = new Employee();
$empList = $emp->getAllEmployees(array());
set_time_limit ( 500 );
ini_set('memory_limit', '500M');
//using cache saves quite some time.
//Make sure the cache memory is within the php memory_limit
$cacheMethod = PHPExcel_CachedObjectStorageFactory:: cache_to_phpTemp;
$cacheSettings = array( 'memoryCacheSize' => '256M');
PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
Excel::create('Filename', function($excel) use($empList) {
$excel->sheet('Employees-1', function($sheet) use($empList){
$sheet->fromArray($empList);
});
})->export('csv');
}