เนื้อหาตอนต่อไปนี้ เราจะมาดูในเรื่องของการจัดการกับรูปแบบ
การแสดงข้อมูลใน Excel เช่น การจัดตำแหน่ง การกำหนดเส้นขอบ การสร้าง
เส้นขอบ การจัดหน้ากระดาษ เหล่านี้เป็นต้น
ดังนั้น เพื่อให้เนื้อหา เฉพาะเจาะจงไปแค่รูปแบบหรือ style ในที่นี้เราจึงไม่สนใจใน
รายละเอียดของข้อมูลเท่าไหร่นัก
เราจะใช้ข้อมูลจังหวัดในประเทศไทย จากตารางข้อมูลในฐานข้อมูลทดสอบ ที่ http://niik.in/que_2398_6277
ไฟล์ demo.php
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 | <?php// โค้ดไฟล์ dbconnect.php ดูได้ที่ http://niik.in/que_2398_5642require_once("dbconnect.php");// include composer autoloadrequire 'vendor/autoload.php';// import the PhpSpreadsheet Classuse 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); // สร้าง excelif(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)
รูปแบบคำสั่ง กำหนดความกว้างให้กับคอลัมน์ที่ต้องการ คือ
1 2 | $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
ลองทดสอบค่าดู ด้วยคำสั่ง
1 | $sheet->getColumnDimension('A')->setWidth(14.2822265625); |
ผลลัพธ์ที่ได้
เราสามารถกำหนดความกว้างพร้อมกันหลายคอลัมน์ โดยใช้การวนลูป array ดังนี้
1 2 3 | foreach(['A','C'] as $column) { // หากต้องการกำหนดคอลัมน์ใดๆ ก็เพิ่มเข้าไปใน array $sheet->getColumnDimension($column)->setWidth(14.2822265625);} |
หรือกรณีเป็นคอลัมน์ติดๆ กันต่อเนื่อง ก็อาจจะใช้เป็น
1 2 3 | foreach(range('A','C') as $column) { // เป็นการสร้าง array จะได้ค่า ['A','B','C'] แล้ววนลูปกำหนดค่า $sheet->getColumnDimension($column)->setWidth(14.2822265625);} |
นอกจากนี้ เรายังสามารถกำหนดให้ขนาดของคอลัมน์ขยายอัตโนมัติ โดยยึดจากความกว้างสูงสุดในคอลัมน์นั้นในการใช้งาน
โดยสามารถทำได้ด้วยการใช้คำสั่ง
1 | $sheet->getColumnDimension('A')->setAutoSize(true); |
เช่นกัน เราสามารถใช้การวนลูปเพื่อกำหนดให้กับหลายคอลัมน์พร้อมๆ กันได้ ดังนี้
1 2 3 | foreach(range('A','C') as $column) { $sheet->getColumnDimension($column)->setAutoSize(true);} |
ผลลัพธ์ที่ได้
หรือถ้าเราต้องการให้บางคอลัมน์ กว้างแบบกำหนดเอง ส่วนบางคอลัมน์กว้างอัตโนมัติ ก็อาจจะใช้รูปแบบดังนี้ได้
1 2 3 4 5 6 7 | foreach(range('A','C') as $column) { if($column=='A'){ $sheet->getColumnDimension($column)->setWidth(9.140625); }else{ $sheet->getColumnDimension($column)->setAutoSize(true); }} |
นอกจากเราสามารถเลือกที่จะกำหนดความกว้างเฉพาะคอลัมน์ที่เราต้องการแล้ว เรายังสามารถเลือกกำหนดความกว้างค่าเริ่มต้นให้กับ
คอลัมน์ทั้งหมด ได้ดังนี้
1 | $sheet->getDefaultColumnDimension()->setWidth(14.2822265625); |
คำสั่งข้างต้น จะเป็นการกำหนดทุกคอลัมน์ใน excel มีความกว้างประมาณ 100 px เท่ากันหมด คำสั่งนี้ เราไม่จำเป็นต้องกำหนดหรือระบุชื่อคอลัมน์
จะเหมาะสำหรับกรณีตารางข้อมูลที่เราต้องการให้แสดงทุกคอลัมน์มีขนาดเท่ากัน
หรือเราจะใช้งานร่วมกับ เช่นให้ทกคอลัมน์เท่ากันหมดที่ 100 px แต่ให้คอลัมน์ A เท่ากับ 64 px เราก็สามารถประยุกต์ร่วมกันได้ดังนี้
1 2 | $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
ลองมาดูคำสั่งการกำหนดความสูงของแถว โดยเราจะลองกำหนดให้กับห้วข้อรายการ เป็นดังนี้
1 | $sheet->getRowDimension('1')->setRowHeight(37.50000937500234); // ประมาณ 50 px |
ผลลัพธ์ที่ได้
เช่นเดียวกันกับรูปแบบการกำหนดคอลัมน์ เราสามารถที่กำหนดหลายๆ แถวพร้อมกัน ในรูปแบบดังนี้
1 2 3 | foreach([1,3,5] as $row) { // หากต้องการกำหนดแถวใดๆ ก็เพิ่มเข้าไปใน array $sheet->getRowDimension($row)->setRowHeight(37.50000937500234); // ประมาณ 50 px} |
หรือกรณีเป็นหลายแถวติดๆ กันต่อเนื่อง ก็อาจจะใช้เป็น
1 2 3 | foreach(range(1,5) as $row) { // เป็นการสร้าง array จะได้ค่า [1,2,3,4,5] แล้ววนลูปกำหนดค่า $sheet->getRowDimension($row)->setRowHeight(37.50000937500234); // ประมาณ 50 px} |
เราสามารถกำหนดค่าเริ่มความสูงของทุกๆ แถวรายการด้วยคำสั่ง
1 | $sheet->getDefaultRowDimension()->setRowHeight(15); |
การซ่อนหรือแสดง แถว - คอลัมน์
เราสามารถใช้คำสั่ง setVisible() สำหรับซ่อนหรือแสดง แถว - คอลัมน์ โดยกำหนดค่า false ถ้าต้องการซ่อน และกำหนด true หลังจาก
ซ่อนด้วยคำสั่ง setVisible(false) เพื่อแสดง โดยรูปแบบการใช้งานจะคล้ายกันสำหรับแถว และคอลัมน์ เพียงแค่อ้างอิงการใช้งานตามประเภท
ของรายการนั้น
1 2 3 4 5 6 7 | // กรณีคอลัมน์$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
ดูตัวอย่างคำสั่ง
1 2 3 4 5 | $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) ดังนี้
1 2 3 4 | $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 แล้ว
แนวทางจะเป็นดังนี้
1 2 3 4 5 6 7 8 9 10 11 12 | $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 ที่มีค่าเดิมอยู่แล้ว ให้อยู่ด้วยกัน เช่น สมมติ มีคอลัมน์ชื่อ และคอลัมน์นามสกุล แต่เราต้องการรวมชื่อและนามสกุล
ให้อยู่ในคอลัมน์เดียวกัน ก็สามารถใช้วิธ๊นี้ได้
1 2 3 4 5 6 7 8 9 10 11 | $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 } |
ผลลัพธ์ที่ได้
และอีกเทคนิคหนึ่งก็คือ ใช้สูตรรวมข้อมูลไว้ในคอลัมน์ใหม่ แล้วใช้การซ่อนคอลัมน์เดิมไป ดังนี้
1 2 3 4 5 6 7 8 9 10 | $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
อ่านต่อที่บทความ
เนื้อหาที่เกี่ยวข้อง