<?php
set_time_limit(0);
header('Content-Type: text/html; charset=utf-8');
/** PHPExcel */
require_once ("PHPExcel/Classes/PHPExcel.php");
// สร้าง object ของ Class PHPExcel ขึ้นมาใหม่
$objPHPExcel = new PHPExcel();
// กำหนดค่าต่างๆ
$objPHPExcel->getProperties()->setCreator("Company Co., Ltd.");
$objPHPExcel->getProperties()->setLastModifiedBy("Company Co., Ltd.");
$objPHPExcel->getProperties()->setTitle("Office 2007 XLSX Data Document");
$objPHPExcel->getProperties()->setSubject("Office 2007 XLSX Data Document");
$objPHPExcel->getProperties()->setDescription("Data from Company Co., Ltd.");
$sheet = $objPHPExcel->getActiveSheet();
$pageMargins = $sheet->getPageMargins();
// margin is set in inches (0.5cm)
$margin = 0.5 / 2.54;
//Set Margin
$pageMargins->setTop($margin);
$pageMargins->setBottom($margin);
$pageMargins->setLeft($margin);
$pageMargins->setRight(0);
//กำหนดความกว้างในแต่ละ Colum
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(80);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('0')->setWidth(10);
//
//สร้าง Style เพื่อกำหนดฟอร์แมตของหัว Colum
$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'
));
//กำหนด Style และข้อมูล ให้กับ Row ที่ 1 หรือหัวของ Colum
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A1', 'รหัส')
->setCellValue('B1', 'โปรแกรมความเสี่ยง')
->setCellValue('C1', 'ต.ค.')
->setCellValue('D1', 'พ.ย.')
->setCellValue('E1', 'ธ.ค.')
->setCellValue('F1', 'ม.ค.')
->setCellValue('G1', 'ก.พ.')
->setCellValue('H1', 'มี.ค.')
->setCellValue('I1', 'เม.ย.')
->setCellValue('J1', 'พ.ค.')
->setCellValue('K1', 'มิ.ย.')
->setCellValue('L1', 'ก.ค.')
->setCellValue('M1', 'ส.ค.')
->setCellValue('N1', 'ก.ย.')
->setCellValue('O1', 'รวม');
$objPHPExcel->getActiveSheet()->getStyle('A1:O1')->applyFromArray($styleHeader);
//
//กำหนดแถวเริ่มต้นในการเริ่มใส่ข้อมูล
$rowCell=2;
//ทำการดึงข้อมูลจาก Database
//Connect DB
$mysql = new mysql('localhost','root','11428','dbrm');
if ($mysql->connect_errno) {
die( "Failed to connect to MySQL : (" . $mysql->connect_errno . ") " . $mysql->connect_error);
}
$mysql->set_charset("utf8");
$query = " select a.rm_group_id,a.rm_group_name as program,
(select count(*) from rm_report where rm_group=a.rm_group_id and MONTH(date)=10 and YEAR(date)=$ya) AS 'oct',
(select count(*) from rm_report where rm_group=a.rm_group_id and MONTH(date)=11 and YEAR(date)=$ya) AS 'nov',
(select count(*) from rm_report where rm_group=a.rm_group_id and MONTH(date)=12 and YEAR(date)=$ya) AS 'dec',
(select count(*) from rm_report where rm_group=a.rm_group_id and MONTH(date)=1 and YEAR(date)=$yb) AS 'jan',
(select count(*) from rm_report where rm_group=a.rm_group_id and MONTH(date)=2 and YEAR(date)=$yb) AS 'feb',
(select count(*) from rm_report where rm_group=a.rm_group_id and MONTH(date)=3 and YEAR(date)=$yb) AS 'mar',
(select count(*) from rm_report where rm_group=a.rm_group_id and MONTH(date)=4 and YEAR(date)=$yb) AS 'apr',
(select count(*) from rm_report where rm_group=a.rm_group_id and MONTH(date)=5 and YEAR(date)=$yb) AS 'may',
(select count(*) from rm_report where rm_group=a.rm_group_id and MONTH(date)=6 and YEAR(date)=$yb) AS 'jun',
(select count(*) from rm_report where rm_group=a.rm_group_id and MONTH(date)=7 and YEAR(date)=$yb) AS 'jul',
(select count(*) from rm_report where rm_group=a.rm_group_id and MONTH(date)=8 and YEAR(date)=$yb) AS 'aug',
(select count(*) from rm_report where rm_group=a.rm_group_id and MONTH(date)=9 and YEAR(date)=$yb) AS 'sep',
(select count(*) from rm_report where rm_group=a.rm_group_id and date BETWEEN '$ya-10-01' and '$yb-09-30') AS 'total' from rm_group a ";
$res = $mysql->query($query);
while($row = $res->fetch_array()){
//วนลูปเพื่อใส่ข้อมูล
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A'.$rowCell, $row['a.rm_group_id'])
->setCellValue('B'.$rowCell, $row['a.rm_group_name'])
->setCellValue('C'.$rowCell, $row['oct'])
->setCellValue('D'.$rowCell, $row['nov'])
->setCellValue('E'.$rowCell, $row['dec'])
->setCellValue('F'.$rowCell, $row['jan'])
->setCellValue('G'.$rowCell, $row['feb'])
->setCellValue('H'.$rowCell, $row['mar'])
->setCellValue('I'.$rowCell, $row['apr'])
->setCellValue('J'.$rowCell, $row['may'])
->setCellValue('K'.$rowCell, $row['jun'])
->setCellValue('L'.$rowCell, $row['jul'])
->setCellValue('M'.$rowCell, $row['aug'])
->setCellValue('N'.$rowCell, $row['sep'])
->setCellValue('O'.$rowCell, $row['total']);
// $rowCell++;
}
// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle('Sheet1');
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);
// 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="rm_group.xlsx"'); //กำหนดชื่อไฟล์
$objWriter->save('php://output');
exit();
?>
Load balance : Server 02
