จัด PHPExcel สวยๆ

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

[code language=”php” title=”PHPExcel_writer_styles.php”]
<?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’);
[/code]