จัดรูปแบบข้อมูลในพิกัด Cell ด้วย PhpSpreadsheet เบื้องต้น ตอนที่ 3

เขียนเมื่อ 5 ปีก่อน โดย Ninenik Narkdee
phpspreadsheet

คำสั่ง การ กำหนด รูปแบบ ตัวอย่าง เทคนิค ลูกเล่น การประยุกต์ การใช้งาน เกี่ยวกับ phpspreadsheet

ดูแล้ว 6,386 ครั้ง


เนื้อหาในตอนต่อไปนี้ เราจะมาเพิ่มเติมจากตอนที่แล้ว เกี่ยวกับ
การจัดการกับรูปแบบของข้อมูลที่แสดงในพิกัด Cell   โดยเราจะใช้งานร่วมกับ
ชุดข้อมูลจากฐานข้อมูล 
    ในที่นี้ เราจะจำลองสร้างข้อมูลสำหรับทดสอบ เป็นชุดข้อมูลคล้ายกับเนื้อหาตอนที่แล้ว
ทบทวนได้ที่ http://niik.in/929   
    โดยจะทำการเพิ่มข้อมูลในลักษณะที่คล้ายกันเข้าไปในฐานข้อมูล จากนั้น จะดึงข้อมูลมาสร้างเไฟล์ excel 
พร้อมจัดรูปแบบข้อมูลใสแต่ละ cell 
 

โครงสร้างตารางฐานข้อมูลประกอบ

--
-- Table structure for table `tbl_excel1`
--

CREATE TABLE `tbl_excel1` (
  `id` int(11) NOT NULL,
  `type_integer` int(11) DEFAULT NULL,
  `type_double` double DEFAULT NULL,
  `type_string` varchar(100) DEFAULT NULL,
  `type_null` char(10) DEFAULT NULL,
  `type_str_int` varchar(100) DEFAULT NULL,
  `type_str_double` varchar(100) DEFAULT NULL,
  `type_str_fraction` varchar(20) DEFAULT NULL,
  `type_str_percent` varchar(10) DEFAULT NULL,
  `type_str_zerolead` varchar(100) DEFAULT NULL,
  `type_date` date DEFAULT NULL,
  `type_datetime` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `tbl_excel1` (`id`, `type_integer`, `type_double`, `type_string`, `type_null`, `type_str_int`, `type_str_double`, `type_str_fraction`, `type_str_percent`, `type_str_zerolead`, `type_date`, `type_datetime`) VALUES
(1, 100, 21.35, 'one', NULL, '100', '21.35', '10/11', '5%', '0801111110', '2019-07-10', '2019-07-13 10:10:00'),
(2, 101, 22.35, 'two', NULL, '101', '22.35', '11/11', '5%', '0801111111', '2019-07-11', '2019-07-13 11:11:00'),
(3, 102, 23.35, 'three', NULL, '102', '23.35', '12/11', '5%', '0801111112', '2019-07-12', '2019-07-13 12:12:00'),
(4, 103, 24.35, 'four', NULL, '103', '24.35', '13/11', '5%', '0801111113', '2019-07-13', '2019-07-13 13:13:00'),
(5, 104, 25.35, 'five', NULL, '104', '25.35', '14/11', '5%', '0801111114', '2019-07-14', '2019-07-13 14:14:00'),
(6, 105, 26.35, 'six', NULL, '105', '26.35', '15/11', '5%', '0801111115', '2019-07-15', '2019-07-13 15:15:00'),
(7, 106, 27.35, 'NULL', NULL, '106', '27.35', '16/11', '5%', '0801111116', '2019-07-16', '2019-07-13 16:16:00'),
(8, 107, 28.35, 'null', NULL, '107', '28.35', '17/11', '5%', '0801111117', '2019-07-17', '2019-07-13 17:17:00'),
(9, 108, 29.35, '0', NULL, '108', '29.35', '18/11', '5%', '0801111118', '2019-07-18', '2019-07-13 18:18:00'),
(10, 109, 30.35, 'nine', NULL, '109', '30.35', '19/11', '5%', '0801111119', '2019-07-19', '2019-07-13 19:19:00'),
(11, 110, 31.35, 'ten', NULL, '110', '31.35', '20/11', '5%', '0801111120', '2019-07-20', '2019-07-13 20:20:00');


ALTER TABLE `tbl_excel1`
  ADD PRIMARY KEY (`id`);


ALTER TABLE `tbl_excel1`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=12;
COMMIT;
 
 

การสร้าง excel ไฟล์ จากข้อมูลในฐานข้อมูล

    ต่อไปเราจะมาดูแนวทางการดึงข้อมูลจากฐานข้อมูล  มาสร้างเป็นไฟล์ excel ซึ่งสามารถทำได้ดังนี้
<?php
// โค้ดไฟล์ dbconnect.php ดูได้ที่ http://niik.in/que_2398_5642
require_once("dbconnect.php");

// include composer autoload
require 'vendor/autoload.php';

// import the PhpSpreadsheet Class
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

// Set value binder
\PhpOffice\PhpSpreadsheet\Cell\Cell::setValueBinder( new \PhpOffice\PhpSpreadsheet\Cell\DefaultValueBinder() );
//\PhpOffice\PhpSpreadsheet\Cell\Cell::setValueBinder( new \PhpOffice\PhpSpreadsheet\Cell\AdvancedValueBinder() );

$spreadsheet = new Spreadsheet(); // สร้าง speadsheet object
$sheet = $spreadsheet->getActiveSheet(); // กำหนดการทำงานที่่แผ่นงานปัจจุบัน

// แสดงข้อมูลทั้งหมดของตาราง tbl_excel1
$sql = "
SELECT * FROM tbl_excel1
";
$result = $mysqli->query($sql);
if($result && $result->num_rows>0){  // คิวรี่ข้อมูลสำเร็จหรือไม่ และมีรายการข้อมูลหรือไม่
    $arrayData = $result->fetch_all();
    $totalRow = count($arrayData); // จำนวนแถวข้อมูลทั้งหมด
    $result->free(); // สามารถใช้ $result->close() หรือ $result->free_result() แทนได้
}

// กำหนดค่าให้กับพิกัด Cell ในรูปแบบข้อมูล array
/* $sheet->fromArray(
    $arrayData,  // ตัวแปร array ข้อมูล
    NULL,        // ค่าข้อมูลที่ตรงตามค่านี้ จะไม่ถูกำหนด
    'A1'         // จุดพิกัดเริ่มต้น ที่ใช้งานข้อมูล เริ่มทึ่มุมบนซ้าย  หากไม่กำหนดจะเป็น "A1" ค่าเริ่มต้น
    ); */
$sheet->fromArray($arrayData); // รูปแบบสั้น ผลลัพธ์เดียวกับด้านบน

// จัดขนาดความกว้างของ cell อย่างง่าย ตามจำนวนฟิลด์คอลัมน์ของฐานข้อมูล 
// ในที่นี้เราดึงฟิลด์ข้อมูลทั้งหมดในตาราง tbl_excel1 ซึ่งมีทั้งหมด 12 ฟิลด์ ก็แทนด้วยคอลัมน์ A ถึง L
foreach(range('A','L') as $column) {
    $sheet->getColumnDimension($column)->setAutoSize(true); 
}


$writer = new Xlsx($spreadsheet);
$output_file = "hello_world.xlsx"; // กำหนดชื่อไฟล์ excel ที่ต้องการ
$writer->save($output_file); // สร้าง excel 

if(file_exists($output_file)){ // ตรวจสอบว่ามีไฟล์ หรือมีการสร้างไฟล์ แล้วหรือไม่
    echo '<a href="'.$output_file.'" target="_blank">Download</a>';
    โค้ดตัวอย่างข้างต้น เป็นการดึงข้อมูลทั้งหมดจากตาราง tbl_excel1 มาสร้างเป็นข้อมูลในไฟล์ excel โดยข้อมูลที่ได้จาก
ตาราง tbl_excel1 จะเป็นข้อมูลประเภท string ผลลัพธ์เมื่อสร้างเป็นไฟล์ excel ก็จะได้รูปแบบแต่ละข้อมูลในแต่ละพิกัด Cell เป็น
ลักษณะข้อความ ดังนั้น เวลาที่เราจะนำข้อมูลในแต่ละ cell ไปใช้งานต่อ ก็อาจจะต้องทำการจัดูปแบบข้อมูลใน cell ก่อน  อย่างเช่น
ข้อมูลที่เป็นวันที่ ข้อมูลที่ตัวเลขเปอร์เซ็น เลขเศษส่วน เหล่านี้เป็นต้น
 
ผลลัพธ์ที่ได้
 

 
 
    จากรูป  เราจะเห็นว่าส่วนใหญ่ข้อมูลที่ได้จะอยู่ในรูปแบบของข้อความ  ส่วนคอลัมน์ E เนื่องจากค่าในฐานข้อมูลเป็น NULL ผลลัพธ์ที่ได้
เมื่อนำมาแสดงใน cell ก็จะไม่มีค่าใดๆ หรือเป็นค่าว่างตามรูป 
    เราลองมาใส่หัวข้อของแต่ละคอลัมน์ในแถวแรก โดยในที่นี้ จะใช้ชื่อเดียวกับชื่อฟิลด์ในฐานข้อมูล ทำได้ดังนี้
// กำหนดหัวข้อคอลัมน์
$columnName = ['id','integer','double','string','null','str_int','str_double','str_fraction','str_percent','str_zerolead','date','datetime'];
$sheet->fromArray($columnName) // array ข้อมูลหัวข้อคอลัมน์
->fromArray(
    $arrayData,  // ตัวแปร array ข้อมูล
    NULL,        // ค่าข้อมูลที่ตรงตามค่านี้ จะไม่ถูกำหนด
    'A2'         // จุดพิกัดเริ่มต้น ที่ใช้งานข้อมูล เริ่มทึ่มุมบนซ้าย  หากไม่กำหนดจะเป็น "A1" ค่าเริ่มต้น
);
ผลลัพธ์ที่ได้
 

 
 
    ต่อไปเราลองใช้วิธีการจัดรูปแบบข้อมูลในพิกัด Cell ด้วยการใช้งาน value binder โดยเอา comment ในส่วนของ 
AdvancedValueBinder class ออก และปิดการใช้งานรูปแบบ DefaultValueBinder จะได้เป็น
// Set value binder
//\PhpOffice\PhpSpreadsheet\Cell\Cell::setValueBinder( new \PhpOffice\PhpSpreadsheet\Cell\DefaultValueBinder() );
\PhpOffice\PhpSpreadsheet\Cell\Cell::setValueBinder( new \PhpOffice\PhpSpreadsheet\Cell\AdvancedValueBinder() );
ผลลัพธ์ที่ได้
 

 
 
    จะเห็นว่าตอนนี้รูปแบบข้อมูลใน cell มีการแปลงเป็นรูปแบบที่ถูกต้องมากขึ้น อย่างเลขเศษส่วนที่พิกัด H5 ที่แสดงเป็น 14/11 แต่ค่า
จริงของข้อมูลถูกแปลงเป็นเลขทศนิยม เท่ากับ 1.2727272727273 ตามรูปด้านบน ซึ่งสามารถนำไปใช้งานต่อได้เลย
เช่น เดียวกันกับข้อมูลรูปแบบวันที่ ที่ก่อนใช้งาน AdvancedValueBinder class ก็ยังแสดงในรูปแบบข้อความเท่านั้น แต่หลังจากใช้งาน
ก็ถูกแปลงให้อยู่ในรูปแบบข้อมูลตัวเลข timestamp และแสดงในรูปแบบข้อมูลที่เป็นวันที่
    โดยส่วนใหญ่แล้ว ข้อมูลในพิกัด Cell หลังจากใช้งาน AdvancedValueBinder  ก็จะมีรูปแบบตามที่ต้องการ แต่ก็มีบางข้อมูลที่ไม่ถูกต้อง
เช่น ข้อมูลที่มีเลข 0 นำหน้า อย่างเบอร์โทร หลังใช้ พบว่าถูกแปลงเป็นตัวเลข และเลข 0 ด้านหน้าก็หายไป  อย่างไรก็ตาม เราสามารถปรับ
เฉพาะส่วนของข้อมูลที่ไม่ถูกต้อง ไปพร้อมๆ กับการใช้งาน  AdvancedValueBinder ได้ ดูต่อในหัวข้อต่อไป
 
 

การจัดรูปแบบข้อมูลในพิกัด Cell

    เราจะมาจัดการเฉพาะพิกัดข้อมูลที่มีการแปลงข้อมูลโดยใช้ AdvancedValueBinder แล้วไม่ได้รูปแบบข้อมูลที่ต้องการ อย่างกรณีเบอร์โทร
ที่ถูกแปลงเป็นตัวเลข และเลข 0 ด้านหน้าหายไป เราสามารถแก้ไขได้ดังนี้
// กำหนดหัวข้อคอลัมน์
$columnName = ['id','integer','double','string','null','str_int','str_double','str_fraction','str_percent','str_zerolead','date','datetime'];
$sheet->fromArray($columnName)
->fromArray(
    $arrayData,  // ตัวแปร array ข้อมูล
    NULL,        // ค่าข้อมูลที่ตรงตามค่านี้ จะไม่ถูกำหนด
    'A2'         // จุดพิกัดเริ่มต้น ที่ใช้งานข้อมูล เริ่มทึ่มุมบนซ้าย  หากไม่กำหนดจะเป็น "A1" ค่าเริ่มต้น
    );

$sheet->getStyle('J2:J12') // กำหนดช่วงพิกัด Cell ที่ต้องการกำหนดรูปแบบช้อมูล
->getNumberFormat()
->setFormatCode('0000000000'); // จัดรูปแบบ คล้ายกำหนดรูปแบบข้อมูล กรณีนี้เป็นเบอร์โทร 10 หลัก
    รูปแบบการกำหนดโดยใช้คำสั่ง setFormatCode ข้างต้น ก็เหมือนกับเราใส่หน้ากากหรือ mask หรือรูปแบบของข้อมูล สมมติเช่น เราอยาก
ได้รูปแบบเบอร์โทรเป็น 080-1111110 เราก็สามารถกำหนดเป็น setFormatCode('000-0000000') แบบนี้เป็นต้น
     สำหรับการกำหนดช่วงพิกัด Cell ข้างต้นเรากำหนดตายตัวคือ J1:J12 ทั้งนี้เพราะจำนวนข้อมูลทดสอบเราไม่เยอะ และเราก็รู้พิกัดของข้อมูล
แต่ในความเป็นจริง ข้อมูลอาจจะมีจำนวนมากๆ และอาจจะมีการเปลี่ยนแปลงได้ตลอด ดังนั้น เราควรจะกำหนดโดยให้รองรับส่วนนี้ได้
เนื่องจากเรารู้ว่า จำนวนข้อมูลทั้งหมดมีกี่แถวจากตัวแปร $totalRow ดังนั้น เราสามารถประยุกต์ได้ เป็นดังนี้
$sheet->getStyle('J2:J'.($totalRow+1)) // จำนวนแถวทั้งหมด + จำนวนแถวที่เป็นหัวข้อ 
->getNumberFormat()
->setFormatCode('0000000000'); // จัดรูปแบบ คล้ายกำหนดรูปแบบข้อมูล กรณีนี้เป็นเบอร์โทร 10 หลัก
    ผลลัพธ์ที่ได้
 

 
 
    จะเห็นว่าในส่วนของเบอร์โทร มีเลข 0 นำหน้าตามรูปแบบที่เราต้องการเรียบร้อยแล้ว
    ถ้าเราสังเกตข้อมูลวันที่ข้างๆ จะเห็นว่ารูปแบบวันที่ จะเป็นรูปแบบมาตรฐาน หากต้องการนำข้อมูลนี้ไปใช้งานต่อ เช่น นำไปบันทึกลงฐานข้อมูล
รูปแบบข้างต้นก็ถือว่าเหมาะสมแล้ว แต่ถ้าเป็นกรณีว่า เราต้องการแสดงข้อมูลวันที่ในรูปแบบที่อ่านง่ายกว่านี้ เราจะทำยังไง  ดังนั้นเรามาดูต่อ
เกี่ยวกับการจัดรูปแบบข้อมูลวันที่ ซึ่งสามารถจัดรูปแบบได้ดังนี้
// กำหนดหัวข้อคอลัมน์
$columnName = ['id','integer','double','string','null','str_int','str_double','str_fraction','str_percent','str_zerolead','date','datetime'];
$sheet->fromArray($columnName)
->fromArray(
    $arrayData,  // ตัวแปร array ข้อมูล
    NULL,        // ค่าข้อมูลที่ตรงตามค่านี้ จะไม่ถูกำหนด
    'A2'         // จุดพิกัดเริ่มต้น ที่ใช้งานข้อมูล เริ่มทึ่มุมบนซ้าย  หากไม่กำหนดจะเป็น "A1" ค่าเริ่มต้น
    );
$sheet->getStyle('J2:J'.($totalRow+1)) // จำนวนแถวทั้งหมด + จำนวนแถวที่เป็นหัวข้อ 
->getNumberFormat()
->setFormatCode('0000000000'); // จัดรูปแบบ คล้ายกำหนดรูปแบบข้อมูล กรณีนี้เป็นเบอร์โทร 10 หลัก

$sheet->getStyle('K2:K'.($totalRow+1)) // จำนวนแถวทั้งหมด + จำนวนแถวที่เป็นหัวข้อ 
->getNumberFormat()
->setFormatCode( \PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_DATE_DDMMYYYY);

$sheet->getStyle('L2:L'.($totalRow+1)) // จำนวนแถวทั้งหมด + จำนวนแถวที่เป็นหัวข้อ
->getNumberFormat()
->setFormatCode( \PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_DATE_DATETIME);
    ผลลัพธ์ที่ได้
 
 
 
    เราสามารถจัดรูปแบบของวันที่ โดยใช้ตัวแปรค่าคงที่ดังนี้ เพิ่มเติมได้
 
    วันที่อย่างเดียว 2019-07-20 วันที่และเวลา   2019-07-20 20:20:02
 
FORMAT_DATE_YYYYMMDD2  -  2019-07-20
FORMAT_DATE_YYYYMMDD    -  19-07-20
FORMAT_DATE_DDMMYYYY    -  20/07/19
FORMAT_DATE_DMYSLASH     -  20/7/19
FORMAT_DATE_YYYYMMDDSLASH  -  19/07/20
FORMAT_DATE_DMYMINUS     -  20-7-19 
FORMAT_DATE_DMMINUS       -  20-7
FORMAT_DATE_MYMINUS       -  7-19
FORMAT_DATE_XLSX14           -  07-20-19
FORMAT_DATE_XLSX15           -  20-Jul-19
FORMAT_DATE_XLSX16           -  20-Jul
FORMAT_DATE_XLSX17           -  Jul-19
FORMAT_DATE_XLSX22           -  7/20/19 0:00  (วันที่อย่างเดียว)    |    7/20/19 20:20 (วันที่ + เวลา)
FORMAT_DATE_DATETIME      -  20/7/19 0:00    |    20/7/19 20:20
FORMAT_DATE_TIME1            -  12:00 AM (วันที่อย่างเดียว เวลาจะเป็นเที่ยงคืน)    |    8:20 PM
FORMAT_DATE_TIME2            -  12:00:00 AM    |    8:20:02 PM
FORMAT_DATE_TIME3            -  00:00         |    20:20 (ชั่วโมง:นาที)
FORMAT_DATE_TIME4            -  00:00:00    |    20:20:02
FORMAT_DATE_TIME5            -  00:00         |    20:02 (นาที:วินาที)
FORMAT_DATE_TIME6            -  00:00:00    |    20:20:02
FORMAT_DATE_TIME7            -  i:0.0           |    i:2.2
FORMAT_DATE_TIME8            -  0:00:00      |    20:20:02
 
    นอกจากการจัดรูปแบบวันที่ และเวลาโดยใช้รูปแบบตัวแปรค่าคงที่ข้างต้นแล้ว เรายังสามารถกำหนดรูปแบบด้วยตัวเองในรูปแบบ
$sheet->getStyle('K2:K'.($totalRow+1)) // จำนวนแถวทั้งหมด + จำนวนแถวที่เป็นหัวข้อ 
 ->getNumberFormat()
 ->setFormatCode("yyyy.mm.dd h:mm");
    โดยสามารถใช้ค่าเหล่านี้ เพื่อปรับแต่งตามต้องการ 
 
d         วันที่ไม่มี 0 นำหน้า
dd       วันที่มี 0 นำหน้ากรณีเลขหลักเดียว
ddd     ชื่อวันแบบย่อ เช่น Mon
dddd    ชื่อวันแบบเต็ม เช่น Monday
m        เดือนไม่มี 0 นำหน้า
mm     เดือนมี 0 นำหน้ากรณีเลขหลักเดียว
mmm    เดือนแบบย่อ เช่น Jul
mmmm เดือนแบบเต็ม เช่น July
yy        ปีสองตัวท้าย
yyyy     ปีสีหลัก
h หรือ hh            ชั่วโมงมี 0 นำหน้ากรณีเลขหลักเดียว
:mm หรือ mm:    นาทีมี 0 นำหน้ากรณีเลขหลักเดียว
ss        วินาทีมี 0 นำหน้ากรณีเลขหลักเดียว
 
    ต่อไปเป็นข้อมูลที่เป็นเปอร์เซ็นต์ หรือที่กำหนดในรูปแบบ 5% หรือ 12.155% สามารถใช้รูปแบบดังนี้
 
FORMAT_PERCENTAGE            -  5%         |    12% ปัดเศษ  ถ้าข้อมูลเดิมมีทศนิยม
FORMAT_PERCENTAGE_00       -  5.00%    |    12.16%        แสดงทศนิยม 2 ตำแหน่ง ปัดเศษ
 
    ต่อไปเป็นข้อมูลที่ต้องให้อยู่ในรูปแบบตัวเลข เช่นตัวเลข 0 และ 28111.35 สามารถใช้รูปแบบดังนี้
 
FORMAT_NUMBER                   -  0       ตัวเลขจำนวนเต็ม
FORMAT_NUMBER_00             -   0.00  ตัวเลขทศนิยม 2 ตำแหน่ง
FORMAT_NUMBER_COMMA_SEPARATED1  -  28,111.35  ตัวเลขทศนิยม 2 ตำแหน่งมีคอมม่าคั่น
 
    นอกจากนั้นยังมีรูปแบบสำหรับจัดการข้อมูลทั่วไป และข้อมูลที่ต้องการกำหนดให้เป็น text โดยใช้รูปแบบ
 
FORMAT_GENERAL    -  ข้อมูลทั่วไป ถ้าเป็นตัวเลข จะชิดขวา แต่ถ้าเป็นข้อความที่ทั่วไปชิดซ้าย
FORMAT_TEXT          -   ข้อความ text ข้อความไม่ว่าจะเป็นตัวเลขหรือไม่ จะถูกแปลงเป็น text และชิดซ้ายใน cell
 
 

    การใช้งานสูตรในพิกัด Cell

    เราสามารถกำหนดค่าโดยกำหนดจากการใช้งานสูตร สมมติเช่น เราจะเพิ่ม ข้อมูลผลรวมของ คอลัมน์ B และ C จะได้เป็น
$sheet ->setCellValue('B13', '=SUM(B2:B12)');
$sheet ->setCellValue('C13', '=SUM(C2:C12)');
    ตัวอย่างผลลัพธ์
 

 
 
    เราสามารถกำหนดค่าด้วยสูตร แล้วต่อด้วยการจัดรูปแบบข้อมูล เช่น สมมติเราต้องการตัวเลข มีคอมม่าคั่น และไม่มีเลขทศนิยม 
ก็อาจจะกำหนดเป็นดังนี้
$sheet ->setCellValue('B13', '=SUM(B2:B12)')
 ->getStyle('B13')
 ->getNumberFormat()
 ->setFormatCode('#,##0'); // เช่น 1,123 
    ทุกครั้งที่เราเพิ่มข้อมูลเข้าไปในพิกัด Cell โดยขึ้นต้นด้วยเครื่องหมาย = จะเป็นการเรียกใช้งานการกำหนดสูตรให้กับพิกัด Cell ดังนั้นกรณี
ที่เราจำเป็นที่จะแสดงเครื่องหมาย = เริ่มต้น อาจจะเป็น cell ที่ต้องการแสดงสูตรที่ใช้งาน เช่นนี้ เราสามารถกำหนดพิกัด Cell นั้นให้เป็นแบบ
ข้อความโดยใช้คำสั่ง  setCellValueExplicit() ตัวอย่างเช่น
$sheet->setCellValueExplicit(
     'B13',
     '=SUM(B2:B12)',
     \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING
     );
$sheet->setCellValueExplicit(
     'C13',
     '=SUM(C2:C12)',
     \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING
     );

// หรือจะใช้คำสั่ง setValueExplicit() ดังนี้ก็ได้

$sheet->getCell('B13')
->setValueExplicit(
     '=SUM(B2:B12)',
     \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING
     );
    รูปแบบข้างต้น ตำแหน่ง B13 และ C13 จะแสดงข้อความ =SUM(B2:B12) กับ =SUM(C2:C12) ตามลำดับ แทนการแสดงค่าจาก
การคำนวณจากสูตร เราสามารถกำหนด DataType ให้กับพิกัด Cell ร่วมกับการใช้งานฟังก์ชั่น setCellValueExplicit() ด้วยค่าต่างๆ เหล่านี้ได้
 
TYPE_STRING2	ข้อความ
TYPE_STRING	ข้อความ
TYPE_FORMULA	สูตร
TYPE_NUMERIC	ตัวเลข
TYPE_BOOL	ค่า TRUE / FALSE ถ้าข้อมูลเป็นตัวเลขที่ไม่ใช่ 0 หรือไม่ใช่ NULL หรือเป็นข้อความ จะแปลงเป็น TRUE 
TYPE_NULL	ค่าว่าง
TYPE_INLINE	ข้อความ richtext
TYPE_ERROR	error
 
    ตอนนี้เราได้เข้าใจเพิ่มเติมเบื้องต้น เกี่ยวกับการกำหนดค่าและจัดรูปแบบข้อมูลในพิกัด Cell   ทั้งรูปแบบที่สะดวกโดยใช้งาน Value Binder
หรือแม้แต่การกำหนดรูปแบบข้อมูลใน cell แยกเฉพาะแต่ละพิกัด Cell ได้
    เนื้อหาเกี่ยวกับ PhpSpreadsheet จะยังมีเพิ่มเติม รอติดตาม


   เพิ่มเติมเนื้อหา ครั้งที่ 1 วันที่ 17-07-2019


การแก้ไขรูปแบบข้อมูลเพิ่มเติม

ในกรณีที่เราต้องการแก้ไขข้อมูลที่ดึงมาจากฐานข้อมูล ก่อนนำไปกำหนดหรือใช้งานในไฟล์ excel สามารถทำได้ดังนี้
ยกตัวอย่างเช่น ข้อมูลวันที่ในคอลัมน์ที่ 11 เวลาแสดงใน excel เราต้องการแค่แสดงเป็นข้อความวันที่ภาษาไทย
สมมติเป็น 2019-07-13 เราต้องการให้แสดงเป็น 13 ก.ค. 2562 ก็สามารถทำได้ดังนี้
 
ตัวแปรข้อมูลจากฐานข้อมูลของเราทั้งหมดอยู่ใน array ชื่อ $arrayData
คอลัมน์ที่ 11 คือ ค่า key ที่ 10 ของ Array 
// ข้อมูลวันที่ในคอลัมน์ที่ 11 เราใช้ฟังก์ชั่น array_column ดึงมา
$arr_dateData = array_column($arrayData,10); // จะใช้งานข้อมูลที่คอลัมน์ 11
//จากนั้นใช้คำสั่ง array_walk เข้าไปจัดการค่าข้อมูลคอลัมน์ที่ 11 ที่เราได้มาในตัวแปร $arr_dateData
array_walk($arr_dateData, 'thaiDate', 10); // วนลูปอัพเดทวันที่ใน คอลัมน์ 11 เป็นภาษาไทย
วนลูปข้อมูล array โดยส่งค่าไปในฟังก์ชั่น thaiDate ที่เราสร้างขึ้นมา
// ฟังก์ชั่นอัพเดทข้อมูลวันที่ในฟิลด์ ที่ต้องการ
function thaiDate($item, $row, $column){
    global $arrayData;
    $arrayData[$row][$column] =  thai_date_short(strtotime($item)); // อัพเดทข้อมูลวันที่ด้วยฟังก์ชั่นที่ต้องการ
}

// ฟังก์ชั่นจัดการวันที่ภาษาไทย 
// ดูฟังก์ชั่นแสดงวันที่ ภาษาไทยเพิ่มเติมได้ที่ http://niik.in/459
$monthTH_brev = [null,'ม.ค.','ก.พ.','มี.ค.','เม.ย.','พ.ค.','มิ.ย.','ก.ค.','ส.ค.','ก.ย.','ต.ค.','พ.ย.','ธ.ค.'];
function thai_date_short($time){   // 19  ธ.ค. 2556
    global $monthTH_brev;
    $thai_date_return = date("j",$time);
    $thai_date_return.=" ".$monthTH_brev[date("n",$time)];
    $thai_date_return.= " ".(date("Y",$time)+543);
    return $thai_date_return;
}
จะได้รูปแบบภาพรวมการปรับแต่งข้อมูลเฉพาะคอลัมน์ที่ต้องการได้เป็นดังนี้
// ฟังก์ชั่นอัพเดทข้อมูลวันที่ในฟิลด์ ที่ต้องการ
function thaiDate($item, $row, $column){
    global $arrayData;
    $arrayData[$row][$column] =  thai_date_short(strtotime($item)); // อัพเดทข้อมูลวันที่ด้วยฟังก์ชั่นที่ต้องการ
}
// ฟังก์ชั่นจัดการวันที่ภาษาไทย
// ดูฟังก์ชั่นแสดงวันที่ ภาษาไทยเพิ่มเติมได้ที่ http://niik.in/459
$monthTH_brev = [null,'ม.ค.','ก.พ.','มี.ค.','เม.ย.','พ.ค.','มิ.ย.','ก.ค.','ส.ค.','ก.ย.','ต.ค.','พ.ย.','ธ.ค.'];
function thai_date_short($time){   // 19  ธ.ค. 2556
    global $monthTH_brev;
    $thai_date_return = date("j",$time);
    $thai_date_return.=" ".$monthTH_brev[date("n",$time)];
    $thai_date_return.= " ".(date("Y",$time)+543);
    return $thai_date_return;
}

// แสดงข้อมูลทั้งหมดของตาราง tbl_excel1
$sql = "
SELECT * FROM tbl_excel1
";
$result = $mysqli->query($sql);
if($result && $result->num_rows>0){  // คิวรี่ข้อมูลสำเร็จหรือไม่ และมีรายการข้อมูลหรือไม่
    $arrayData = $result->fetch_all();
    $totalRow = count($arrayData); // จำนวนแถวข้อมูลทั้งหมด
    $result->free(); // สามารถใช้ $result->close() หรือ $result->free_result() แทนได้
}

// ข้อมูลวันที่ในคอลัมน์ที่ 11 เราใช้ฟังก์ชั่น array_column ดึงมา
$arr_dateData = array_column($arrayData,10); // จะใช้งานข้อมูลที่คอลัมน์ 11
// จากนั้นใช้คำสั่ง array_walk เข้าไปจัดการค่าข้อมูลคอลัมน์ที่ 11 ที่เราได้มาในตัวแปร $arr_dateData
array_walk($arr_dateData, 'thaiDate', 10); // วนลูปอัพเดทวันที่ใน คอลัมน์ 11 เป็นภาษาไทย
ผลลัพธ์ที่ได้
 

 
นอกจากวิธีการใช้ array_column() ร่วมกับ array_walk() เพื่อเข้าไปจัดการค่าของข้อมูล array ข้างต้นแล้ว
เรายังสามารถใช้ array_walk_recursive() ซึ่งวิธีนี้ จะเป็นการวนลูปเข้าไปจัดการค่าแต่ละค่าของสมาชิก array 
ต่างจาก array_walk() ที่รองรับ array มากกว่า 1 มิติ ในขณะที่ array_walk() จะเหมาะกับ array มิติเดียวมากกว่า
อีกทั้ง การใช้งาน array_walk_recursive() นั่น เราไม่จำเป็นต้องใช้งาน array_column() ร่วมด้วย
    รูปแบบการใช้งาน ก็จะเป็นดังนี้
// ฟังก์ชั่นอัพเดทข้อมูลวันที่ในฟิลด์ ที่ต้องการ สังเกตว่ามีการอ้างอิงตัวแปรด้วย &$item
function thaiDate(&$item, $column){
    if($column==10){ // อัพเดทเฉพาะคอลัมน์ที่ 11 ที่ key array เท่ากับ 10
        // รายการข้อมูลในตำแหน่ง array นั่นๆ จะถูกอัพเดทเป็นค่าใหม่
        $item =  thai_date_short(strtotime($item));
    }
}
// ฟังก์ชั่นจัดการวันที่ภาษาไทย
// ดูฟังก์ชั่นแสดงวันที่ ภาษาไทยเพิ่มเติมได้ที่ http://niik.in/459
$monthTH_brev = [null,'ม.ค.','ก.พ.','มี.ค.','เม.ย.','พ.ค.','มิ.ย.','ก.ค.','ส.ค.','ก.ย.','ต.ค.','พ.ย.','ธ.ค.'];
function thai_date_short($time){   // 19  ธ.ค. 2556
    global $monthTH_brev;
    $thai_date_return = date("j",$time);
    $thai_date_return.=" ".$monthTH_brev[date("n",$time)];
    $thai_date_return.= " ".(date("Y",$time)+543);
    return $thai_date_return;
}
 
// แสดงข้อมูลทั้งหมดของตาราง tbl_excel1
$sql = "
SELECT * FROM tbl_excel1
";
$result = $mysqli->query($sql);
if($result && $result->num_rows>0){  // คิวรี่ข้อมูลสำเร็จหรือไม่ และมีรายการข้อมูลหรือไม่
    $arrayData = $result->fetch_all();
    $totalRow = count($arrayData); // จำนวนแถวข้อมูลทั้งหมด
    $result->free(); // สามารถใช้ $result->close() หรือ $result->free_result() แทนได้
}
 
// จัดการค่าของข้อมูลใน array กรณีนี้เหมาะกับ array มากกว่า 1 มิติ
array_walk_recursive($arrayData, 'thaiDate'); // วนลูปอัพเดทวันที่ใน คอลัมน์ 11 เป็นภาษาไทย
    ผลลัพธ์ของโค้ดด้านบน จะเป็นเช่นเดียวกับ กรณีการใช้งาน array_walk()


กด Like หรือ Share เป็นกำลังใจ ให้มีบทความใหม่ๆ เรื่อยๆ น่ะครับ



อ่านต่อที่บทความ









เนื้อหาที่เกี่ยวข้อง









URL สำหรับอ้างอิง





คำแนะนำ และการใช้งาน

สมาชิก กรุณา ล็อกอินเข้าระบบ เพื่อตั้งคำถามใหม่ หรือ ตอบคำถาม สมาชิกใหม่ สมัครสมาชิกได้ที่ สมัครสมาชิก


  • ถาม-ตอบ กรุณา ล็อกอินเข้าระบบ
  • เปลี่ยน


    ( หรือ เข้าใช้งานผ่าน Social Login )







เว็บไซต์ของเราให้บริการเนื้อหาบทความสำหรับนักพัฒนา โดยพึ่งพารายได้เล็กน้อยจากการแสดงโฆษณา โปรดสนับสนุนเว็บไซต์ของเราด้วยการปิดการใช้งานตัวปิดกั้นโฆษณา (Disable Ads Blocker) ขอบคุณครับ