<?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/
