Tag Archive default

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

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

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

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

  1. ส่วนใส่ข้อมูล บางชนิดต้องแปลงข้อมูลก่อน เช่น date, datetime, timestamp และ time ต้องเปลี่ยนเป็น unix timestamp ก่อน
  2. ส่วนกำหนด cell format (ในตัวอย่างให้วิธีกำหนดทั้ง column ไปเลย) ต้องเลือกรูปแบบที่เหมาะสมโดยจะกำหนดเอง[code language=”php” title=”custom format”]->setFormatCode(‘Y-m-d H:i:s’);[/code] หรือจะใช้ตามมาตราฐานก้ได้ Class PHPExcel_Style_NumberFormat ก็ได้

default file รูปด้วย .htaccess

ทำระบบสมาชิกโดยแต่ละ user จะมีภาพประจำตัว โดยเก็บในรูปแบบ \datas\peoples\id ของ user.jpg แต่บางคน (ส่วนใหญ่) ไม่ส่งภาพมา แต่ในหลายๆ หน้าจะมีจุดที่แสดงภาพสมาชิก

มี 2 ทางเลือกคือ

  • ใช้คำสั่ง file_exists ตรวจสอบดูว่ามีไฟล์นี้รึเปล่า ถ้ามีก็เอาภาพไปแสดง ถ้าไม่ก็แสดงภาพเป็นเงาดำๆ ให้ดูไป
  • อีกวิธีคือ แสดงภาพเหมือนว่ามีไฟล์นี้จริงๆ แต่ถ้าหาไฟล์ไม่เจอก็ให้ server ไปดึงไฟล์ดีฟอล์ตมาแสดง โดยใน code ไม่ต้องตรวจสอบอะไรเลย

วิธีการคือ สร้างไฟล์ .htaccess ใน \datas\peoples\ โดยมีเนื้อหาคือ[code language=”text”]
RewriteEngine on

# If requested resource exists as a file or directory go to it
RewriteCond %{REQUEST_FILENAME} -f
RewriteRule (.*) – [L]

# Else rewrite requests for non-existent .jpg to default.jpg
RewriteRule (.jpg$) default.jpg [L]

# Else rewrite requests for non-existent resources to 404.html
RewriteRule (.*) ../404.html [L]
[/code] เมื่อมีการเรียกใช้ภาพ .jpg มาใน folder \datas\peoples\ ตัว apache จะไปหาก่อนว่าไฟล์นั้นมีหรือเปล่า ถ้าไม่มีก็จะใช้ไฟล์ default.jpg มาแสดงแทน แค่นี้ก็ไม่ต้องตรวจสอบทุกจุดที่แสดงภาพแล้วว่ามีภาพจริงๆ หรือเปล่า

ถ้าใช้ git อ่านเรื่อง git: ไม่สนไฟล์ในโพลเดอร์นี้ ยกเว้น ด้วย

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

บางครั้งก็ต้องส่งออกข้อมูลเป็นชุดจำนวนมากหลายชีท ข้อมูลหนึ่งก็ใส่ไว้ในอีกชีทหนึ่ง ก็ใช้การแก้เล็กน้อยจากตัวอย่าง สร้าง excel จาก PHP

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

อธิบาย

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

Import วันที่โดย PHPExcel

ใช้ PHPExcel ดึงข้อมูลออกมา แต่ไม่สามารถ insert เข้า database ได้เลย ลอง debug เอา query string ออกมาดูก็เห็นสาเหตุ เพราะวันที่โดนแปลงจาก 1982-08-05 ไปเป็นตัวเลขประมาณ 3455 ทุก row เลย พอเข้า sql server มันก็ฟ้องมาว่าไม่ใช่วันที่นะ

หาเจอในเน็ต how to get date from excel using PHPExcel library แล้วก็หลายๆที่ ก็ใช้วิธี[code language=”php”]
$cell = $objWorksheet->getCell(‘A2’);
$value = trim($cell->getValue());
/* detect if the cell value is formated to date or not */
if (\PHPExcel_Shared_Date::isDateTime($cell)) {
$value = date(‘Y-m-d’, \PHPExcel_Shared_Date::ExcelToPHP($value));
}
[/code]แต่ผลที่ได้ก็เหมือนเดิม

เกือบจะยอมแพ้แล้วจน ไปเจอคนจุดประเด็นว่า isDateTime มันตรวจโดยใช้ format mm-dd-yyyy ใน PHPExcel detect if the cell value is formated to date or not ลองเทสดูก็จริง เพราะเดิมใช้เป็น format yyyy-mm-dd ลองเปลี่ยนเป็น mm-dd-yyyy ดู สามารถดึงออกมาเป็นวันที่แล้ว ไม่ใช้ int เหมือนเดิม

เพราะว่า sql server มันใช้รูปแบบ yyyy-mm-dd และไม่อยากจะเปลี่ยนไปเปลี่ยนมา เรียงตาม ปี เดือน วัน มันดูง่ายดีแล้ว วันไหนก่อนหลัง ก็ลองหาทางดู จะเปลี่ยนคอนฟิกใหม่ ให้ใช้วันที่แบบ yyyy-mm-dd โดยเพิ่ม[code language=”php” title=”PHPExcel 1=Date 2=Format”]
$objPHPExcel->getDefaultStyle()->getNumberFormat()
->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2);
[/code] โดยมีรูปแบบอื่นๆ ให้เลือกคือ Class: PHPExcel_Style_NumberFormat

ทดลองนำเข้าข้อมูลอีกครั้ง สามารถนำเข้าข้อมูลได้โดยไม่มีปัญหาแล้ว

หล้งจากแก้ข้อมูลแล้วต้องแน่ใจว่าใส่ข้อมูลถูก format เป็น yyyy-mm-dd จริงๆ เพราะว่าตอนเทสเจอใส่ 10/09/1987 มา ค่า value ก่อนแปลงได้ -2208211200 แทนที่จะเป็น 143856000 และหลังแปลงได้ปี 2036-02-16 ไม่ใช่ปี 1987 วันที่มันจะผิดไปหมดเลยครับ อาจจะใช้[code language=”php”]
if (!preg_match(‘/^(19|20)\d\d[\-\/.](0[1-9]|1[012])[\-\/.](0[1-9]|[12][0-9]|3[01])$/’, $value)) {
$this->error = true;

$cell->setValue(‘Your date "’. $cell->getValue().’" does not match the YYYY-MM-DD required format.’);
$objWorksheet->getStyle($coordinate)->applyFromArray($this->stylesError);
}
[/code]

อ่านเพิ่มเติม อ่านข้อมูลจาก excel

YII 2 : Horizontal / inline form

ตามปกติ form ของ yii ที่ใช้ GII generate ออกมาจะเป็นรูปแบบ label บรรทัดหนึ่ง input อีกบรรทัดหนึ่ง อ่านง่าย สวยงามพอสมควร แต่อาจจะไม่ถูกใจบรรดาหัวหน้าอนุรักษ์นิยมทั้งหลาย

yii มีอีกทางเลือกให้ใช้ทำฟอร์ม คือ มี Horizontal form ให้เลือกใช้ yii bootstrap activeform (Horizontal form) อีกตัว

วิธีการคือ

  1. เปิดไฟล์ _form.php ใน view เป้าหมาย
  2. เปลี่ยน use yii\widgets\ActiveForm; เป็น use yii\bootstrap\ActiveForm; ถ้าไม่เปลี่ยนจะเจอ error Setting unknown property: yii\widgets\ActiveForm::layout
  3. เปลี่ยน[code language=”PHP”]$form = ActiveForm::begin();[/code]เป็น[code language=”PHP”]$form = ActiveForm::begin([
    ‘fieldConfig’ => [
    ‘horizontalCssClasses’ => [
    ‘label’ => ‘col-sm-2’,
    ‘offset’ => ‘col-sm-offset-2’,
    ‘wrapper’ => ‘col-sm-10’
    ]
    ],
    ‘layout’ => ‘horizontal’
    ]);[/code]
  4. สำเร็จแล้ว ฟอร์มจะเปลี่ยนเป็นรูปแบบเหมือนกับ Bootstrap Horizontal form โดย

    • layout คือ จะวางฟอร์มเป็นแบบไหน มีให้เลือก คือ default, horizontal และ inline
    • label คือ ค่า grid ของ label
    • wrapper คือ ความกว้างของช่อง input
    • offset คือ การเว้นระยะของพวก checkbox ตั้งไว้เท่ากับ label จะได้ตรงกันพอดี

    เสร็จแล้ว หรือจะใช้การจัด code แบบของผมก็ได้ครับ ไม่อยากสลับ php เข้าออก ไปๆ มาๆ [code language=”PHP”]
    <?php

    use yii\helpers\Html;
    use yii\bootstrap\ActiveForm;

    ?>

    <div class="user-form">
    <?php
    $form = ActiveForm::begin([
    ‘fieldConfig’ => [
    ‘horizontalCssClasses’ => [
    ‘label’ => ‘col-sm-2’,
    ‘offset’ => ‘col-sm-offset-2’,
    ‘wrapper’ => ‘col-sm-10’
    ]
    ],
    ‘layout’ => ‘horizontal’
    ]);

    echo $form->field($model, ‘username’)->textInput(),
    $form->field($model, ’email’)->textInput(),
    $form->field($model, ‘status’)->checkbox(array(‘label’=>’enable’)),
    ‘<div class="form-group">’,Html::submitButton($model->isNewRecord ? Yii::t(‘app’, ‘Create’) : Yii::t(‘app’, ‘Update’), [‘class’ => $model->isNewRecord ? ‘btn btn-success’ : ‘btn btn-primary’]);
    ActiveForm::end();
    echo'</div>’;[/code]