อิมพอร์ต – PlusMagi's Blog By Pitt Phunsanit

โปรแกรมจัดการ mysql ง่ายๆ เร็วๆ

การจัดการ database คู่บุญ PHP อย่าง MySql หรือ mariadb ร้อยทั้งร้อย และทุกโฮสต์เตรียมเอาไว้ให้ใช้คือ phpMyAdmin แต่มันตัวอ้วนใหญ่ขึ้นทุกๆปี ตามลูกเล่นที่มากขึ้น ถ้ารีบเอางานขึ้นหรือเตรียมไว้เป็นช่องทางฉุกเฺฉินขอแนะนำ Adminer เป็นมายเอสคิวแอล management ที่ตัวเล็กกว่าเยอะ ฟรี ที่สำคัญคือมีไฟล์แค่ไฟล์เดียว

การติกตั้งแค่โหลดมากจากเว็บ เปลี่ยนชื่อซะหน่อยเพื่อความปลอดภัย ให้เป็นชื่อแบบที่เรารู้อยู่แค่คนเดียว แล้ว ftp ขึ้นไป เรียกใช้ได้เลย อาจจะไม่ถนัดเท่าตัวเดิมแต่การติดตั้ง script import export data แบ็คอัพแก้ตารางก็ทำได้ดี สะดวกพอใช้

Import วันที่โดย PHPExcel

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

หาเจอในเน็ต how to get date from excel using PHPExcel library แล้วก็หลายๆที่ ก็ใช้วิธี

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

แต่ผลที่ได้ก็เหมือนเดิม

เกือบจะยอมแพ้แล้วจน ไปเจอคนจุดประเด็นว่า 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 โดยเพิ่ม

$objPHPExcel->getDefaultStyle()->getNumberFormat()
	->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2);

โดยมีรูปแบบอื่นๆ ให้เลือกคือ Class: PHPExcel_Style_NumberFormat

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

หล้งจากแก้ข้อมูลแล้วต้องแน่ใจว่าใส่ข้อมูลถูก format เป็น yyyy-mm-dd จริงๆ เพราะว่าตอนเทสเจอใส่ 10/09/1987 มา ค่า value ก่อนแปลงได้ -2208211200 แทนที่จะเป็น 143856000 และหลังแปลงได้ปี 2036-02-16 ไม่ใช่ปี 1987 วันที่มันจะผิดไปหมดเลยครับ อาจจะใช้

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

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

อ่านข้อมูลจาก 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>';

พื้นฐานคือ

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

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

PlusMagi's Blog By Pitt Phunsanit

Tag Archive อิมพอร์ต

โปรแกรมจัดการ mysql ง่ายๆ เร็วๆ

Import วันที่โดย PHPExcel

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