Tag Archive excel

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 ได้แล้วโดยไม่ต้องแก้สูตรให้ยุ่งยากเลย

Joget: สร้างรายงาน pdf excel

การสร้างรายงานใน joget ทำได้โดยใช้ Jaspersoft® Studio สร้างรูปแบบรายงาน จากนั้นก็ให้ joget เรียกใช้ jasper libary สร้างรายงานออกมาให้เรา

การกรอกข้อมูล report

  1. สร้าง report ให้เรียบร้อยใน jaspersoft studio
  2. เปิด user view ขึ้นมา กด Add Category ด้านล่าง ขึ้นมาใหม่ คลิก Permission Icon คลิก Hide From Menu จะได้ไม่แสดง ในเมนูให้ผู้ใช้เห็น
  3. ลาก JasperReports มาวางใน Category ของเรา
  4. กรอกข้อมูล
    • Custom ID เป็น id ที่จะอ้างถึงเวลาที่เราต้องการดู report
    • Label เป็นแค่ชื่อที่ทำให้จำ JasperReports ว่าตัวไหนเป็นตัวไหน จะเห็นแค่ใน user view เท่านั้นไม่เกี่ยวกับตอนออกรายงาน ควรตั้งชื่อให้สื่อความหมาย
  5. Jasper Reports Definition (JRXML) คือ code ที่ได้มาจาก jaspersoft studio
  6. คลิก next
  7. Datasource คือ database connection ปกติใช้เป็น Default Datasource
  8. คลิก next
  9. ระบุ parameters ที่ส่งให้ report โดย
    • Name คือ ชื่อ parameter ที่ report ต้องการ
    • Value คือ ค่าที่จะส่งไป ถ้าส่งค่าด้วย url สามารถใช้ hash variable #requestParam. ชื่อ parameter # ได้เลยเช่น #requestParam.member_id#

การเรียกดูรายงาน

จะทำได้โดยเรียก

  • url domain / server ip /: port โดยทั่วไปคือ 8080
  • action=report
  • appId= application id
  • appVersion= version ของ user view
  • key=_&
  • menuId= คือ Custom ID ใน userview นั่นเอง
  • type= จะแสดงเป็นไฟล์ format อะไร มีให้เลือก pdf และ xls
  • userviewId= คือ id ของ userview
  • query string ที่จะส่ง parameter ให้ report เช่น &member_id=xxxxx

ถ้า

  • userview ของเรามี url http://localhost:8080/jw/web/console/app/develop/1/userview/builder/testUserview
  • Custom ID มีค่า profile
  • Parameter ที่ต้องส่งไปคือ member_id

จะเรียกดูรายงานได้จากรูปแบบ http://localhost:8080/jw/web/json/plugin/org.joget.plugin.enterprise.JasperReportsMenu/service?action=report&appId=develop&appVersion=1&key=_&menuId=profile&type=pdf&userviewId=testUserview&member_id=0910097693

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: ใส่ภาพในไฟล์

บางครั้งการ export ข้อมูลออกไปเป็นไฟล์ excel ก็ต้องการใส่ภาพลงไปด้วย อาจจะเป็นโลโก้บริษัท หรือว่าเป็นภาพตัวอย่างสินค้า PHPExcel สามารถแทรกภาพได้ทั้งจาก file หรือ url ของภาพที่ออนไลน์อยู่

<?php

include 'vendor/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');

/* image path */
$images = [
    'https://raw.githubusercontent.com/PHPOffice/PHPExcel/1.8/Examples/images/officelogo.jpg',
    'https://raw.githubusercontent.com/PHPOffice/PHPExcel/1.8/Examples/images/paid.png',
    'https://raw.githubusercontent.com/PHPOffice/PHPExcel/1.8/Examples/images/phpexcel_logo.gif',
    'https://raw.githubusercontent.com/PHPOffice/PHPExcel/1.8/Examples/images/termsconditions.jpg',
    'vendor/phpoffice/phpexcel/Examples/images/officelogo.jpg',
    'vendor/phpoffice/phpexcel/Examples/images/paid.png',
    'vendor/phpoffice/phpexcel/Examples/images/phpexcel_logo.gif',
    'vendor/phpoffice/phpexcel/Examples/images/termsconditions.jpg',
];

$rowNo = 0;
foreach ($images as $image) {
    $rowNo++;

    $coordinate = 'A' . $rowNo;

    $objWorkSheet->getRowDimension($rowNo)->setRowHeight(90);
    $objWorkSheet->setCellValue($coordinate, $image);

    switch (strtolower(pathinfo($image, PATHINFO_EXTENSION))) {
        case 'gif':
            $gdImage = imagecreatefromgif($image);
            $mimetype = PHPExcel_Worksheet_MemoryDrawing::MIMETYPE_GIF;
            $render = PHPExcel_Worksheet_MemoryDrawing::RENDERING_GIF;
            break;

        case 'jpeg':
        case 'jpg':
            $gdImage = imagecreatefromjpeg($image);
            $mimetype = PHPExcel_Worksheet_MemoryDrawing::MIMETYPE_JPEG;
            $render = PHPExcel_Worksheet_MemoryDrawing::RENDERING_JPEG;
            break;

        case 'png':
            $gdImage = imagecreatefrompng($image);
            $mimetype = PHPExcel_Worksheet_MemoryDrawing::MIMETYPE_PNG;
            $render = PHPExcel_Worksheet_MemoryDrawing::RENDERING_PNG;
            break;
    }

    $objDrawing = new PHPExcel_Worksheet_MemoryDrawing();

    $objDrawing->setCoordinates($coordinate);
    $objDrawing->setImageResource($gdImage);
    $objDrawing->setWorksheet($objWorkSheet);

    /* optional */
    $objDrawing->setDescription($image);
    $objDrawing->setMimeType($mimetype);
    $objDrawing->setName(pathinfo($image, PATHINFO_FILENAME));
    $objDrawing->setRenderingFunction($render);
    $objDrawing->setResizeProportional(true);
    $objDrawing->setWidth(100);

}

$objWorkSheet->getColumnDimension('A')->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');

อธิบาย

setCoordinates
ตำแหน่งที่ให้ภาพไปลอยอยู่
setDescription
รายละเอียด (optional)
setName
ชื่อภาพ (optional)
setResizeProportional
รักษาสัดส่วนของภาพเอาไว้
setWorksheet
อ้างถึง Active Sheet หรือ sheet ที่ใส่ข้อมูล
ถ้าเลือกใช้ PHPExcel_IOFactory::createWriter ผิด version เช่น ‘Excel5’ อาจจะทำให้ภาพมีสัดส่วนเพี้ยนได้

PHPExcel : กำหนด border

การใช้คำสั่ง border ตีเส้นตารางช่วยในการแบ่งขอบเขตุข้อมูลและช่วยให้อ่านให้ง่ายขึ้น

ตัวอย่าง

<?php

/* http://stackoverflow.com/questions/27764204/how-to-do-the-phpexcel-outside-border */
/* 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);

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

/* add background */
$background = [
    'fill' => [
        'color' => [
            'rgb' => 'FF9',
        ],
        'type' => PHPExcel_Style_Fill::FILL_SOLID,
    ],
];

$borders = [
    'allborders' => [
        'borders' => [
            'allborders' => [
                'style' => PHPExcel_Style_Border::BORDER_HAIR,
            ],
        ],
    ],
    'bottom' => [
        'borders' => [
            'bottom' => [
                'style' => PHPExcel_Style_Border::BORDER_HAIR,
            ],
        ],
    ],
    'diagonal (both)' => [
        'borders' => [
            'diagonal' => [
                'style' => PHPExcel_Style_Border::BORDER_HAIR,
            ],
            'diagonaldirection' => PHPExcel_Style_Borders::DIAGONAL_BOTH,
        ],
    ],
    'diagonal (down)' => [
        'borders' => [
            'diagonal' => [
                'style' => PHPExcel_Style_Border::BORDER_HAIR,
            ],
            'diagonaldirection' => PHPExcel_Style_Borders::DIAGONAL_DOWN,
        ],
    ],
    'diagonal (none)' => [
        'borders' => [
            'diagonal' => [
                'style' => PHPExcel_Style_Border::BORDER_HAIR,
            ],
            'diagonaldirection' => PHPExcel_Style_Borders::DIAGONAL_NONE,
        ],
    ],
    'diagonal (up)' => [
        'borders' => [
            'diagonal' => [
                'style' => PHPExcel_Style_Border::BORDER_HAIR,
            ],
            'diagonaldirection' => PHPExcel_Style_Borders::DIAGONAL_UP,
        ],
    ],
    'horizontal' => [
        'borders' => [
            'horizontal' => [
                'style' => PHPExcel_Style_Border::BORDER_HAIR,
            ],
        ],
    ],
    'inside' => [
        'borders' => [
            'inside' => [
                'style' => PHPExcel_Style_Border::BORDER_HAIR,
            ],
        ],
    ],
    'left' => [
        'borders' => [
            'left' => [
                'style' => PHPExcel_Style_Border::BORDER_HAIR,
            ],
        ],
    ],
    'outline' => [
        'borders' => [
            'outline' => [
                'style' => PHPExcel_Style_Border::BORDER_HAIR,
            ],
        ],
    ],
    'right' => [
        'borders' => [
            'right' => [
                'style' => PHPExcel_Style_Border::BORDER_HAIR,
            ],
        ],
    ],
    'top' => [
        'borders' => [
            'top' => [
                'style' => PHPExcel_Style_Border::BORDER_HAIR,
            ],
        ],
    ],
    'vertical' => [
        'borders' => [
            'vertical' => [
                'style' => PHPExcel_Style_Border::BORDER_HAIR,
            ],
        ],
    ],
];

$bordersLine = [
    'BORDER_DASHDOT' => [
        'borders' => [
            'allborders' => [
                'style' => PHPExcel_Style_Border::BORDER_DASHDOT,
            ],
        ],
    ],
    'BORDER_DASHDOTDOT' => [
        'borders' => [
            'allborders' => [
                'style' => PHPExcel_Style_Border::BORDER_DASHDOTDOT,
            ],
        ],
    ],
    'BORDER_DASHED' => [
        'borders' => [
            'allborders' => [
                'style' => PHPExcel_Style_Border::BORDER_DASHED,
            ],
        ],
    ],
    'BORDER_DOTTED' => [
        'borders' => [
            'allborders' => [
                'style' => PHPExcel_Style_Border::BORDER_DOTTED,
            ],
        ],
    ],
    'BORDER_DOUBLE' => [
        'borders' => [
            'allborders' => [
                'style' => PHPExcel_Style_Border::BORDER_DOUBLE,
            ],
        ],
    ],
    'BORDER_HAIR' => [
        'borders' => [
            'allborders' => [
                'style' => PHPExcel_Style_Border::BORDER_HAIR,
            ],
        ],
    ],
    'BORDER_MEDIUM' => [
        'borders' => [
            'allborders' => [
                'style' => PHPExcel_Style_Border::BORDER_MEDIUM,
            ],
        ],
    ],
    'BORDER_MEDIUMDASHDOT' => [
        'borders' => [
            'allborders' => [
                'style' => PHPExcel_Style_Border::BORDER_MEDIUMDASHDOT,
            ],
        ],
    ],
    'BORDER_MEDIUMDASHDOTDOT' => [
        'borders' => [
            'allborders' => [
                'style' => PHPExcel_Style_Border::BORDER_MEDIUMDASHDOTDOT,
            ],
        ],
    ],
    'BORDER_MEDIUMDASHED' => [
        'borders' => [
            'allborders' => [
                'style' => PHPExcel_Style_Border::BORDER_MEDIUMDASHED,
            ],
        ],
    ],
    'BORDER_NONE' => [
        'borders' => [
            'allborders' => [
                'style' => PHPExcel_Style_Border::BORDER_NONE,
            ],
        ],
    ],
    'BORDER_SLANTDASHDOT' => [
        'borders' => [
            'allborders' => [
                'style' => PHPExcel_Style_Border::BORDER_SLANTDASHDOT,
            ],
        ],
    ],
    'BORDER_THICK' => [
        'borders' => [
            'allborders' => [
                'style' => PHPExcel_Style_Border::BORDER_THICK,
            ],
        ],
    ],
    'BORDER_THIN' => [
        'borders' => [
            'allborders' => [
                'style' => PHPExcel_Style_Border::BORDER_THIN,
            ],
        ],
    ],

];

$objWorkSheet->setCellValue('C1', 'Borders');
$objWorkSheet->getStyle('C1')->getFont()->setBold(true);
$rowNo = -1;
foreach ($borders as $name => $style) {
    $rowNo += 4;

    $objWorkSheet->setCellValue('A' . $rowNo, $name);

    /* merge background */
    $style = array_merge($background, $style);

    $objWorkSheet->getStyle('D' . $rowNo . ':F' . ($rowNo + 2))->applyFromArray($style);

}

$objWorkSheet->getStyle('G3:G53')->applyFromArray($borders['right']);

$objWorkSheet->setCellValue('M1', 'Line');
$objWorkSheet->getStyle('M1')->getFont()->setBold(true);
$rowNo = 1;
foreach ($bordersLine as $name => $style) {
    $rowNo += 2;

    $objWorkSheet->setCellValue('I' . $rowNo, $name);

    /* merge background */
    $style = array_merge($background, $style);

    $objWorkSheet->getStyle('N' . $rowNo)->applyFromArray($style);
}

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

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

สร้าง grid ใน laravel แบบ advance

การใช้ Nayjest Grids แบบ advance เพื่อที่จะสามารถค้นหา filter ข้อมูล, export ไฟล์ ออกมาในรูปแบบ .csv และ excel ได้

ติดตั้งโดย

  1. เปิดไฟล์ composer.json เพิ่ม
    ...
            "laravelcollective/html": "^5",
            "maatwebsite/excel": "~2.1.0",
            "nayjest/grids": "^1.3.1"
    ...
    
  2. Command
    composer update
  3. เปิดไฟล์ \config\app.php เพิ่ม
        'providers' => [
    ...
            'Collective&#92;Html&#92;HtmlServiceProvider',
            'Maatwebsite&#92;Excel&#92;ExcelServiceProvider',
            'Nayjest&#92;Grids&#92;ServiceProvider',
    ...
    
        'aliases' => [
    ...
            'Excel' => 'Maatwebsite&#92;Excel&#92;Facades&#92;Excel',
            'Form' => 'Collective&#92;Html&#92;FormFacade',
            'Grids' => 'Nayjest&#92;Grids&#92;Grids',
            'HTML' => 'Collective&#92;Html&#92;HtmlFacade',
            'Input' => 'Illuminate&#92;Support&#92;Facades&#92;Input',
    ...
    ]

สร้าง grid โดย

  1. <?php
    
    namespace App\Http\Controllers;
    
    use App\user;
    use HTML;
    use Nayjest\Grids\Components\Base\RenderableRegistry;
    use Nayjest\Grids\Components\ColumnHeadersRow;
    use Nayjest\Grids\Components\ColumnsHider;
    use Nayjest\Grids\Components\CsvExport;
    use Nayjest\Grids\Components\ExcelExport;
    use Nayjest\Grids\Components\FiltersRow;
    use Nayjest\Grids\Components\Filters\DateRangePicker;
    use Nayjest\Grids\Components\HtmlTag;
    use Nayjest\Grids\Components\Laravel5\Pager;
    use Nayjest\Grids\Components\OneCellRow;
    use Nayjest\Grids\Components\RecordsPerPage;
    use Nayjest\Grids\Components\RenderFunc;
    use Nayjest\Grids\Components\ShowingRecords;
    use Nayjest\Grids\Components\TFoot;
    use Nayjest\Grids\Components\THead;
    use Nayjest\Grids\EloquentDataProvider;
    use Nayjest\Grids\FieldConfig;
    use Nayjest\Grids\FilterConfig;
    use Nayjest\Grids\Grid;
    use Nayjest\Grids\GridConfig;
    
    class UsersController extends Controller
    {
    
        public function Index()
        {
    
            $query = (new User)
                ->query();
    
            $grid = new Grid(
                (new GridConfig)
                    ->setDataProvider(
                        new EloquentDataProvider($query)
                    )
                    ->setName('UserIndex') /* grid unque id */
                    ->setPageSize(20)
                    ->setColumns([
                        (new FieldConfig)
                            ->addFilter(
                                (new FilterConfig)
                                    ->setOperator(FilterConfig::OPERATOR_LIKE)
                            )
                            ->setLabel('ID')
                            ->setName('id') /* field */
                            ->setSortable(true)
                            ->setSorting(Grid::SORT_DESC)
                        ,
                        (new FieldConfig)
                            ->addFilter(
                                (new FilterConfig)
                                    ->setOperator(FilterConfig::OPERATOR_LIKE)
                            )
                            ->setLabel('Name')
                            ->setName('name')
                            ->setSortable(true)
                        ,
                        (new FieldConfig)
                            ->addFilter(
                                (new FilterConfig)
                                    ->setOperator(FilterConfig::OPERATOR_LIKE)
                            )
                            ->setLabel('Email')
                            ->setName('email')
                            ->setSortable(true)
                        ,
                        (new FieldConfig)
                            ->addFilter(
                                (new FilterConfig)
                                    ->setOperator(FilterConfig::OPERATOR_LIKE)
                            )
                            ->setLabel('Update Information')
                            ->setName('count_update')
                            ->setSortable(true)
                        ,
                        (new FieldConfig)
                            ->setLabel('Created')
                            ->setName('created_at')
                            ->setSortable(true)
                        ,
                        (new FieldConfig)
                            ->setLabel('Last Update')
                            ->setName('updated_at')
                            ->setSortable(true),
                    ])
                    ->setComponents([
                        (new THead)
                            ->setComponents([
                                (new ColumnHeadersRow),
                                (new FiltersRow)
                                    ->addComponents([
                                        (new RenderFunc(function () {
                                            return HTML::style('js/daterangepicker/daterangepicker-bs3.css')
                                            . HTML::script('js/moment/moment-with-locales.js')
                                            . HTML::script('js/daterangepicker/daterangepicker.js')
                                                . "<style>
                                                    .daterangepicker td.available.active,
                                                    .daterangepicker li.active,
                                                    .daterangepicker li:hover {
                                                        color:black !important;
                                                        font-weight: bold;
                                                    }
                                               </style>";
                                        }))
                                            ->setRenderSection('filters_row_column_created_at'),
                                        (new DateRangePicker)
                                            ->setName('created_at')
                                            ->setRenderSection('filters_row_column_created_at')
                                            ->setDefaultValue(['1990-01-01', date('Y-m-d H:i:s')]),
                                        (new DateRangePicker)
                                            ->setName('created_at')
                                            ->setRenderSection('filters_row_column_updated_at')
                                            ->setDefaultValue(['1990-01-01', date('Y-m-d H:i:s')]),
                                    ])
                                ,
                                (new OneCellRow)
                                    ->setRenderSection(RenderableRegistry::SECTION_END)
                                    ->setComponents([
                                        new RecordsPerPage,
                                        new ColumnsHider,
                                        (new CsvExport)
                                            ->setFileName('usersExport_' . date('Y-m-d'))
                                        ,
                                        (new ExcelExport())
                                            ->setFileName('usersExport_' . date('Y-m-d'))
                                        ,
                                        (new HtmlTag)
                                            ->setContent('<span class="glyphicon glyphicon-refresh"></span> Filter')
                                            ->setTagName('button')
                                            ->setRenderSection(RenderableRegistry::SECTION_END)
                                            ->setAttributes([
                                                'class' => 'btn btn-success btn-sm',
                                            ]),
                                    ]),
                            ])
                        ,
                        (new TFoot)
                            ->setComponents([
                                (new OneCellRow)
                                    ->setComponents([
                                        new Pager,
                                        (new HtmlTag)
                                            ->setAttributes(['class' => 'pull-right'])
                                            ->addComponent(new ShowingRecords)
                                        ,
                                    ]),
                            ])
                        ,
                    ])
            );
            $grid = $grid->render();
    
            return view('users.index', [
                'grid' => $grid,
            ]);
        }
    
    }
    
  2. สร้าง view \resources\views\users\index.blade.php
    @extends('layouts.app')
    
    @section('title', 'Users')
    
    @section('main-content') <a href="{{ url('/excels/form') }}">
    <button class="btn btn-success btn-sm"><span class="glyphicon glyphicon-upload"></span> Import Datas</button>
    </a> @if(!empty($text))
    <div class="container">{!! $text !!}</div>
    @endif
    <?=$grid;?>
    @stop
  3. สร้าง route โดยไปที่ \app\Http\routes.php
    ...
    Route::get('/users/index', 'UsersController@index');
    ...

ทดลองโดยเรียก /users/index ในเครื่องผมคือ http://localhost/laravel52/public/users/index grid แสดงข้อมูลออกมาแล้วแต่ จะส่งออกเป็นไฟล์ csv หรือ excel ยังไงละ ใจเย็นๆครับ อ่าน สร้าง grid ใน laravel แบบ advance ครับ

สร้างไฟล์ csv จาก phpexcel

phpexcel สร้างไฟล์ excel ได้สะดวกมาก แต่ไม่ใช่ทุกระบบจะสามารถ import ข้อมูลจากไฟล์แบบเอ็กเซลได้ การส่งออกไฟล์แบบตัวอักษรล้วนๆ และใช้เครื่องหมายคั่นแบบไฟล์ csv จึงยังจำเป็นอยู่

ที่สำคัญคือสามารถแก้หรือเพิ่มเงื่อนไขจาก code ส่งออกข้อมูลเป็น excel โดย phpexcel ที่มีอยู่เดิม โดยดัดแปลงเล็กน้อยในส่วน header และการ write data

<?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);

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

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

        $insert = rand(0, 1);
        if($insert == 1) {

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

            $coordinate = $colString . $rowNo;

            $objWorkSheet->setCellValue($coordinate, 'ส่งออกข้อมูล -> '.$coordinate);
        }
    }
}

header('Cache-Control: max-age=0');
header('Content-Encoding: UTF-8');
header('Content-Description: File Transfer');
header('Content-Type: application/octet-stream');
header('Content-type: text/csv; charset=UTF-8');
header('Content-Disposition: attachment;filename="'.$title.'.csv"');
echo "\xEF\xBB\xBF"; /* UTF-8 BOM */

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'CSV');

/* set Delimiter, (defaults ,) */
//$objWriter->setDelimiter('|');
//$objWriter->setEnclosure('');
/* Set line ending (defaults PHP_EOL) */
//$objWriter->setLineEnding('0x0d0a');

$objWriter->save('php://output');

ถ้าสังเกตุจะมี $insert = rand(0, 1); และ if($insert == 1) { ทำให้บาง cell ไม่มีข้อมูล เพื่อที่จะแสดงให้เห็นว่าการใช้ phpexcel จะดีกว่าการใช้ echo ส่งออกข้อมูลแบบที่ง่ายที่สุด (CSV) ในกรณีที่บางคอลัมข้อมูลไม่มี เราไม่จำเป็นต้อง echo ค่าว่าง หรือมานับ , ว่าครบมั๋ย phpexcel จะจัดการให้เอง

ถ้าเปลี่ยนรูปแบบอื่นเช่น

  • ต้องการใช้เครื่องหมายอื่นเช่น pipe | ก็สามารถกำหนดโดยใช่ setDelimiter
  • ต้องการ wrap ข้อมูลก็ใช้ setEnclosure จะปิดหัว ปิดท้ายข้อมูลแต่ละ cell ให้
  • ต้องการใช้เครื่องมายอื่นในการแบ่งข้อมูลออกเป็นชุดๆ ก็ setLineEnding

เขียนครั้งเดียวส่งออกข้อมูลได้ 2 รูปแบบ ส่วนคำสั่งที่ไม่ได้ใช้ในรูปแบบ csv เช่น setTitle ก็ไม่ทำให้มี error ดีจริงๆ phpexcel