PHPExcel export mysql to Excel in Laravel 5
My clients have to download data from their database tables for most of the time. Today, I chose to use PHPExcel in laravel 5 to build a very small controller that is compact and powerful to export complete MySQL tables to Excel.
Next, you need to install PHPExcel as a laravel 5 library. You should follow the steps posted here to do this.
Now you can use your controllers or middleware or library's PHPEXCEL class.
use PHPExcel;
use PHPExcel_IOFactory;
Mysql to excel export.
$tamplate = "stock_download.xlt";
$objReader = PHPExcel_IOFactory::createReader('Excel5');
$objPHPExcel = $objReader->load("assets/file_template/".$tamplate);
$exportdimond = DiamondMaster::where('is_delete','0')
->where('Location','16')
->get();
$row = 2;
foreach ($exportdimond as $value)
{
$objPHPExcel->getActiveSheet()->setCellValue('A' . $row, $value->id);
$objPHPExcel->getActiveSheet()->setCellValue('B' . $row, $value->C_Shape);
$objPHPExcel->getActiveSheet()->setCellValue('C' . $row, $value->C_Weight);
$objPHPExcel->getActiveSheet()->setCellValue('D' . $row, $value->C_Color);
$objPHPExcel->getActiveSheet()->setCellValue('E' . $row, $value->C_Clarity);
$objPHPExcel->getActiveSheet()->setCellValue('F' . $row, $value->C_Cut);
$objPHPExcel->getActiveSheet()->setCellValue('G' . $row, $value->C_Polish);
$objPHPExcel->getActiveSheet()->setCellValue('H' . $row, $value->C_Symmetry);
$objPHPExcel->getActiveSheet()->setCellValue('I' . $row, $value->C_Fluorescence);
$objPHPExcel->getActiveSheet()->setCellValue('J' . $row, $value->C_Length);
$objPHPExcel->getActiveSheet()->setCellValue('K' . $row, trim($value->C_Rap));
$objPHPExcel->getActiveSheet()->setCellValue('M' . $row, trim($carat_price));
$objPHPExcel->getActiveSheet()->setCellValue('N' . $row, trim($net_price));
$objPHPExcel->getActiveSheet()->setCellValue('P' . $row, floor($value->Certi_NO));
$objPHPExcel->getActiveSheet()->setCellValue('Q' . $row, $value->C_DefthP);
$objPHPExcel->getActiveSheet()->setCellValue('R' . $row, $value->C_TableP);
$objPHPExcel->getActiveSheet()->setCellValue('S' . $row, $value->Crn_Ag);
$objPHPExcel->getActiveSheet()->setCellValue('T' . $row, $value->Crn_Ht);
$objPHPExcel->getActiveSheet()->setCellValue('U' . $row, $value->Pav_Ag);
$objPHPExcel->getActiveSheet()->setCellValue('V' . $row, $value->Pav_Dp);
$objPHPExcel->getActiveSheet()->setCellValue('W' . $row, $value->HNA);
$objPHPExcel->getActiveSheet()->setCellValue('X' . $row, @$value->BGM);
$objPHPExcel->getActiveSheet()->setCellValue('Y' . $row, $value->key_symbols);
$objPHPExcel->getActiveSheet()->setCellValue('AB' . $row, $value->EyeC);
$row++;
}
$filename = "MYDetail". date("Y-m-d-H-i-s").".csv";
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="'.$filename.'"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'CSV');
$objWriter->save('php://output');