Export Excel จาก MySQL ด้วย PHP โดยใช้ PHPExcel

<?php
require_once("connect.inc.php");
$query = " SELECT *,
     CASE
       WHEN is_subscription = 1 
       THEN 'Yes'
       ELSE '-'
     END as subscription
     FROM vtiger_leaddetails ORDER BY lead_no ";
$res = $mysqli->query($query); 

include ("PHPExcel/Classes/PHPExcel.php");
// สร้าง object ของ Class  PHPExcel  ขึ้นมาใหม่
$objPHPExcel = new PHPExcel();

// กำหนดค่าต่างๆ
$objPHPExcel->getProperties()->setCreator("AppliCAD Co., Ltd.");
$objPHPExcel->getProperties()->setLastModifiedBy("AppliCAD Co., Ltd.");
$objPHPExcel->getProperties()->setTitle("Office 2007 XLSX Leads Document");
$objPHPExcel->getProperties()->setSubject("Office 2007 XLSX Leads Document");
$objPHPExcel->getProperties()->setDescription("Leads from AppliCAD Co., Ltd.");

$sheet = $objPHPExcel->getActiveSheet();
$pageMargins = $sheet->getPageMargins();

// margin is set in inches (0.5cm)
$margin = 0.5 / 2.54;

$pageMargins->setTop($margin);
$pageMargins->setBottom($margin);
$pageMargins->setLeft($margin);
$pageMargins->setRight(0);

//กำหนดความกว้างของคอลัมน์
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(40);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(35);
$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(10);

//กำหนด Style ของหัวคอลัมน์
$styleHeader = array(
  'fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID,'color' => array('rgb' => 'ffff00')),
  'borders' => array('bottom' => array('style' => PHPExcel_Style_Border::BORDER_THIN)),
  'font'  => array(
  'bold'  => true,
  'size'  => 9,
  'name'  => 'Arial'
 ));

//เขียนหัวคอลัมน์
$objPHPExcel->setActiveSheetIndex(0)
   ->setCellValue('A1', 'leadid')
   ->setCellValue('B1', 'lead_no')
   ->setCellValue('C1', 'firstname')
   ->setCellValue('D1', 'lastname')
   ->setCellValue('E1', 'company')
   ->setCellValue('F1', 'mobile')
   ->setCellValue('G1', 'phone')
   ->setCellValue('H1', 'email')
   ->setCellValue('I1', 'Sub')
   ->setCellValue('J1', 'attended');
   $objPHPExcel->getActiveSheet()->getStyle('A1:J1')->applyFromArray($styleHeader);

//เริ่มเขียนข้อมูลที่แถวที่ 2
$rowCell=2; 
while($row=$res->fetch_array()){ 
 $objPHPExcel->setActiveSheetIndex(0)
    ->setCellValue('A'.$rowCell, $row['leadid'])
    ->setCellValue('B'.$rowCell, $row['lead_no'])
    ->setCellValue('C'.$rowCell,$row['firstname'])
    ->setCellValue('D'.$rowCell, $row['lastname'])
    ->setCellValue('E'.$rowCell, $row['company'])
    ->setCellValueExplicit('F'.$rowCell, $row['mobile'],PHPExcel_Cell_DataType::TYPE_STRING)
    ->setCellValueExplicit('G'.$rowCell, $row['phone'],PHPExcel_Cell_DataType::TYPE_STRING)
    ->setCellValue('H'.$rowCell, $row['email'])
    ->setCellValue('I'.$rowCell, $row['subscription'])
    ->setCellValue('J'.$rowCell, $row['attended']);

 $rowCell++;
}

$mysqli->close(); 

// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle('Leads');

// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);

//ตั้งชื่อไฟล์
$datetime=date("Y-m-d-H:i:s");
$file_name = "Leads_".$datetime;

// Save Excel 2007 file
#echo date('H:i:s') . " Write to Excel2007 format\n";
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
ob_end_clean();
// We'll be outputting an excel file
header('Content-type: application/vnd.ms-excel');
// It will be called file.xls
header('Content-Disposition: attachment;filename="'.$file_name.'.xlsx"');
$objWriter->save('php://output');

exit();
?>

สามารถดาวน์โหลด Class PHPExcel ได้จาก https://phpexcel.codeplex.com/