Tag Archive เอกซ์เซล

jQuery.Calx2:binding

เพราะว่าตารางที่ต้องนำมาแสดงค่าการคำนวณมาจากระบบอื่นและใช้ในจุดอื่นๆ ด้วยดังนั้นการเพิ่มแอติบิว data-cell และ data-formula มันไม่ค่อยจะเหมาะนัก ลองใช้ .data แล้วกลับพบว่าสคริปมันไม่ทำงานเหมือน jQuery.Calx2: ใช้สูตร Excel ใน เว็บ ซะงั้น ลองแก้ปัญหาดูจนได้ code

<!doctype html>
<html>

<head>
    <meta charset="utf-8">
    <title>jQuery Calx: dynamic</title>
    <link href="../vendor/twbs/bootstrap/dist/css/bootstrap.min.css" rel="stylesheet" rel="stylesheet" type="text/css">
</head>

<body>
    <table class="table table-bordered table-hover table-striped" id="sheetA" width="100%">
        <thead>
            <tr>
                <td width="58">No.</td>
                <td width="145">First Name</td>
                <td width="126">Last Name</td>
                <td width="135">Relationship to policyholder / main insured</td>
                <td width="191">Employee name<br /> (main insured)</td>
                <td width="100">Period<br /> Start Date<br /> (dd/mm/yy)
                </td>
                <td width="105">Effetive Date<br /> (dd/mm/yy)
                </td>
                <td width="101">Period<br /> End Date<br /> (dd/mm/yy)
                </td>
                <td width="109">DOB</td>
                <td width="51">Age</td>
                <td width="113">ACF - Vital<br /> Annual Premium</td>
                <td width="113">Annually Premium included AGA</td>
                <td width="113">Annual Premium<br /> after 10%<br /> Gr. Discntd</td>
                <td width="107">Prorated Premium<br /> (USD)
                </td>
                <td width="74">Duration Days</td>
            </tr>
        </thead>
        <tbody>
            <tr>
                <td>1</td>
                <td>Tianyi</td>
                <td>Deng</td>
                <td>Employee</td>
                <td>-</td>
                <td>15-May-17</td>
                <td>20-Oct-17</td>
                <td>14-May-18</td>
                <td>24-May-1989</td>
                <td>27</td>
                <td>1,022.00</td>
                <td>ccc</td>
                <td></td>
                <td></td>
                <td></td>
            </tr>
        </tbody>
        <tfoot>
            <tr>
                <td colspan="8"></td>
                <td colspan="5">Total Premium to be Refund(USD)</td>
                <td>(622.19)</td>
                <td></td>
            </tr>
        </tfoot>
    </table>
    <script src="../vendor/components/jquery/jquery.min.js"></script>
    <script src="jquery-calx-2.2.7.min.js"></script>
    <script>
        $(document).ready(function() {

            let sheetA = $('#sheetA');

            let row = $('tr:eq(1)', sheetA);
            $('td:eq(10)', row).attr({
                "data-cell": "K24"
            });
            $('td:eq(11)', row).attr({
                "data-cell": "L24",
                "data-formula": "K24+197"
            });
            $('td:eq(12)', row).attr({
                "data-cell": "M24",
                "data-formula": "L24-(L24*0.1)"
            });
            $('td:eq(13)', row).attr({
                "data-cell": "N24",
                "data-formula": "-(M24*O24)/365"
            });
            $('td:eq(14)', row).attr({
                "data-cell": "O24",
                "data-formula": "H24-G24+1"
            });

            row = $('tr:eq(2)');
            $('td:eq(14)', row).attr({
                "data-cell": "O25",
                "data-formula": "N27"
            });

            sheetA.calx();
        });
    </script>
</body>

</html>

การใช้ .attr() แทน .data() ที่เป็น function โดยตรงกลับทำงานได้ซะงั้น บางครั้ง programmer ก็ต้องอ้อมโลกบ้าง

jQuery.Calx2: ใช้สูตร Excel ใน เว็บ

แรกเริ่มเดิมที่ user จะให้ไฟล์ excel ตัวอย่างการคำนวณมาให้ sa แล้ว sa ก็ส่งสูตรตัวนี้มาให้ทาง programmer เปลี่ยนเป็น javascript พอมีการแก้สูตรตัวนี้กระบวนการก็เริ่มอีกครั้งหนึ่ง ทั้งๆ ที่บางครั้งมีการเปลี่ยนแค่สูตรเดียวเท่านั้น แต่เพราะการเขียนด้วย javascript ที่ซับซ้อนกว่า ทำให้ต้องใช้เวลาในการแก้ไขมากกว่าที่ควรจะเป็น

จะดีกว่ามั๋ย ถ้าหากว่า เราสามารถใช้ excel formula ได้ในหน้าเว็บเลย โดยไม่ต้องมาแปลงเป็นภาษาอื่นและ sa หรือใครก็ตามสามารถมาแก้มันได้เอง จึงเป็นที่มาของ xsanisty/jquery-calx ที่จะช่วยใช้การทำงานเร็วขึ้นมาก

การใช้งานง่ายๆ เพียงแค่ใส่ data 2 ตัวคือ data-cell=” { cell reference } ” และ data-formula=” {สูตร excel ที่ต้องการ} “กับการเรียกใช้อีกเล็กน้อย

<script>
    $(document).ready(function() {
        $('#sheetA').calx();
    });
</script>

เท่านั้น

ตัวอย่างการใช้งาน

<!doctype html>
<html>

<head>
    <meta charset="utf-8">
    <title>jQuery Calx: basic</title>
    <link href="../vendor/twbs/bootstrap/dist/css/bootstrap.min.css" rel="stylesheet" rel="stylesheet" type="text/css">
</head>

<body>
    <table class="table table-bordered table-hover table-striped" id="sheetA" width="100%">
        <thead>
            <tr>
                <td width="58">No.</td>
                <td width="145">First Name</td>
                <td width="126">Last Name</td>
                <td width="135">Relationship to policyholder / main insured</td>
                <td width="191">Employee name<br /> (main insured)</td>
                <td width="100">Period<br /> Start Date<br /> (dd/mm/yy)
                </td>
                <td width="105">Effetive Date<br /> (dd/mm/yy)
                </td>
                <td width="101">Period<br /> End Date<br /> (dd/mm/yy)
                </td>
                <td width="109">DOB</td>
                <td width="51">Age</td>
                <td width="113">ACF - Vital<br /> Annual Premium</td>
                <td width="113">Annually Premium included AGA</td>
                <td width="113">Annual Premium<br /> after 10%<br /> Gr. Discntd</td>
                <td width="107">Prorated Premium<br /> (USD)
                </td>
                <td width="74">Duration Days</td>
            </tr>
        </thead>
        <tbody>
            <tr>
                <td>1</td>
                <td>Tianyi</td>
                <td>Deng</td>
                <td>Employee</td>
                <td>-</td>
                <td>15-May-17</td>
                <td>20-Oct-17</td>
                <td>14-May-18</td>
                <td>24-May-1989</td>
                <td>27</td>
                <td data-cell="K24">1,022.00</td>
                <td data-cell="L24" data-formula="K24+197"></td>
                <td data-cell="M24" data-formula="L24-(L24*0.1)"></td>
                <td data-cell="N24" data-formula="-(M24*O24)/365"></td>
                <td data-cell="O24" data-formula="H24-G24+1"></td>
            </tr>
        </tbody>
        <tfoot>
            <tr>
                <td colspan="8"></td>
                <td colspan="5">Total Premium to be Refund(USD)</td>
                <td>(622.19)</td>
                <td data-cell="O25" data-formula="N27"></td>
            </tr>
        </tfoot>
    </table>
    <script src="../vendor/components/jquery/jquery.min.js"></script>
    <script src="jquery-calx-2.2.7.min.js"></script>
    <script>
        $(document).ready(function() {
            $('#sheetA').calx();
        });
    </script>
</body>

</html>

เพียงเท่านี้ เราก็สามารถจะคำนวณค่าต่างๆ ได้เหมืือนใช้ excel ได้แล้วโดยไม่ต้องแก้สูตรให้ยุ่งยากเลย

PHPExcel: จัดรูปแบบ format ข้อมูล

เมื่อวานเขียน export ข้อมูลออกเป็นไฟล์ excel โดยใช้เวลาไม่นาน เพราะโครงสร้างการทำงานมันเหมือนๆ งานที่เคยทำมา แต่มาตกม้าตายเอาที่การฟอร์เมตของแต่ละ column ให้ตรงกับชนิดข้อมูล เช่น type เป็น date ก็ควรให้เห็นเป็นวันที่ไม่ใช่เลข 1234155 อะไรก็ไม่ทราบ

<?php

include '../vendor/phpoffice/phpexcel/Classes/PHPExcel.php';

set_time_limit(0);

$objPHPExcel = new PHPExcel();

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

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

$defaultStyle->getNumberFormat()
    ->setFormatCode('yyyy-mm-dd');

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

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

$columns = [
    'row_number' => ['title' => 'No.', 'type' => 'row_number'],

    'price' => ['title' => 'ราคา', 'type' => 'currency'],

    'dateEnd' => ['title' => 'เริ่มจำหน่าย', 'type' => 'date'],
    'dateStart' => ['title' => 'เริ่มจำหน่าย', 'type' => 'date'],

    'dateApproved' => ['title' => 'เวลาอนุมัติ', 'type' => 'datetime'],

    'height' => ['title' => 'สูง (เมตร)', 'type' => 'float'],
    'width' => ['title' => 'กว้าง (เมตร)', 'type' => 'float'],

    'calculate' => ['title' => 'สูตรคํานวณหวย', 'type' => 'formula'],

    'image' => ['title' => 'ภาพ', 'type' => 'image'],

    'items' => ['title' => 'จำนวน', 'type' => 'integer'],

    'productName' => ['title' => 'ชื่อสินค้า', 'type' => 'string'],

    'timeEnd' => ['title' => 'เวลาขาย', 'type' => 'time'],
    'timeStart' => ['title' => 'เวลาปิดการขาย', 'type' => 'time'],

    'url' => ['title' => 'page', 'type' => 'url'],
];

/* header */
$colNo = -1;
$rowNo = 1;
$colStrings = [];
foreach ($columns as $fieldId => $field) {
    $colNo++;
    $colStrings[$colNo] = $colString = PHPExcel_Cell::stringFromColumnIndex($colNo);
    $objWorkSheet->setCellValue($colString . '1', $field['title']);
    $objWorkSheet->setCellValue($colString . '2', 'type = ' . $field['type']);
}
$headerHeight = $rowNo = 2;

$objPHPExcel->getActiveSheet()->freezePane($colString . ($headerHeight + 1));

/* random data */
$datas = [];
for ($a = 0; $a < 10; $a++) {
    $temp = [];

    $temp['calculate'] = '=RAND()';
    $temp['dateApproved'] = date(DATE_ISO8601, mt_rand(0, 1499291999));
    $temp['dateEnd'] = date('Y-m-d', mt_rand(0, 1499291999));
    $temp['dateStart'] = date('Y-m-d', mt_rand(0, 1499291999));
    $temp['height'] = mt_rand(0, 10) / 10;
    $temp['image'] = 'http://lorempixel.com/400/200/sports/?st=' . mt_rand(1, 500);
    $temp['items'] = mt_rand(999, 9999999);
    $temp['price'] = mt_rand(100, 10000);
    $temp['productName'] = substr(str_shuffle(str_repeat($x = '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', ceil(10 / strlen($x)))), 1, 10);
    $temp['timeEnd'] = date('H:i:s', mt_rand(0, 1499291999));
    $temp['timeStart'] = date('H:i:s', mt_rand(0, 1499291999));
    $temp['url'] = 'https://plusmagi.com/?s=' . mt_rand(1, 500);
    $temp['width'] = mt_rand(0, 10) / 10;

    array_push($datas, $temp);
}

/* add data */
$row_number = 0;
foreach ($datas as $item) {
    $colNo = -1;
    $row_number++;
    $rowNo++;
    foreach ($columns as $fieldId => $field) {
        $colNo++;

        $coordinate = $colStrings[$colNo] . $rowNo;

        switch ($field['type']) {
            case 'date':
            case 'datetime':
            case 'time':{
                    $ts = strtotime($item[$fieldId]);
                    $value = PHPExcel_Shared_Date::PHPToExcel($ts);
                }break;

            case 'image':{
                    $value = $item[$fieldId];

                    $gdImage = imagecreatefromjpeg($value);
                    $objDrawing = new PHPExcel_Worksheet_MemoryDrawing(); /*create object for Worksheet drawing*/

                    $objDrawing->setCoordinates($coordinate); /*set image to cell*/
                    $objDrawing->setDescription('Customer Signature'); /*set description to image*/
                    $objDrawing->setHeight(50);
                    $objDrawing->setImageResource($gdImage);
                    $objDrawing->setName('Customer Signature'); /*set name to image*/
                    $objDrawing->setOffsetX(25); /*setOffsetX works properly*/
                    $objDrawing->setOffsetY(10); /*setOffsetY works properly*/
                    $objDrawing->setWidth(100); /*set width, height*/

                    $objDrawing->setWorksheet($objWorkSheet); /*save*/

                    $objWorkSheet->getRowDimension($rowNo)->setRowHeight(60); /* set row height*/
                }break;

            case 'row_number':{
                    $value = $row_number;
                }break;

            case 'url':{
                    $value = str_replace('http://', '', $item[$fieldId]);
                    $objWorkSheet->getCell($coordinate)
                        ->getHyperlink()
                        ->setTooltip('Click here to access page')
                        ->setUrl($item[$fieldId]);
                }break;

            default:{
                    $value = $item[$fieldId];
                }break;
        }

        $objWorkSheet->setCellValue($coordinate, $value);
    }
}

/* auto width column */
$cellIterator = $objWorkSheet->getRowIterator()->current()->getCellIterator();
$cellIterator->setIterateOnlyExistingCells(true);
foreach ($cellIterator as $cell) {
    $objWorkSheet->getColumnDimension($cell->getColumn())->setAutoSize(true);
}

/* format for columns */
$colNo = -1;
foreach ($columns as $fieldId => $field) {
    $colNo++;

    $coordinate = $colStrings[$colNo] . ($headerHeight + 1) . ':' . $colStrings[$colNo] . $rowNo;

    switch ($field['type']) {

        case 'currency':{
                $objWorkSheet->getStyle($coordinate)
                    ->getNumberFormat()
                    ->setFormatCode('#,##0.00');
                /*->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER);*/
            }break;

        case 'date':{
                $objWorkSheet->getStyle($coordinate)
                    ->getNumberFormat()
                    ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_DMYSLASH);
            }break;

        case 'datetime':{
                $objWorkSheet->getStyle($coordinate)
                    ->getNumberFormat()
                    ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_DATETIME);
                /*->setFormatCode('Y-m-d H:i:s');*/
            }break;

        case 'float':{
                $objWorkSheet->getStyle($coordinate)
                    ->getNumberFormat()
                    ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
            }break;

        case 'integer':
        case 'row_number':{
                $objWorkSheet->getStyle($coordinate)
                    ->getNumberFormat()
                    ->setFormatCode('#,##');
            }break;

        case 'time':{
                $objWorkSheet->getStyle($coordinate)
                    ->getNumberFormat()
                    ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME4);
                /*->setFormatCode('Y-m-d H:i:s');*/
            }break;

        default:{
                $objWorkSheet->getStyle($coordinate)
                    ->getNumberFormat()
                    ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_GENERAL);
            }break;
    }

}

$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');

หลักการต้องทำงานคู่กัน 2 ส่วนคือ

  1. ส่วนใส่ข้อมูล บางชนิดต้องแปลงข้อมูลก่อน เช่น date, datetime, timestamp และ time ต้องเปลี่ยนเป็น unix timestamp ก่อน
  2. ส่วนกำหนด cell format (ในตัวอย่างให้วิธีกำหนดทั้ง column ไปเลย) ต้องเลือกรูปแบบที่เหมาะสมโดยจะกำหนดเอง
    ->setFormatCode('Y-m-d H:i:s');

    หรือจะใช้ตามมาตราฐานก้ได้ Class PHPExcel_Style_NumberFormat ก็ได้

PHPExcel : ตั้งค่าหน้ากระดาษ

วันนี้ตอนที่กำลังเขียน code โดยใช้ PHPExcel โดยพยามแต่งรูปแบบไฟล์ excel ให้มันสวยงามอยู่ เพื่อนโปรแกรมเมอร์อีกคน เตือนด้วยความหวังดีว่า จัดไปก็เท่านั้นเดี๋ยว user ก็พิมพ์ให้มันพัง ออกแบบให้พิมพ์แนวนอน แต่พี่แกก็ print ออกมาเป็นแบบแนวตั้ง

กลับมาบ้านลองหาคู่มือดู PHPExcel มันกำหนด page setup ให้เอกสารได้ด้วย ^_^ ก็เลยลองเขียนดู

<?php

include 'vendor/phpoffice/phpexcel/Classes/PHPExcel.php';

$objPHPExcel = new PHPExcel();

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

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

$defaultStyle->getNumberFormat()
    ->setFormatCode('yyyy-mm-dd');

/* 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);

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

/*
printer page setup
https://github.com/PHPOffice/PHPExcel/blob/develop/Documentation/markdown/Overview/08-Recipes.md
 */

/* print header and footer of a worksheet */
$objWorkSheet->getHeaderFooter()->setOddFooter('&L&B' . $objPHPExcel->getProperties()->getTitle() . '&RPage &P of &N');
$objWorkSheet->getHeaderFooter()->setOddHeader('&C&HPlease treat this document as confidential!');

/* page margins */
$objWorkSheet->getPageMargins()->setBottom(1);
$objWorkSheet->getPageMargins()->setLeft(0.75);
$objWorkSheet->getPageMargins()->setRight(0.75);
$objWorkSheet->getPageMargins()->setTop(1);

/* Setting a worksheet's page orientation and size */
$objWorkSheet->getPageSetup()->setFitToPage(true);
$objWorkSheet->getPageSetup()->setFitToWidth(true);
$objWorkSheet->getPageSetup()->setHorizontalCentered(true);
$objWorkSheet->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
$objWorkSheet->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);

/* Specify printing area */
$objWorkSheet->getPageSetup()->setPrintArea('A1:E11');

/* add demo data */
for ($rowNo = 1; $rowNo < 10; $rowNo++) {
    for ($colNo = 0; $colNo < 5; $colNo++) {

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

        $coordinate = $colString . $rowNo;

        $objWorkSheet->setCellValue($coordinate, 'Add Data To ' . $coordinate);
    }
}

/* auto width column */
$cellIterator = $objWorkSheet->getRowIterator()->current()->getCellIterator();
$cellIterator->setIterateOnlyExistingCells(true);
foreach ($cellIterator as $cell) {
    $objWorkSheet->getColumnDimension($cell->getColumn())->setAutoSize(true);
}

/* write */
$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');

เปิดกับ LibreOffice Writer ก็ใช้ได้จริงๆ (สมัยนี้ใครเค้าใช้ microsoft word กันแล้ว ถ้าต้องจ่ายตัง) การกำหนดรูปแบบอื่นๆ อ่านได้จากตัวอย่าง PHPExcel recipes จัดได้หลากหลายมาก จะเลือกกระดาษ มาร์จิ้น สี เส้น เลขหน้า แนวนอน แนวตั้ง ทำได้หมด

PHPExcel แสดงตัวเลขเป็นวันที่

×Warning! ใช้วิธีในบทความ PHPExcel: จัดรูปแบบ format ข้อมูล จะดีกว่าวิธีนี้

เขียนระบบส่งออกข้อมูล ทดสอบดูโดยใช้ LibreOffice Calc ปกติดี แต่ใน Microsoft Excel 2013 กลับแสดงตัวเลขทั้งหมดเป็นวันที่

งานรีบ (ตลอด) ไม่อยากเขียนเงื่อนไขให้เช็กว่าช่องนี้คอลัมน์นั้นเป็นตัวหนังสือ หรือว่าตัวเลข ก็ตั้งให้ที่ column เป็น text ไปเลยละกัน

<?php

error_reporting(E_ALL);
ini_set('display_errors', true);
ini_set('display_startup_errors', true);

include 'config.php';

/* PHPExcel_IOFactory - Reader */
include '../vendor/phpoffice/phpexcel/Classes/PHPExcel.php';

$objPHPExcel = new PHPExcel();

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

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

$defaultStyle->getNumberFormat()
    ->setFormatCode('yyyy-mm-dd');

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

/* create new sheet */
$objWorkSheet = $objPHPExcel->getActiveSheet();
$objWorkSheet->setTitle('Subscription Datas');

$objWorkSheet->setCellValue('A1', 'subscription_id');
$objWorkSheet->setCellValue('B1', 'first name');
$objWorkSheet->setCellValue('C1', 'last name');
$objWorkSheet->setCellValue('D1', 'email');
$objWorkSheet->setCellValue('E1', 'mobile');
$objWorkSheet->setCellValue('F1', 'major');
$objWorkSheet->setCellValue('G1', 'congratulations year');
$objWorkSheet->setCellValue('H1', 'date create');

$sql = "SELECT *
FROM subscriptions
WHERE enable = 1
ORDER BY sort ASC, skill_id ASC;";
$query = $conn->prepare($sql);
$query->execute();
$results = $query->fetchAll(PDO::FETCH_ASSOC);
$colNo = -1;
$rowNo = 1;

foreach ($results[0] as $key => $value) {
    $colNo++;
    $colStrings[$key] = $column = PHPExcel_Cell::stringFromColumnIndex($colNo);
    $objWorkSheet->setCellValue($column . $rowNo, $key);
}
$objPHPExcel->getActiveSheet()->getStyle('A1:' . $column . '1')->getFont()->setBold(true);

foreach ($results as $result) {
    $rowNo++;
    foreach ($result as $key => $value) {
        $objWorkSheet->setCellValueExplicit($colStrings[$key] . $rowNo, $value, PHPExcel_Cell_DataType::TYPE_STRING);
    }
}

$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');

ที่แก้จริงๆ คือ ใช้

$objWorkSheet->setCellValueExplicit($colStrings[$key] . $rowNo, $value, PHPExcel_Cell_DataType::TYPE_STRING);

กรอกข้อมูลพร้อมกำหนดชนิดของข้อมูลใน column จะ formula, inline, null, numeric, string ที่ต้องการ จะใช้ชนิดอื่นก็ใช้ตามคู่มือ Class: PHPExcel_Cell_DataType

สร้าง excel เป็นชุดแบบหลายหน้าชีท

บางครั้งก็ต้องส่งออกข้อมูลเป็นชุดจำนวนมากหลายชีท ข้อมูลหนึ่งก็ใส่ไว้ในอีกชีทหนึ่ง ก็ใช้การแก้เล็กน้อยจากตัวอย่าง สร้าง excel จาก 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);

$defaultStyle->getNumberFormat()
    ->setFormatCode('yyyy-mm-dd');

/* 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);

/* multiple_sheets */
for ($sheetNo = 0; $sheetNo < 10; $sheetNo++) {
    $objWorkSheet = $objPHPExcel->createSheet($sheetNo);
    $objWorkSheet->setTitle('Sheet Nane '.$sheetNo);
    $objWorkSheet->setCellValue('A1', 'Add data for sheet no. '.$sheetNo);
}

/* set active sheet */
$objPHPExcel->setActiveSheetIndex(5);
$objWorkSheet = $objPHPExcel->getActiveSheet();
$objWorkSheet->setCellValue('A4', 'Add data for sheet no. 5 after Sheet Nane '.$sheetNo);

$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');

อธิบาย

  1. บรรทัดที่ 28 – 32 จะเป็นการสร้าง sheet ขึ้นมาใหม่ และอ้างถึงชีทนี้โดยใช้ $objWorkSheet ในการใส่ข้อมูลลงไป
  2. บรรทัดที่ 35 – 37 เราสามารถกลับมาแก้ไข sheet ที่สร้างไปแล้วโดยใช้คำสั่ง setActiveSheetIndex และ getActiveSheet จากนั้นอ้างถึงชีทนี้โดยใช้ $objWorkSheet
  3. สังเกตุว่า setActiveSheetIndex ใช้การนับลำดับ sheet เริ่มจาก 0 ไม่ใช่ 1
  4. มีชีทชือ Worksheet เกินอยู่ มันเป็น default ของตัว PHPExcel เอง จริงๆเราควรจะใช้ คำสั่ง
    $objWorkSheet = $objPHPExcel->getActiveSheet();
    $objWorkSheet->setTitle('Exports Datas');
    

    กับแผ่นนี้ แล้วใช้เหมือนกับชีทแผ่นอื่นๆ ก่อนที่จะ loop สร้าง sheet ขึ้นมาใหม่

สร้าง excel จาก PHP

วิธีส่งออกข้อมูลที่ ยูเซอร์สดวกที่จะเอาไปใช้ต่อมากที่สุดคือ ส่งออกมาเป็นเอกซ์เซล เพราะามารถเอาไปใช้ได้ทันที คุ้นเคยอยู่แล้ว ไม่รู้สึกว่ายุ่งยากในการใช้งาน

ภาษา PHP สามารถส่งออกข้อมูลเป็น excel ได้ง่ายตามขั้นตอนดังนี้

  1. โหลดตัว PHPExcel มาจาก PHPExcel
  2. แตกไฟล์ออกมา
  3. เขียน code ตามตัวอย่าง
<?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);

$defaultStyle->getNumberFormat()
    ->setFormatCode('yyyy-mm-dd');

/* 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);

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

for ($rowNo = 1; $rowNo < 10; $rowNo++) {
    for ($colNo = 0; $colNo < 5; $colNo++) {

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

        $coordinate = $colString . $rowNo;

        $objWorkSheet->setCellValue($coordinate, 'Add Data To '.$coordinate);
    }
}

$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');

อธิบาย

  1. บรรทัดที่ 4 เรียกใช้ตัว libarry PHPExcel
  2. บรรทัดที่ 8 – 16 จะกำหนดค่า default ของไฟล์ excel จะไม่มีก็ได้
  3. บรรทัดที่ 18 – 25 ใส่ข้อมูลพื้นฐานให้ไฟล์ excel จะไม่มีก็ได้ แต่ถ้ามีจะมีประโยชน์ในการค้นหาไฟล์นี้ในภายหลัง
  4. บรรทัดที่ 28, 29 เป็นการเปลี่ยนชื่อ sheet ให้สื่อความหมาย ไม่มีก็ได้เช่นกัน
  5. บรรทัดที่ 31 – 40 จะเป็นส่วนที่ใส่ข้อมูลลงใน sheet ในการใช้งานจริงๆ จะเป็น loop ที่ดึงข้อมูลจาก database มากรอก
  6. บรรทัดที่ 42 – 49 เป็นส่วนที่เขียนออกมาเป็นไฟล์ และให้ download ไฟล์ออกมา

Note: อย่า echo หรือแสดงข้อมูลไม่งั้นจะมี error ประมาณ Excel cannot open the file ‘xxx.xlsx’ because the file format of file extension is not valid. Verify that the file has been corrupted and that the file extension matches the format of the file.

อ่านข้อมูลจาก excel

ตัวอย่างการอ่านข้อมูลใน excel โดยใช้ PHPExcel

<?php

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

$inputFileName = 'importsDatas.xlsx';

$inputFileType = PHPExcel_IOFactory::identify($inputFileName);

$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objReader->setReadDataOnly(true);

$objPHPExcel = $objReader->load($inputFileName);

/* select sheet */
$objWorksheet = $objPHPExcel->setActiveSheetIndex(0);

/* range of datas */
$highestColumn = $objWorksheet->getHighestColumn();

$highestColumnNumber = PHPExcel_Cell::columnIndexFromString($highestColumn);

$highestRow = $objWorksheet->getHighestRow();

echo '<!doctype html>',
'<html>',
'<head>',
'<meta charset="utf-8">',
'<title>PHPExcel Reader</title>',
'<body>',
'<table border="1" width="100%"><caption>Highest Row = ', $highestRow, ' Highest Column = ', $highestColumn, ' (', $highestColumnNumber, ')</caption><tbody>';

for ($rowNo = 1; $rowNo <= $highestRow; $rowNo++) {
    echo '<tr>';
    for ($colNo = 0; $colNo < $highestColumnNumber; $colNo++) {

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

        $coordinate = $colString . $rowNo;

        $cell = $objWorksheet->getCell($coordinate);

        $value = trim($cell->getValue());

        echo '<td>', $value, ' <sup>', $coordinate, '</sup></td>';
    }
    echo '</tr>';
}
echo '</tbody></table></body></html>';

พื้นฐานคือ

  1. บรรทัด 8 – 13 ให้ PHPExcel อ่านจากไฟล์ไหน เป็น excel version ไหน setReadDataOnly บอกว่าให้เปิดเพืออ่านข้อมูลอย่างเดียวนะ มันจะเร็วกว่าเพราะไม่ต้องเตรียมการเพื่อเขียนข้อมูลด้วย
  2. setActiveSheetIndex จะบอกว่าให้อ่าน sheet ไหน โดยเริ่มนับจาก 0 ไม่ใช่ 1
  3. บรรทัด 19 – 23 เป็นการหาขอบเขตที่มีการเขียนข้อมูลเอาไว้ เพราะว่าไม่ต้องการให้ loop ไปอ่านเรื่อยๆโดยที่ไม่มีข้อมูลอะไรเลย เปลืองทรัพยากร เสียเวลาไปเปล่าๆ
  4. บรรทัด 33 – 35 เป็นการ loop อ่านข้อมูล มาสร้างเป็น coordinate อย่าง G1, G2, G3, …, G7 แล้วแต่ว่าจะเขียนเงื่อนไขให้เริ่มอ่านจากจุดไหน เพราะบางไฟล์แถวแรกๆ จะเป็น header ไม่ใช่ข้อมูล สาเหตุที่ต้องใช้ columnIndexFromString ทั้งๆที่มี getHighestColumn แล้ว เพราะว่าต้องการลำดับ column เอาไป loop จะใช้ตัวอักษรไป loop ไม่ได้
  5. บรรทัด 41 ใช้ getCell ดึงข้อมูลออกมาใช้ทีละ coordinate

อ่านเพิ่มเติม Import วันที่โดย PHPExcel