Bladephp: Now with 7+ codeigniter, php, and laravel project 7+ php project From $18/Year From Rs. 1000/Year

PHPExcel export mysql to Excel in Laravel 5

by
Category:laravel
mysql laravel phpexcel
PHPExcel export mysql to Excel in Laravel 5

Most of the time my clients need to download data from their database tables. Today, I decided to make a very small controller that is portable and efficient for exporting full MySQL tables to Excel using PHPExcel in laravel 5.

First of all, you need PHPExcel which should be installed as a laravel 5 library. In order to do this, you should follow the steps posted here.

Now you can use PHPEXCEL library in your controllers or middleware or library.

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

My another tutorial PHPExcel import Excel to mysql in Laravel 5


0 Comments


Html code work in comment box


Related Post