Tag Archive string

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 ก็ได้

ส่งค่าตัวแปรในเช็กบ็อกซ์โดย jQuery

เขียนระบบ update ข้อมูลโดยใช้ ajax แต่ปัญหาคือ ใน form นี้มันมี input อยู่หลายตัว และเพราะว่าต้องการแค่ input ที่ชื่อ items[] ตัวเดียวเท่านั้น ถ้าส่ง data ไปโดยใช้ jQuery โดยปกติจะส่งค่าไปโดยใช้ .serialize() หรือ .serializeArray() ก็จะส่งตัวแปรอื่นๆ ที่ไม่จำเป็นติดไปด้วย จนทำให้ url ยาวจนเกิน limit ทำให้ต้องหาวิธีส่งไปเฉพาะตัวที่ใช้จริงๆ เท่านั้น

ตัวอย่างการส่งค่าไปในแบบ array

<!doctype html>
<html>

<head>
    <meta charset="utf-8">
    <title>checkbox to array</title>
</head>

<body>
    <ul>
        <li>
            <input name="items[]" type="checkbox" value="c1">
        </li>
        <li>
            <input name="items[]" type="checkbox" value="c2">
        </li>
        <li>
            <input name="items[]" type="checkbox" value="c3">
        </li>
        <li>
            <input name="items[]" type="checkbox" value="c4">
        </li>
        <li>
            <input name="items[]" type="checkbox" value="c5">
        </li>
        <li>
            <input name="items[]" type="checkbox" value="c6">
        </li>
        <li>
            <input name="items[]" type="checkbox" value="c7">
        </li>
        <li>
            <input name="items[]" type="checkbox" value="c8">
        </li>
        <li>
            <input name="items[]" type="checkbox" value="c9">
        </li>
        <li>
            <input name="items[]" type="checkbox" value="c10">
        </li>
    </ul>
    <div id="result"></div>
    <button id="sendBtn" type="submit">Send</button>
    <script src="jquery-3.1.1.min.js"></script>
    <script>
    $(function() {

        $('#sendBtn').click(function() {
            var checkboxs = $('input[name="items\\[\\]"]:checked');

            alert('checked ' + checkboxs.length + ' items');

            var values = checkboxs.map(function() {
                    return $(this).val();
                })
                .get();

            var params = {
                "id": 24,
                "items": values,
            };
            $.ajax({
                "data": params,
                "success": function(data) {
                    $('#result').html(data);
                },
                "type": "GET",
                "url": "processing.php",
            });

        });

    });
    </script>
</body>

</html>

ตัวอย่างการส่งค่าไปในแบบ string

<!doctype html>
<html>

<head>
    <meta charset="utf-8">
    <title>checkbox to string</title>
</head>

<body>
    <ul>
        <li>
            <input name="items[]" type="checkbox" value="c1">
        </li>
        <li>
            <input name="items[]" type="checkbox" value="c2">
        </li>
        <li>
            <input name="items[]" type="checkbox" value="c3">
        </li>
        <li>
            <input name="items[]" type="checkbox" value="c4">
        </li>
        <li>
            <input name="items[]" type="checkbox" value="c5">
        </li>
        <li>
            <input name="items[]" type="checkbox" value="c6">
        </li>
        <li>
            <input name="items[]" type="checkbox" value="c7">
        </li>
        <li>
            <input name="items[]" type="checkbox" value="c8">
        </li>
        <li>
            <input name="items[]" type="checkbox" value="c9">
        </li>
        <li>
            <input name="items[]" type="checkbox" value="c10">
        </li>
    </ul>
    <div id="result"></div>
    <button id="sendBtn" type="submit">Send</button>
    <script src="jquery-3.1.1.min.js"></script>
    <script>
    $(function() {

        $('#sendBtn').click(function() {
            var checkboxs = $('input[name="items\\[\\]"]:checked');

            alert('checked ' + checkboxs.length + ' items');

            var values = checkboxs.map(function() {
                    return $(this).val();
                })
                .get()
                .join();

            alert('values = ' + values);

            var encoded = encodeURIComponent(values);

            alert('URL encoded = ' + encoded);

            var decoded = decodeURIComponent(encoded);

            alert('URL decoded = ' + decoded);

            $.ajax({
                "data": 'id=24&items=' + encoded,
                "success": function(data) {
                    $('#result').html(data);
                },
                "type": "GET",
                "url": "processing.php",
            });

        });

    });
    </script>
</body>

</html>

ตัวอย่างการนำค่าไปเขียนเป็น sql query

<dl>
  <dt>method="GET"</dt>
  <dd><?=print_r($_GET, true);?></dd>
  <dt>method="POST"</dt>
  <dd><?=print_r($_POST, true);?></dd>
</dl>
<?php
if (is_array($_REQUEST['items'])) {
    echo '<br>send items by array';
    $where = "WHERE id IN('" . implode("', '", $_REQUEST['items']) . "')";
} else {
    echo '<br>send items by string';
    $where = "WHERE id IN('" . str_replace(',', "', '", $_REQUEST['items']) . "')";
}

$query = "SELECT *
FROM table_name
$where;";

echo '<br>example query = ' . $query;

เลือกใช้การส่งแบบสตริงหรืออาร์เรย์ ก็แล้วแต่ความสดวก ที่ต้องนำไป loop อีกหรือแค่ใช้ในการสร้างเอสคิวแอลคิวรีอย่างเดียว

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