Laravel4: Exporting Large Volumes of data to Excel

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');
}