ป้ายกำกับ: ดาต้า

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

2015-03-072015-03-07| phunsanitphunsanit| 0 Comment | 00:00

เมื่อวานเขียน 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 ก็ได้

Read MoreRead More