ป้ายกำกับ: กลับ

การจัดแต่งเล็กน้อย จะทำให้รายงานออกมาดูน่าสนใจ จะยกตัวอย่างการใช้ style, การ merge cells, pinned (freeze) cell ไว้ไม่ให้ขยับ เช่น fixed หัวตารางไว้ให้อยู่กับที่ และการ flip กลับตัวอักษร

<?php

/* PHPExcel_IOFactory - Reader */
include 'PHPOffice/PHPExcel/Classes/PHPExcel.php';

$objPHPExcel = new PHPExcel () ;

/* Set default style */
$defaultStyle = $objPHPExcel->getDefaultStyle () ;

$defaultStyle->getFont () ->setName ('Arial') ->setSize (11) ;

/* Set document properties */
$title = 'Exports_Datas_' . date ('Y-m-d_H:i') ;
$objPHPExcel->getProperties () ->setCreator ('Pitt Phunsanit') ->setCategory ('Exports Datas') ->setDescription ($title) ->setKeywords ('Exports Datas ' . date ('Y-m-d')) ->setSubject ($title) ->setTitle ($title) ;

/* rename sheet */
$objWorkSheet = $objPHPExcel->getActiveSheet () ;
$objWorkSheet->setTitle ('Exports Datas') ;

$styles = [

 'tableHeader1' => [
 'alignment' => [
 'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
 ],
 'fill' => [
 'color' => [
 'rgb' => 'C00000'
 ],
 'type' => PHPExcel_Style_Fill::FILL_SOLID,
 ],
 'font' => [
 'color' => [
 'rgb' => 'ffffff'
 ],
 'name' => 'Arial',
 ],
 'name' => 'Arial',
 ],

 'tableHeader2' => [
 'alignment' => [
 'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
 ],
 'fill' => [
 'color' => [
 'rgb' => '00c000'
 ],
 'type' => PHPExcel_Style_Fill::FILL_SOLID,
 ],
 'font' => [
 'color' => [
 'rgb' => 'ffffff'
 ],
 'name' => 'Arial',
 ],
 'name' => 'Arial',
 ],

 'tableRow' => [
 'alignment' => [
 'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT,
 ],
 'bold' => true,
 'fill' => [
 'color' => [
 'rgb' => '0000c0'
 ],
 'type' => PHPExcel_Style_Fill::FILL_SOLID,
 ],
 'font' => [
 'color' => [
 'rgb' => 'ffffff'
 ],
 'name' => 'Arial',
 ],
 'name' => 'Arial',
 ],

];

/* raw datas */
$headers1 = ['2557', '2558', '2559'];
$headers2 = ['Shop A', 'Shop B'];
$headersRows = ['มกราคม', 'กุมภาพันธ์', 'มีนาคม', 'เมษายน', 'พฤษภาคม', 'มิถุนายน', 'กรกฎาคม', 'สิงหาคม', 'กันยายน', 'ตุลาคม', 'พฤศจิกายน', 'ธันวาคม'];

/* make table */
$header1ColNo = 1;
$header2ColNo = 1;
$header2PerHeaders1 = count ($headers2) ;
foreach ($headers1 as $header1) {
 $header1CoordinateStart = PHPExcel_Cell::stringFromColumnIndex ($header1ColNo) . '1';
 $header1CoordinateEnd = PHPExcel_Cell::stringFromColumnIndex ($header1ColNo + $header2PerHeaders1 - 1) . '1';

 $objWorkSheet->getStyle ($header1CoordinateStart) ->applyFromArray ($styles['tableHeader1']) ;
 $objWorkSheet->mergeCells ($header1CoordinateStart . ':' . $header1CoordinateEnd) ;
 $objWorkSheet->setCellValue ($header1CoordinateStart, $header1) ;

 /* set column */
 $header1ColNo += $header2PerHeaders1;

 foreach ($headers2 as $header2) {
 $coordinate = PHPExcel_Cell::stringFromColumnIndex ($header2ColNo) . '2';

 $objWorkSheet->getStyle ($coordinate) ->applyFromArray ($styles['tableHeader2']) ;
 $objWorkSheet->setCellValue ($coordinate, $header2) ;

 $header2ColNo++;
 }
}

$rowNo = 3;
foreach ($headersRows as $header) {
 $coordinate = 'A' . $rowNo;

 $objWorkSheet->getStyle ($coordinate) ->applyFromArray ($styles['tableRow']) ;
 $objWorkSheet->setCellValue ($coordinate, $header) ;

 $rowNo++;
}

/* random add datas */
$highestColumn = 1 + (count ($headers1) * count ($headers2)) ;
$highestRow = 2 + count ($headersRows) ;
for ($rowNo = 3; $rowNo <= $highestRow; $rowNo++) {
 for ($colNo = 1; $colNo < $highestColumn; $colNo++) {

 $colString = PHPExcel_Cell::stringFromColumnIndex ($colNo) ;

 $coordinate = $colString . $rowNo;

 $objWorkSheet->setCellValue ($coordinate, rand (0 , 100)) ;
 }
}

/* right label */
$colString = PHPExcel_Cell::stringFromColumnIndex ($colNo) ;
$rowNo--;

$objWorkSheet->getStyle ($colString . '3') ->getAlignment () ->setTextRotation (-90) ->setVERTICAL (PHPExcel_Style_Alignment::VERTICAL_TOP) ;

$objWorkSheet->mergeCells ($colString . '3:' . $colString . $rowNo) ;
$objWorkSheet->setCellValue ($colString . '3', 'Gross Income Per Month') ;

/* freeze pinned head column */
$objPHPExcel->getActiveSheet () ->freezePane ($colString.'3') ;

$objWriter =PHPExcel_IOFactory::createWriter ($objPHPExcel, 'Excel2007') ;
header ('Content-Type: application/vnd.ms-excel') ;
header ('Content-Disposition: attachment;filename="' . $title . '.xlsx"') ;
header ('Cache-Control: max-age=0') ;
header ('Cache-Control: no-store, no-cache, must-revalidate, max-age=0') ;
header ('Cache-Control: post-check=0, pre-check=0', false) ;
header ('Pragma: no-cache') ;
$objWriter->save ('php://output') ;