การจัดการ cell ในไฟล์ Excel ด้วย PhpSpreadsheet เบื้องต้น ตอนที่ 2

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

phpspreadsheet

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

ดูแล้ว 9,082 ครั้ง

เนื้อหานี้ เราจะมาต่อจากตอนที่แล้ว ที่เราได้ทำความรู้จักกับ PhpSpreadsheet และการใช้งาน

เบื้องต้นไป โดยในตอนต่อไปนี้ เรามาดูเรื่องของการปรับแต่งค่าต่างๆ ทีมักจะพบบ่อยๆ

ในการจัดการ excel ไฟล์

เราจะใช้ไฟล์ตัวอย่างเริ่มต้นประกอบเนื้อหา

ไฟล์ test.php

<?php
// include composer autoload
require 'vendor/autoload.php';
 
// import the PhpSpreadsheet Class
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
 
$spreadsheet = new Spreadsheet(); // สร้าง speadsheet object
$sheet = $spreadsheet->getActiveSheet(); // กำหนดการทำงานที่่แผ่นงานปัจจุบัน
 
$sheet->setCellValue('A1', 'Hello World !'); // กำหนดค่าใน cell A1
$sheet->setCellValue('B1', 'ทดสอบข้อความภาษาไทย !'); // กำหนดค่าใน cell B1
 
// เขียนไฟล์ หรือสร้างไฟล์ excel จาก spreadsheet
$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>';
}

การจัดการข้อมูลใน cell

การกำหนดค่าให้กับพิกัด cell

ใน PhpSpreadsheet เราสามารถทำการกำหนดค่าต่างให้กับพิกัด cell โดยใช้คำสั่ง

1	`setCellValue('พิกัด cell ที่ต้องการ', ค่าของข้อมูล);`

ข้างต้นเราอ้างอิง แผ่นงานที่กำลังใช้งานอยู่ผ่านตัวแปร $sheet ที่มีค่าเท่ากับ $spreadsheet->getActiveSheet()

ดังนั้น การกำหนด

1 2	`$sheet->setCellValue('A1',` `'Hello World !');` `// กำหนดค่าใน cell A1` `$sheet->setCellValue('B1',` `'ทดสอบข้อความภาษาไทย !');` `// กำหนดค่าใน cell B1`

ก็คือการกำหนดค่าที่ตำแหน่งพิกัด cell ที่ "A1" ให้เท่ากับข้อความคำว่า "Hello World !"

และกำหนดค่าที่ตำแหน่งพิกัด cell ที่ "B1" ให้เท่ากับ "ทดสอบข้อความภาษาไทย !"

รูปแบบข้างต้น เราสามารถรวมการกำหนดให้อยู่ในรูปแบบ chain หรือเป็นลูกโซ่ต่อเนื้องกันได้ดังนี้

1 2	`$sheet->setCellValue('A1',` `'Hello World !')` `->setCellValue('B1',` `'ทดสอบข้อความภาษาไทย !');`

หรืออีกวิธี เราสามารถอ้างอิงไปที่พิกัด cell โดยใช้คำสั่ง getCell('พิกัด cell ที่ต้องการ') และกำหนดค่าด้วยคำสั่ง setValue('ค่าของข้อมูล')

แต่รูปแบบนี้ ไม่รองรับการใช้งานแบบ chain

1 2	`$sheet->getCell('A1')->setValue('Hello World !');` `$sheet->getCell('B1')->setValue('ทดสอบข้อความภาษาไทย !');`

ในการใช้งานคำสั่ง getCell() นั้น โดยค่าเริ่มต้น จะเป็นสร้างพิกัด cell นั้นๆ ขึ้นมาใหม่ถ้าไม่มีการกำหนดไว้ก่อนหน้า เราสามารถใช้เงื่อนไข

กรณีต้องการให้กำหนดค่า ถ้ามีพิกัด cell นั้นอยู่แล้วเท่านั้น ถ้าไม่มีไม่ต้องทำการกำหนดค่าเข้าไป เราจะกำหนด ค่า argument ตัวที่สองเป็น false

เข้าไป เป็นดังนี้

$sheet->getCell('A1')->setValue('Hello World !');
if($sheet->getCell('B1',false)){ // ถ้าไม่มีพิกัดนี้อยู่ก่อนหน้า ไม่ต้องสร้าง 
    $sheet->getCell('B1')->setValue('ทดสอบข้อความภาษาไทย !');
}

ดังนั้น กรณีข้างต้นคือที่พิกัด B1 จะไม่การสร้างและไม่มีการกำหนดค่าเข้าไป

การกำหนดค่าให้กับพิกัด cell โดยอ้างอิงพิก้ด cell

เราสามารถอ้างอิงตัวแปร สำหรับกำหนดพิกัด cell เพื่อกำหนดค่า หรือเรียกดูข้อมูลได้ เช่น

$cellA1 = $sheet->getCell('A1');
$cellA1->setValue('Hello World !');
$cellA1_value = $cellA1->getValue(); // ดึงข้อมูลจากพิกัด A1
$cellA1_coordinate = $cellA1->getCoordinate(); // ดึงพิกัด cell จะได้ 'A1'
$cellB1 = $sheet->getCell('B1');
$cellB1->setValue($cellA1_value); // นำค่าจาก A1 มาใส่ในพิกัด B1

การกำหนดค่าให้กับพิกัด cell ด้วยข้อมูล Array

เราสามารถกำหนดช่วงของ cell โดยใช้ข้อมูล array กรณีนี้เหมาะสำหรับการใช้งานร่วมกับฐานข้อมูล

// array ข้อมูล
$arrayData = [
    [NULL, 2010, 2011, 2012],
    ['Q1',   12,   15,   21],
    ['Q2',   56,   73,   86],
    ['Q3',   52,   61,   69],
    ['Q4',   30,   32,    0],
];
$sheet->fromArray(
    $arrayData,  // ตัวแปร array ข้อมูล
    NULL,        // ค่าข้อมูลที่ตรงตามค่านี้ จะไม่ถูกำหนด 
    'C3'         // จุดพิกัดเริ่มต้น ที่ใช้งานข้อมูล เริ่มทึ่มุมบนซ้าย  หากไม่กำหนดจะเป็น "A1" ค่าเริ่มต้น
);

ตัวอย่างผลลัพธ์ที่ได้

จะเห็นค่าที่ตำแหน่ง C3 เป็นค่า NULL จะไม่มีการแสดงข้อมูล เช่นเดียวกับที่ตำแหน่ง ที่มีค่าเท่ากับ 0

หากเราไม่กำหนด สองค่าหลัง ตำแหน่งเริ่มต้นของข้อมูลจะเริ่มที่พิกัด A1 และค่าข้อมูลที่เป็น NULL และ 0 จะไม่แสดง

ตามรูปแบบการใช้งานด้านล่าง

// ไม่กำหนดจดพิกัดเริ่มต้น
$sheet->fromArray(
    $arrayData,  // ตัวแปร array ข้อมูล
);

สำหรับข้อมูล array ข้างต้น จะเป็นรูปแบบ array ข้อมูล 2 มิติ กรณีข้อมูลที่เป็นมิติเดียว ข้อมูลจะแสดงในรูปแบบแถวข้อมูล

เรียงจากซ้ายไปขวา ดังนี้

// array ข้อมูล 1 มิติ
$rowArray = ['Value1', 'Value2', 'Value3', 'Value4'];
$sheet->fromArray(
    $rowArray,  // ตัวแปร array ข้อมูล
    NULL,        // ค่าข้อมูลที่ตรงตามค่านี้ จะไม่ถูกำหนด
    'C3'         // จุดพิกัดเริ่มต้น ที่ใช้งานข้อมูล เริ่มทึ่มุมบนซ้าย  หากไม่กำหนดจะเป็น "A1" ค่าเริ่มต้น
);

ตัวอย่างผลลัพธ์ที่ได้

รูปแบบ array ข้อมูล 1 มิติข้างต้น เราสามารถแก้ไขเป็นแบบ 2 มิติ เพื่อให้แสดงข้อมูลแบบ คอลัมน์เรียงบนลงล่างเป็นดังนี้

1	`$columnArray` `= [['Value1'],[` `'Value2'],[` `'Value3'], ['Value4']];`

หรือใช้ array ฟังก์ชั่นจัดการ โดยใช้คำส่ัง array_chunk() เพื่อแยกข้อมูล array เป็น 2 มิติ ดังนี้

$rowArray = ['Value1', 'Value2', 'Value3', 'Value4'];
$columnArray = array_chunk($rowArray, 1); // แยกเป็น 2 มิติ ทุก 1 รายการ
$sheet->fromArray(
    $columnArray,  // ตัวแปร array ข้อมูล
    NULL,        // ค่าข้อมูลที่ตรงตามค่านี้ จะไม่ถูกำหนด
    'C3'         // จุดพิกัดเริ่มต้น ที่ใช้งานข้อมูล เริ่มทึ่มุมบนซ้าย  หากไม่กำหนดจะเป็น "A1" ค่าเริ่มต้น
);

ตัวอย่างผลลัพธ์ที่ได้

การกำหนดค่าให้กับพิกัด cell โดยอ้างอิงตำแหน่ง column และ row

เราสามารถกำหนดค่า หรือเรียกดูข้อมูลในตำแหน่งพิกัด cell โดยใช้คำสั่ง getCellByColumnAndRow() และ

setCellValueByColumnAndRow() ตามลำดับ ดังนี้

$sheet->setCellValueByColumnAndRow(1, 5, 'PhpSpreadsheet');
// กำหนดค่าที่พิกัด A1 
$sheet->getCellByColumnAndRow(2, 5)->getValue();
// ดึงข้อมูล B5

ตำแหน่งคอลัมน์แรก คือ 1 จะแทนคอลัมน์ A

ข้อมูลใน Excel

MS Excel รองรับข้อมูลพื้นฐานด้วยกัน 7 ประเภทได้แก่

- ข้อความ string

- ตัวเลข number

- boolean

- null

- สูตร formula

- error

- Inline string

โดยค่าเริ่มต้นแล้ว เมื่อเรามีการกำหนดค่าให้กับพิกัด cell ด้วยคำสั่ง setCellValue() หรือ setValue() PhpSpreadsheet จะทำการปรับ

ชนิดข้อมูลของ PHP อย่าง NULL, Boolean, Float หรือ Integer หรือ ข้อความ String มาให้อยู่ในรูปแบบข้อมูลที่ MS Excel รองรับ

เช่น ข้อความที่เป็นตัวเลขจะถูกแปลงมาเป็นตัวเลข หรือ ข้อความที่ขึ้นต้นด้วยเครื่องหมาย = จะแปลงเป็นสูตร formula

ข้อความที่ไม่ใช่ตัวเลข หรือไม่ได้ขึ้นต้นด้วยเครื่องหมาย = จะคงรูปแบบข้อความเดิมไว้

ดูตัวอย่างการกำหนดค่าข้อมูล

$rowArray = ['100',52.65,'0804423124',30,'1/12/2019','3/4','5%', 'one',null,0,'0', true,'=A1'];
$columnArray = array_chunk($rowArray, 1); // แยกเป็น 2 มิติ ทุก 1 รายการ
$sheet->fromArray(
    $columnArray  // ตัวแปร array ข้อมูล
);

ผลลัพธ์ที่ได้ จะเป็นไปในลักษณะที่เราอธิบายไปข้างต้น

จะเห็นว่าการกำหนดค่าข้อมูล โดยจำลองใส่ข้อมูลชนิดต่างๆ ไม่ว่าจะเป็น ข้อความ, ตัวเลข, ข้อความตัวเลข, เลขทศนิยม, ค่า boolean,

ค่า NULL, เลข 0, กับ ข้อความเลข 0 หรือ สูตร Excel จะเห็นว่า ข้อมูลที่แสดงใน excel ค่าเริ่มต้น จะอยู่ในรูปแบบข้อความพื้นฐาน 7 ประเภท

ค่าทั้งหมดที่แสดงข้างต้นนี้ เป็นข้อมูลที่เรายังไม่มีการจัดรูปแบบข้อมูลหรือที่เรียกว่ายังไม่ได้กำหนด FORMAT CELL แต่อย่างไร

การใช้งาน Value Binder ใน PhpSpreadsheet

โดยค่าเริ่มต้น ใน PhpSpeadsheet จะมีการใช้งาน Value Binder ภายในอยู่แล้วเป็นค่าเริ่มต้น ซึ่ง Value Binder ก็คือการจัดรูปแบบข้อมูล

จากชนิดข้อมูลที่กำหนดเข้าไปใน cell อย่างที่กล่าวผ่านมาแล้วด้านบน เรากำหนดชนิดของข้อมูลที่แตกต่างลงไปใน cell ยกตัวอย่างที่เห็นชัด

ก็เช่นค่าตัวเลข 0 ที่ตัว value binder ทำการแปลงข้อมูลเป็นหรือ NULL แทนที่จะเป็นตัวเลข 0 ทั้งนี้ก็เพราะมีการใช้งาน DefaultValueBinder

หรือเราสามารถกำหนดลงไปในโค้ดเพื่อให้เห็นการใช้งานเป็นดังนี้

<?php
// include composer autoload
require 'vendor/autoload.php';
 
// import the PhpSpreadsheet Class
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
 
// Set value binder ปกติ หากใช้ค่า DefaultValueBinder จะกำหนดตรงนี้หรือไม่ ก็ได้ ได้ผลลัพธ์เหมือนกัน
\PhpOffice\PhpSpreadsheet\Cell\Cell::setValueBinder( new \PhpOffice\PhpSpreadsheet\Cell\DefaultValueBinder() );
 
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
 
$rowArray = ['100',52.65,'0804423124',30,'1/12/2019','3/4','5%', 'one',null,0,'0', true,'=A1'];
$columnArray = array_chunk($rowArray, 1); // แยกเป็น 2 มิติ ทุก 1 รายการ
$sheet->fromArray(
    $columnArray  // ตัวแปร array ข้อมูล
);
 
$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>';
}

ผลลัพธ์ที่ได้ก็จะเหมือนกับ กรณีไม่ได้กำหนดโค้ดการใช้งาน value binder

นอกจากการใช้งาน value binder ที่เป็นค่า default หรือค่าเริ่มต้นแล้ว เรายังสามารถใช้ value binder ด้วย class AdvancedValueBinder

เพื่อจัดการกับข้อมูลได้อย่างง่าย ซึ่งการใช้งาน class นี้จะทำการแปลงชนิดหรือรูปแบบชองข้อมูลได้มากกว่า เช่น แปลงเปอร์เซ็นให้อยู่ในรูปแบบ

ตัวเลขทางวิทยาศาสตร์ แปลงข้อความวันที่เป็นรูปแบบวันที่ที่ถูกต้อง เป็นต้น โดยเราสามารถกำหนดการใช้งาน ได้ดังนี้

1 2	`//เปลี่ยนจาก DefaultValueBinder() เป็น AdvancedValueBinder()` `\PhpOffice\PhpSpreadsheet\Cell\Cell::setValueBinder(` `new` `\PhpOffice\PhpSpreadsheet\Cell\AdvancedValueBinder() );`

ผลลัพธ์ที่ได้

จะเห็นว่าข้อมูลที่เพิ่มเข้าไปใน cell มีการเปลี่ยนแปลงหรือก็คือมีการแปลงค่าเป็นรูปแบบที่ถูกต้องมากขึ้น อย่างวันที่ ก็มีการจัดรูปแบบข้อมูลใหม่

อย่างเห็นได้ชัด รวมถึงตัวเลขเศษส่วน "3/4" จากรูปแบบเดิม จะเป็นแค่การแปลงเป็นข้อความตัวเลขเศษส่วนธรรมดา แต่เมื่อใช้รูปแบบ value binder

ใหม่ ตัวเลขเศษส่วนก็ถูกแปลงเป็นค่าเลขทศนิยม ถึงแม้เราจะเห็นว่ายังแสดงเป็น "3/4" แต่ค่าด้านในมีการเปลี่ยนแปลงแล้ว ทั้งนี้ก็เพราะตัว cell มีการ

จัดรูปแบบหรือที่เรียกว่ามีการกำหนด mask การแสดงข้อมูลเพิ่มเข้ามาด้วยนั่นเอง

การใช้งาน Value Binder นั่น หากเราประยุกต์ใช้ร่วมกับข้อมูลที่ได้จากฐานข้อมูล และใช้งานอย่างถูกวิธี ก็จะช่วยให้เราสามารถแสดงข้อมูลใน

excel ได้สะดวกและง่ายขึ้น โดยไม่ต้องไปกำหนด format หรือรูปแบบข้อมูลในแต่ละ cell เอง ซึ่งวิธีแบบนี้ทำให้เราจัดการข้อมูลได้ง่ายขึ้นนั่นเอง

อย่างไรก็ตาม การใช้งาน Value Binder ก็ใช่ว่าจะทำให้เราได้รูปแบบที่ต้องการเสมอไป อย่างเช่น กรณีที่เป็นข้อความเบอร์โทร ที่ขึ้นต้นด้วย

'0804423124' นั่น กลับถูกแปลงเป็น ตัวเลขที่ไม่มีเลข 0 ด้านหน้า ซึ่งเป็นรูปแบบที่ไม่ถูกต้อง และไม่ใช่รูปแบบที่เราต้องการ

เราจะได้ปรับแต่ง ศึกษาและทำความเข้าใจ ในการจัดการรูปแบบข้อมูลเพิ่มเติมในตอนต่อไป รอติดตาม

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

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

15 Jul
2019

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

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

12 Jul
2019

ออกรายงานเป็น Excel ไฟล์ ด้วย PhpSpreadsheet เบื้องต้น ตอนที่ 1 อ่าน 17,609
เนื้อหานี้จะเป็นเริ่มต้นการใช้งาน PhpSpreadsheet สำหรับการออกรายงานหรือสร้าง
กำลังอ่านเนื้อหานี้อยู่

13 Jul
2019

การจัดการ cell ในไฟล์ Excel ด้วย PhpSpreadsheet เบื้องต้น ตอนที่ 2 อ่าน 9,082
เนื้อหานี้ เราจะมาต่อจากตอนที่แล้ว ที่เราได้ทำความรู้จักกับ PhpSpreadsheet แล
15 Jul
2019

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

Tags:: phpspreadsheet

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

ขอบคุณทุกการสนับสนุน

สนับสนุนเว็บไซต์เรา จ่ายด้วย Truemoney Wallet

20 บาท 50 บาท 100 บาท 300 บาท หรือ ระบุจำนวน