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

<?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>';
}

$sheet->getColumnDimension('A')->setWidth(12);
// $sheet->getColumnDimension('A')->setWidth(9.140625); // เท่ากับ 64 pixels หรือ 1 char = 7.001709401709402 pixels

    100 / 7.001709401709402 = 14.2822265625

$sheet->getColumnDimension('A')->setWidth(14.2822265625);

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);

$sheet->getDefaultColumnDimension()->setWidth(14.2822265625); // ประมาณ 100 px
$sheet->getColumnDimension('A')->setWidth(9.140625);  // ประมาณ 64 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);

// กรณีคอลัมน์
$sheet->getColumnDimension('C')->setVisible(true);
$sheet->getColumnDimension('D')->setVisible(false);

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

$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

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

$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

$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);