ประยุกต์ DataTable ร่วมกับ PHPExcel ออกรายงาน ใน Codeigniter ตอนที่ 6

$this->db->last_query();

SELECT * FROM `tbl_provinces` ORDER BY `province_id` ASC LIMIT 10

<?php
defined('BASEPATH') OR exit('No direct script access allowed');
  
class Ajaxdata extends CI_Controller {
    private $table = "tbl_provinces"; // กำหนดชื่อตารางข้อมูล
    // กำหนดฟิลด์ข้อมูลที่สามารถให้ค้นหาข้อมูลได้
    private $column_search = array(
        "province_id","province_name","province_name_eng"
    );
    // กำหนดฟิลด์ข้อมูลที่สามารถให้เรียงข้อมูลได้
    private $column_order = array(
        NULL,"province_id","province_name","province_name_eng"
    );  
    // กำหนดฟิลด์ข้อมูลที่่ต้องการเรียงข้อมูลเริ่มต้น และรูปแบบการเรียงข้อมูล
    private $order = array("province_id"=>"asc");
    public function __construct(){
        parent::__construct();
    }
    public function index(){
         
        $data = array();
        $_draw = $this->input->post('draw'); // ครั้งที่การดึงข้อมูล ค่าของ dataTable ส่งมาอัตโนมัติ
        $_p = $this->input->post('search'); // ตัวแปรคำค้นหาถ้ามี
        $_earchValue = $_p['value']; // ค่าคำค้นหา
        $_order = $this->input->post('order'); // ตัวแปรคอลัมน์ที่ต้องการเรียงข้อมูล
        $_length = $this->input->post('length'); // ตัวแปรจำนวนรายการที่จะแสดงแต่ละหน้า
        $_start = $this->input->post('start'); // เริ่มต้นที่รายการ
        $query = $this->db->from($this->table);  // ดึงข้อมูลจากตารางที่กำหนด
        $total_rows_all = $this->db->count_all_results(null,FALSE); // เก็บค่าจำนวนรายการทั้งหมด      
        $i = 0;    
        // วนลูปฟิลด์ที่ต้องการค้นหา กรณีมีการส่งคำค้น เข้ามา
        foreach ($this->column_search as $item){
            if($_earchValue){ // ถ้ามีค่าคำค้น
                // จัดรูปแแบคำสั่ง sql การใช้งาน OR กับ LIKE
                if($i===0){ // ถ้าเป็นค่าเริ่มเต้นให้เปิดวงเล็บ (
                    $this->db->group_start(); 
                    $this->db->like($item, $_earchValue);                 
                }else{
                    $this->db->or_like($item, $_earchValue);
                }
                if(count($this->column_search) - 1 == $i){ // ถ้าเป็นต้วสุดท้ายให้ปิดวงเล็บ )
                    $this->db->group_end();
                }
            }
            $i++;
            // ส่วนของการวนลูปนี้จะได้รูปแบบ เช่น ( fileld1 LIKE 'a' OR field2 LIKE 'a' )  เป็นต้น
        }  
        // ถ้ามีการส่งฟิลด์ที่ต้องการเรียงข้อมูลเข้ามา เช่น กรณีกดที่หัวข้อในตาราง dataTable
        if(isset($_order) && $_order!=NULL){
            // จัดรูปแบบการจัดเรียงข้อมูลจากค่าที่ส่งมา
            $_orderColumn = $_order['0']['column'];
            $_orderSort = $_order['0']['dir'];
            $this->db->order_by($this->column_order[$_orderColumn], $_orderSort);
        }else{ // กรณีไม่ได้ส่งค่าในตอนต้น ให้ใช้ค่าตามที่กำหนด
            // จัดรูปแบบการจัดเรียง  ตามที่กำหนดด้ายตัวแปร $order ด้านบน
            $order = $this->order;
            $this->db->order_by(key($order), $order[key($order)]);            
        }
        $total_rows_filter = $this->db->count_all_results(null,FALSE); // กำหนดค่าจำนวนข้อมูลหลังมีเงื่อนไขต่างๆ     		
        if($_length != -1){ // กรณีมีการกำหนดว่าต้องการแสดงข้อมูลหน้าละกี่รายการ
            $this->db->limit($_length, $_start); // จัดรูปแบบการแสดง ผลที่ได้เช่น LIMIT 10,10
        }   
        $query = $this->db->get(); // คิวรี่ข้อมูลตาเงื่อนไข
		
		$sql_str = $this->db->last_query(); // เก็บค่าคำสั่ง SQL ล่าสุด
		$sql_str_all =substr($sql_str,0,strpos($sql_str,'LIMIT')); // เก็บค่าคำสั่ง SQL ล่าสุดโดยตัด LIMIT ออก
		$this->session->set_userdata('ses_sql_str_all',$sql_str_all); // กำหนดตัวแปร session ชื่อ ses_sql_str_all
		$this->session->set_userdata('ses_sql_str',$sql_str); // กำหนดตัวแปร session ชื่อ ses_sql_str

        $_page = $this->input->post('page'); // ค่าตัวแปร page ที่เรากำหนดเองส่งหน้าปัจจุบันเข้ามา
        // วนลูปนำฟิลด์รายการที่ต้องการและสอดคล้องกันมาไว้ในตัวแปร array ที่ชื่อ $data
        $_i = 0; // ตัวแปรเลขลำดับข้อมูล
        foreach ($query->result_array() as $row){
            $_i++;
            $data[] = array(
                ($_page*$_length)+$_i,
                $row['province_id'],
                $row['province_name'],
                $row['province_name_eng']
            );
        }
        // กำหนดรูปแบบ array ของข้อมูลที่ต้องการสร้าง JSON data ตามรูปแบบที่ DataTable กำหนด
        $output = array(
            "draw" => $_draw, // ครั้งที่เข้ามาดึงข้อมูล
            "recordsTotal" => $total_rows_all, // ข้อมูลทั้งหมดที่มี
            "recordsFiltered" => $total_rows_filter, // ข้อมูลเฉพาะที่เข้าเงื่อนไข เช่น ค้นหา แล้ว       
             "data" => $data // รายการ array ข้อมูลที่จะใช้งาน
        );
        echo json_encode($output);
        exit();         
    }
}

<?php
defined('BASEPATH') OR exit('No direct script access allowed');
  
class Export extends CI_Controller {

    public function __construct() {
        parent::__construct();
		$this->load->library('excel');    
    }
    public function index() {
     
		// เรียนกใช้ PHPExcel  
		$objPHPExcel = new PHPExcel();   
		// เราสามารถเรียกใช้เป็น  $this->excel แทนก็ได้
		 
		// กำหนดค่าต่างๆ ของเอกสาร excel
		$objPHPExcel->getProperties()->setCreator("Ninenik.com")  
									 ->setLastModifiedBy("Ninenik.com")  
									 ->setTitle("PHPExcel Test Document")  
									 ->setSubject("PHPExcel Test Document")  
									 ->setDescription("Test document for PHPExcel, generated using PHP classes.")  
									 ->setKeywords("office PHPExcel php")  
									 ->setCategory("Test result file");      
	 
		// กำหนดชื่อให้กับ worksheet ที่ใช้งาน  
		$objPHPExcel->getActiveSheet()->setTitle('Provinces Report');  
		   
		// กำหนด worksheet ที่ต้องการให้เปิดมาแล้วแสดง ค่าจะเริ่มจาก 0 , 1 , 2 , ......  
		$objPHPExcel->setActiveSheetIndex(0);        
									  
		// การจัดรูปแบบของ cell  
		$objPHPExcel->getDefaultStyle()  
								->getAlignment()  
								->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP)  
								->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);   
								//HORIZONTAL_CENTER //VERTICAL_CENTER               
								 
		// จัดความกว้างของคอลัมน์
		$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
		$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
		$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);     
		$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);                                              
		 
		// กำหนดหัวข้อให้กับแถวแรก
		$objPHPExcel->setActiveSheetIndex(0)  
					->setCellValue('A1', '#')    
					->setCellValue('B1', 'province id')  
					->setCellValue('C1', 'province name')  
					->setCellValue('D1', 'province name eng');    
			 
		// ดึงข้อมูลเริ่มเพิ่มแถวที่ 2 ของ excel            
		$start_row=2;  
		
		// กรณีส่งตัวแปร GET['all'] หรือให้แสดงข้อมูลทั้งหมด
		if($this->input->get('all')){
			$sql = $this->session->ses_sql_str_all; // ใช้คำสั่ง SQL ที่ดึงรายกรทั้งหมด
		}else{
	 		$sql = $this->session->ses_sql_str; // ใช้คำสั่ง SQL ที่ดึงข้อมูลเฉพาะหน้านั้นๆ
		}
		$query = $this->db->query($sql);  
		$result = $query->result_array(); 
		$i_num=0;
		if(count($result)>0){
			foreach($result as $row){
				$i_num++;
				 
				// หากอยากจัดข้อมูลราคาให้ชิดขวา
				$objPHPExcel->getActiveSheet()
					->getStyle('C'.$start_row)
					->getAlignment()  
					->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);         
					 
				// หากอยากจัดให้รหัสสินค้ามีเลย 0 ด้านหน้า และแสดง 3     หลักเช่น 001 002
				$objPHPExcel->getActiveSheet()
					->getStyle('B'.$start_row)
					->getNumberFormat()
					->setFormatCode('000');          
				 
				// เพิ่มข้อมูลลงแต่ละเซลล์                          
				$objPHPExcel->setActiveSheetIndex(0)  
							->setCellValue('A'.$start_row, $i_num)  
							->setCellValue('B'.$start_row, $row['province_id'])  
							->setCellValue('C'.$start_row, $row['province_name'])  
							->setCellValue('D'.$start_row, $row['province_name_eng']);           
				 
				// เพิ่มแถวข้อมูล
				$start_row++;               
			}
	 
			// กำหนดรูปแบบของไฟล์ที่ต้องการเขียนว่าเป็นไฟล์ excel แบบไหน ในที่นี้เป้นนามสกุล xlsx  ใช้คำว่า Excel2007
			// แต่หากต้องการกำหนดเป็นไฟล์ xls ใช้กับโปรแกรม excel รุ่นเก่าๆ ได้ ให้กำหนดเป็น  Excel5
			$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');  // Excel2007 (xlsx) หรือ Excel5 (xls)        
			 
			$filename='Province-'.date("dmYHi").'.xlsx'; //  กำหนดชือ่ไฟล์ นามสกุล xls หรือ xlsx
			// บังคับให้ทำการดาวน์ดหลดไฟล์
			// https://www.freeformatter.com/mime-types-list.html
			// header('Content-Type: application/vnd.ms-excel'); //mime type xls
			header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); //mime type	 xlsx	
			header('Content-Disposition: attachment;filename="'.$filename.'"'); //tell browser what's the file name
			header('Cache-Control: max-age=0'); //no cache
			ob_end_clean();     
			$objWriter->save('php://output'); // ดาวน์โหลดไฟล์รายงาน
			// หากต้องการบันทึกเป็นไฟล์ไว้ใน server  ใช้คำสั่งนี้ $this->excel->save("/path/".$filename); 
			// แล้วตัด header ดัานบนทั้ง 3 อันออก   
			exit();    
		}
    }
}

<link rel="stylesheet" href="//cdn.datatables.net/1.10.15/css/jquery.dataTables.min.css">  
<link rel="stylesheet" href="//cdn.datatables.net/buttons/1.4.1/css/buttons.dataTables.min.css">
<script src="//cdn.datatables.net/1.10.15/js/jquery.dataTables.min.js"></script>
<script src="//cdn.datatables.net/buttons/1.4.1/js/dataTables.buttons.min.js"></script>
<script src="//cdn.datatables.net/buttons/1.4.1/js/buttons.print.min.js"></script>

// สร้างปุ่มแบบกำหนดเองให้กับ DataTable ในที่นี้ใช้ชื่อว่า exportExcel
$.fn.dataTable.ext.buttons.exportExcel = {
    className: 'buttons-excel',  // กำหนด css class 
    action: function ( e, dt, node, config ) { // กำหนดการทำงาน
		if(this.text() == 'Excel All Page'){ // ถ้าปุ่มที่กด มีข้อความว่า  Excel All Page
			window.open('<?=base_url('export/?all=1')?>'); // ให้เปิดไฟล์ export.php?all=1 ดึงข้อมูลทั้งหมด
		}else{
        	window.open('<?=base_url('export')?>'); // ให้เปิดไฟล์ export.php ดึงข้อมูลเฉพาะหน้าปัจจุบัน
		}
    }
};

<link rel="stylesheet" href="//cdn.datatables.net/1.10.15/css/jquery.dataTables.min.css">  
<link rel="stylesheet" href="//cdn.datatables.net/buttons/1.4.1/css/buttons.dataTables.min.css">
<script src="//cdn.datatables.net/1.10.15/js/jquery.dataTables.min.js"></script>
<script src="//cdn.datatables.net/buttons/1.4.1/js/dataTables.buttons.min.js"></script>
<script src="//cdn.datatables.net/buttons/1.4.1/js/buttons.print.min.js"></script>
 
<table id="table_server_id" class="display">
    <thead>
        <tr>
            <th>#</th>
            <th>Province ID</th>
            <th>Province Name TH</th>
            <th>Province Name ENG</th>
        </tr>
    </thead>
</table>
   
<script type="text/javascript">
// สร้างปุ่มแบบกำหนดเองให้กับ DataTable ในที่นี้ใช้ชื่อว่า exportExcel
$.fn.dataTable.ext.buttons.exportExcel = {
    className: 'buttons-excel',  // กำหนด css class 
    action: function ( e, dt, node, config ) { // กำหนดการทำงาน
		if(this.text() == 'Excel All Page'){ // ถ้าปุ่มที่กด มีข้อความว่า  Excel All Page
			window.open('<?=base_url('export/?all=1')?>'); // ให้เปิดไฟล์ export.php?all=1 ดึงข้อมูลทั้งหมด
		}else{
        	window.open('<?=base_url('export')?>'); // ให้เปิดไฟล์ export.php ดึงข้อมูลเฉพาะหน้าปัจจุบัน
		}
    }
};
$(function(){
     $('#table_server_id').DataTable( {
        "processing": true, // แสดงข้อความกำลังดำเนินการ กรณีข้อมูลมีมากๆ จะสังเกตเห็นง่าย
        "serverSide": true,  // ใช้งานในโหมด Server-side processing
        "order": [], // กำหนดให้ไม่ต้องการส่งการเรียงข้อมูลค่าเริ่มต้น จะใช้ค่าเริ่มต้นตามค่าที่กำหนดในไฟล์ php
        "ajax": {
            "url": "<?=base_url("ajaxdata")?>", // ไฟล์ Server script php
            "data":{   // เพิ่มตัวแปรที่ต้องกาส่งเข้าไปแบบกำหนดเอง
                "page":function(){ // ใข้ข้อมูลตัวแปรชื่อ page
                    var dataTable1 = $('#table_server_id').DataTable(); // จะใช้ข้อมูลอ้างอิงจาก dataTable
                    return dataTable1.page.info().page; // ส่งค่าเลขหน้าปัจจุบันไปไว้ในตัวแปร page ค่าเรี่มต้นนับจาก 0
                }
            },
            "type": "POST"  // ส่งข้อมูลแบบ post
        },
        "columnDefs": [  // กำหนดลักษณะพิเสษเฉพาะสำหรับคอลัมน์ตารางที่ต้องการ
            { 
                "targets": [ 0 ], // เราต้องการกำหนดคอลัมน์แรก ค่าเริ่มต้นที่ 0
                "orderable": false, // ให้ไม่ต้องสามารถเรียงข้อมูลได้ เพราะเป็นลำดับรายการเฉยๆ 
            }
        ],
        "lengthMenu": [ 10, 25, 50, 100 ],		
		"dom": 'Bfrtip',
		"buttons":[
			{ // เริ่มต้นกลุ่มปุ่มแรก
				"extend":'collection',
				"text":'Export',
				"autoClose": true, // ให้ปิดกลุ่มปุ่มที่เปิดไว้แล้วอัตโนมัติ
				"buttons": [ // ปุ่มทั้งหมดในกลุ่ม export
					{ // ปุ่มส่งออก Excel ปุ่มแรก
						"extend": 'exportExcel', // สืบทอดจากปุ่มที่เรากำหนด
						"text": 'Excel current Page' // กำหนดข้อความปุ่มตามต้องการ
					},
					{ // ปุ่มส่งออก Excel ปุ่มแรก
						"extend": 'exportExcel',
						"text": 'Excel All Page'						
					},
					'print'
				]							
			}, // จบส่วนของ กลุ่มปุ่มแรก
			'pageLength'
		]
    } );
});
</script>