จัดรูปแบบการแสดงใน Excel ด้วย PhpSpreadsheet เบื้องต้น ตอนที่ 4

เขียนเมื่อ 4 ปีก่อน โดย Ninenik Narkdee
phpspreadsheet ตวามสูงแถว ความกว้างคอลัมน์

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

ดูแล้ว 7,442 ครั้ง


เนื้อหาตอนต่อไปนี้ เราจะมาดูในเรื่องของการจัดการกับรูปแบบ
การแสดงข้อมูลใน Excel เช่น การจัดตำแหน่ง การกำหนดเส้นขอบ การสร้าง
เส้นขอบ การจัดหน้ากระดาษ เหล่านี้เป็นต้น 
    ดังนั้น เพื่อให้เนื้อหา เฉพาะเจาะจงไปแค่รูปแบบหรือ style ในที่นี้เราจึงไม่สนใจใน
รายละเอียดของข้อมูลเท่าไหร่นัก
    เราจะใช้ข้อมูลจังหวัดในประเทศไทย จากตารางข้อมูลในฐานข้อมูลทดสอบ ที่ http://niik.in/que_2398_6277
 
ไฟล์ demo.php
<?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 province_id,province_name,province_name_eng FROM tbl_provinces
";
$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
// กำหนดหัวข้อคอลัมน์
$columnName = ['ID','Name','Name Eng'];
$sheet->fromArray($columnName) // array ข้อมูลหัวข้อคอลัมน์
->fromArray(
     $arrayData,  // ตัวแปร array ข้อมูล
     NULL,        // ค่าข้อมูลที่ตรงตามค่านี้ จะไม่ถูกำหนด
     'A2'         // จุดพิกัดเริ่มต้น ที่ใช้งานข้อมูล เริ่มทึ่มุมบนซ้าย  หากไม่กำหนดจะเป็น "A1" ค่าเริ่มต้น
 );

/// ส่วนของการจัดรูปแบบการแสดง กำหนดต่อจากบรรทัดนี้

// ส่วนของการสร้างไฟล์ excel
$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>';
}
ตัวอย่างผลลัพธ์บางส่วน
 
 

 

จัดรูปแบบการแสดงใน Excel

    การกำหนดความกว้างของคอลัมน์

    ใน PhpSpreadsheet การกำหนดเกี่ยวกับขนาดต่างๆ ไม่ได้เหมือนหรือได้ผลลัพธ์ตามรูปแบบของ Excel ตรงๆ แต่ก็พอจะเป็นค่าประมาณ
ที่เราสามารถปรับแต่งให้เหมาะสมได้ หน่วยที่ใช้จะเรียกว่า char unit   โดยขนาดความกว้างแบบเต็ม รวม padding แล้ว จะเท่ากับ 64 pixels
หรือประมาณ 9.140625 char (character units)
    รูปแบบคำสั่ง กำหนดความกว้างให้กับคอลัมน์ที่ต้องการ คือ
$sheet->getColumnDimension('A')->setWidth(12);
// $sheet->getColumnDimension('A')->setWidth(9.140625); // เท่ากับ 64 pixels หรือ 1 char = 7.001709401709402 pixels
    เรากำหนดคอลัมน์ที่ต้องการกำหนดขนาดความกว้างด้วยตัวอักษรภาษาอังกฤษ เช่น A ตามตัวอย่างด้านบน   ส่วนค่าที่กำหนดอย่าง
12 และ หรือ 9.140625 คือค่าในหน่วย char units  เราสามารถใช้ค่า 7.001709401709402 pixel ไปหาค่าที่ต้องการกำหนดเป็น char unit
ได้เช่น เราอยากได้ความกว้างสักประมาณ 100 px จะต้องกำหนด char เท่าไหร่ ก็สามารถหาได้จาก 
 
    100 / 7.001709401709402 = 14.2822265625
 
    ลองทดสอบค่าดู ด้วยคำสั่ง 
$sheet->getColumnDimension('A')->setWidth(14.2822265625);
    ผลลัพธ์ที่ได้
 

 
 
    เราสามารถกำหนดความกว้างพร้อมกันหลายคอลัมน์ โดยใช้การวนลูป array ดังนี้
foreach(['A','C'] as $column) { // หากต้องการกำหนดคอลัมน์ใดๆ ก็เพิ่มเข้าไปใน array
    $sheet->getColumnDimension($column)->setWidth(14.2822265625);
}
    หรือกรณีเป็นคอลัมน์ติดๆ กันต่อเนื่อง ก็อาจจะใช้เป็น
foreach(range('A','C') as $column) { // เป็นการสร้าง array จะได้ค่า ['A','B','C'] แล้ววนลูปกำหนดค่า
    $sheet->getColumnDimension($column)->setWidth(14.2822265625);
}
    นอกจากนี้ เรายังสามารถกำหนดให้ขนาดของคอลัมน์ขยายอัตโนมัติ โดยยึดจากความกว้างสูงสุดในคอลัมน์นั้นในการใช้งาน
โดยสามารถทำได้ด้วยการใช้คำสั่ง
$sheet->getColumnDimension('A')->setAutoSize(true);
    เช่นกัน เราสามารถใช้การวนลูปเพื่อกำหนดให้กับหลายคอลัมน์พร้อมๆ กันได้ ดังนี้
foreach(range('A','C') as $column) {
    $sheet->getColumnDimension($column)->setAutoSize(true);
}
    ผลลัพธ์ที่ได้
 

 
 
    หรือถ้าเราต้องการให้บางคอลัมน์ กว้างแบบกำหนดเอง ส่วนบางคอลัมน์กว้างอัตโนมัติ ก็อาจจะใช้รูปแบบดังนี้ได้
foreach(range('A','C') as $column) {
    if($column=='A'){
        $sheet->getColumnDimension($column)->setWidth(9.140625);
    }else{
        $sheet->getColumnDimension($column)->setAutoSize(true);
    }
}
    นอกจากเราสามารถเลือกที่จะกำหนดความกว้างเฉพาะคอลัมน์ที่เราต้องการแล้ว เรายังสามารถเลือกกำหนดความกว้างค่าเริ่มต้นให้กับ
คอลัมน์ทั้งหมด ได้ดังนี้
$sheet->getDefaultColumnDimension()->setWidth(14.2822265625);
    คำสั่งข้างต้น จะเป็นการกำหนดทุกคอลัมน์ใน excel มีความกว้างประมาณ 100 px เท่ากันหมด คำสั่งนี้ เราไม่จำเป็นต้องกำหนดหรือระบุชื่อคอลัมน์
จะเหมาะสำหรับกรณีตารางข้อมูลที่เราต้องการให้แสดงทุกคอลัมน์มีขนาดเท่ากัน 
    หรือเราจะใช้งานร่วมกับ เช่นให้ทกคอลัมน์เท่ากันหมดที่ 100 px แต่ให้คอลัมน์ A เท่ากับ 64 px เราก็สามารถประยุกต์ร่วมกันได้ดังนี้
$sheet->getDefaultColumnDimension()->setWidth(14.2822265625); // ประมาณ 100 px
$sheet->getColumnDimension('A')->setWidth(9.140625);  // ประมาณ 64 px
 

    การกำหนดความสูงของแถวรายการ

    สำหรับความสูงของแถวรายการ excel จะใช้หน่วย point (pt) โดยค่าเริ่มต้น จะอยู่ 12.75 pt หรือเท่ากับ 17 pixels   ค่าที่สามารถกำหนดความ
สูงของแถวรายการจะอยู่ที่ 0 - 409 pt (0 - 545.333333 pixels) ซึ่งถ้าเรากำหนดเป็น 0 นั่นหมายถึงเป็นการซ่อนแถวนั้นๆ ไป
เราสามารถประมาณค่าในหน่วย pixel โดยใช้สัดส่วน 1 point  =  1.333333 pixel โดยสมมติว่าเราต้องการความสูงที่ประมาณ 50 px 
ก็จะใช้การคำนวณเป็นดังนี้
 
    50 / 1.333333 = 37.50000937500234
 
    ลองมาดูคำสั่งการกำหนดความสูงของแถว โดยเราจะลองกำหนดให้กับห้วข้อรายการ เป็นดังนี้
$sheet->getRowDimension('1')->setRowHeight(37.50000937500234); // ประมาณ 50 px
    ผลลัพธ์ที่ได้
 

 
 
    เช่นเดียวกันกับรูปแบบการกำหนดคอลัมน์ เราสามารถที่กำหนดหลายๆ แถวพร้อมกัน ในรูปแบบดังนี้
foreach([1,3,5] as $row) { // หากต้องการกำหนดแถวใดๆ ก็เพิ่มเข้าไปใน array
    $sheet->getRowDimension($row)->setRowHeight(37.50000937500234); // ประมาณ 50 px
}
    หรือกรณีเป็นหลายแถวติดๆ กันต่อเนื่อง ก็อาจจะใช้เป็น
foreach(range(1,5) as $row) { // เป็นการสร้าง array จะได้ค่า [1,2,3,4,5] แล้ววนลูปกำหนดค่า
    $sheet->getRowDimension($row)->setRowHeight(37.50000937500234); // ประมาณ 50 px
}
    เราสามารถกำหนดค่าเริ่มความสูงของทุกๆ แถวรายการด้วยคำสั่ง
$sheet->getDefaultRowDimension()->setRowHeight(15);
 
 

    การซ่อนหรือแสดง แถว - คอลัมน์

    เราสามารถใช้คำสั่ง setVisible() สำหรับซ่อนหรือแสดง แถว - คอลัมน์ โดยกำหนดค่า false ถ้าต้องการซ่อน และกำหนด true หลังจาก
ซ่อนด้วยคำสั่ง setVisible(false) เพื่อแสดง โดยรูปแบบการใช้งานจะคล้ายกันสำหรับแถว และคอลัมน์ เพียงแค่อ้างอิงการใช้งานตามประเภท
ของรายการนั้น 
// กรณีคอลัมน์
$sheet->getColumnDimension('C')->setVisible(true);
$sheet->getColumnDimension('D')->setVisible(false);

// กรณีแถวรายการ
$sheet->getRowDimension('10')->setVisible(true);
$sheet->getRowDimension('11')->setVisible(false);
 
 

    การรวมและยกเลิกรวม cell

    เราสามารถรวม cell และ ยกเลิกการรวม cell โดยใช้คำสั่ง mergeCells() และ unmergeCells() ตามลำดับ   มาดูตัวอย่าง สมมติเราต้องการ
แสดงจำนวนจังหวัดทั้งในแถวต่อจากแถวข้อมูลสุดท้าย ในที่นี้ ก็คือ ตำแหน่ง C79
    ตำแหน่ง C79 มาจาก จำนวนแถวหัวข้อ(1) + จำนวนรายการ(77) + แถวลำดับถัดไป(1) = 79
    ดูตัวอย่างคำสั่ง
$rowSumNo = $totalRow+2; // แถวที่เราจะจัดการ
$sheet ->setCellValue('A'.$rowSumNo, 'จำนวน');
$sheet ->setCellValue('B'.$rowSumNo, 'ทั้งหมด');
$sheet ->mergeCells('A'.$rowSumNo.':B'.$rowSumNo); // รวม cel A79:B79
$sheet ->setCellValue('C'.$rowSumNo, '=MAX(A2:A78)'); // ใช้สูตรหาค่าสูงสุด ซึ่งก็คือ 77
    ผลลัพธ์ที่ได้ 
 

 
 
    จะเห็นว่าข้อความคำว่า "ทั้งหมด" จะไม่แสดง แต่จะใช้ค่าจาก พิกัด cell แรกทั้งหมด ดังนั้น ถ้าเราต้องการข้อความว่า "จำนวนทั้งหมด"
ใน cell ที่มีการรวมกัน เราต้องกำหนดค่าไว้ใน พิกัดแรก (ในที่นี้ก็คือ A79) ดังนี้
$rowSumNo = $totalRow+2; // แถวที่เราจะจัดการ
$sheet ->setCellValue('A'.$rowSumNo, 'จำนวนทั้งหมด');
$sheet ->mergeCells('A'.$rowSumNo.':B'.$rowSumNo); // รวม cel A79:B79
$sheet ->setCellValue('C'.$rowSumNo, '=MAX(A2:A78)'); // ใช้สูตรหาค่าสูงสุด ซึ่งก็คือ 77
    จะได้เป็น
 

 
 
    เรามาลองกรณีสมมติ ว่าในคอลัมน์ A79 และ B79 มีข้อมูลอยู่แล้ว และเราต้องการเอาค่าจากพิกัดทั้งสอง มาใส่ในพิกัดข้อมูลที่รวม cell แล้ว
แนวทางจะเป็นดังนี้
$rowSumNo = $totalRow+2; // แถวที่เราจะจัดการ
// สมมติมีข้อมูลอยู่แล้ว 
$sheet ->setCellValue('A'.$rowSumNo, 'จำนวน');
$sheet ->setCellValue('B'.$rowSumNo, 'ทั้งหมด');
// ดึงค่ามาใช้
$cellA79_value = $sheet->getCell('A'.$rowSumNo)->getValue(); 
$cellB79_value = $sheet->getCell('B'.$rowSumNo)->getValue(); 
$mergeValue = $cellA79_value.$cellB79_value;
// กำหนดค่าให้กับพิกัดแรก ที่มีการรวม cell
$sheet ->setCellValue('A'.$rowSumNo, $mergeValue);
$sheet ->mergeCells('A'.$rowSumNo.':B'.$rowSumNo); // รวม cel A79:B79
$sheet ->setCellValue('C'.$rowSumNo, '=MAX(A2:A78)'); // ใช้สูตรหาค่าสูงสุด ซึ่งก็คือ 77
    ผลลัพธ์ที่ได้ จะคล้ายกับตัวก่อนหน้า ที่ต่างไปคือเราเพิ่มช่องว่างระหว่างค่าข้อมูล เพื่อให้เห็นความแตกต่าง
 

 
 
    วิธีนี้เหมาะสำหรับรวมพิกัด cell ที่มีค่าเดิมอยู่แล้ว ให้อยู่ด้วยกัน เช่น สมมติ มีคอลัมน์ชื่อ และคอลัมน์นามสกุล แต่เราต้องการรวมชื่อและนามสกุล
ให้อยู่ในคอลัมน์เดียวกัน ก็สามารถใช้วิธ๊นี้ได้
$lastRow = $totalRow +1;
for($i=1; $i<=$lastRow; $i++){
    $coorCellB = 'B'.$i;
    $coorCellC = 'C'.$i;
    // ดึงค่ามาใช้
    $columnB_value = $sheet->getCell($coorCellB)->getValue();
    $columnC_value = $sheet->getCell($coorCellC)->getValue();
    $mergeValue = $columnB_value.' - '.$columnC_value;
    $sheet ->setCellValue('B'.$i, $mergeValue);
    $sheet ->mergeCells('B'.$i.':C'.$i); // รวม cel 
}
    ผลลัพธ์ที่ได้
 

 
 
    และอีกเทคนิคหนึ่งก็คือ ใช้สูตรรวมข้อมูลไว้ในคอลัมน์ใหม่ แล้วใช้การซ่อนคอลัมน์เดิมไป ดังนี้
$lastRow = $totalRow +1;
for($i=1; $i<=$lastRow; $i++){
    $coorCellB = 'B'.$i;
    $coorCellC = 'C'.$i;
    // ใช้สูตรรวมข้อความไว้ใน คอลัมน์ D 
    $sheet ->setCellValue('D'.$i, '=CONCATENATE('.$coorCellB.'," - ",'.$coorCellC.')');
}
// จากนั้นซ่อนคอลัมน์ที่ไม่ใช้งานไป
$sheet->getColumnDimension('B')->setVisible(false);
$sheet->getColumnDimension('C')->setVisible(false);
    ผลลัพธ์ที่ได้
 

 
 
    เราจะมาดูกันต่อในตอนหน้า เกี่ยวกับการกำหนด style ให้กับพิกัด cell


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



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









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









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





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

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


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


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







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