YouTube Icon

Code Playground.

PHPExcel export mysql to Excel in Laravel 5

CFG

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




CFG