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

15 July 2019 By Ninenik Narkdee
phpspreadsheet

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



เนื้อหาในตอนต่อไปนี้ เราจะมาเพิ่มเติมจากตอนที่แล้ว เกี่ยวกับ
การจัดการกับรูปแบบของข้อมูลที่แสดงในพิกัด 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 เป็นกำลังใจ ในการสร้างบทความใหม่ๆ น่ะครับ



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









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



Tags:: phpspreadsheet







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











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