Tag Archive เอ็กเซล

การจัดแต่งเล็กน้อย จะทำให้รายงานออกมาดูน่าสนใจ จะยกตัวอย่างการใช้ 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');