PHP Ionic Angular Phonegap AJAX Javascript CSS MySQL jQuery Forum


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

17 September 2017 By
phpexcel codeigniter ออกรายงาน datatable excel

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



เนื้อหานี้เป็นการประยุกต์เพิ่มความสามารถของ DataTable ให้รองรับการออกรายงานที่เป็นไฟล์ Excel
กับข้อมูลจำนวนมากๆ ซึ่งแตกต่างจากหัวข้อตอนที่แล้วเรื่อง
 
DataTable ออกรายงาน Excel หรือ PDF อย่างง่ายใน Codeigniter ตอนที่ 5 
http://www.ninenik.com/content.php?arti_id=817 via @ninenik
 
เนื่องจากเนื้อหาตอนที่ผ่านมา ในการออกรายการที่เป็นไฟล์ Excel หรือ PDF นั้น ยังมีข้อจำกัดในส่วนของ
จำนวนข้อมูลที่ใช้ในการสร้างไฟล์ ที่ไม่สามารถใช้กับรายการที่มีจำนวนมากๆ ได้ เนื้อหาต่อไปนี้ เราจะแนะนำ
วิธีการประยุกต์การใช้งาน PHPExcel ซึ่งเราเคยได้แนะนำวิธีการใช้งาน PHPExcel ร่วมกับ Project Codeigniter
ไว้มาแล้วในบทความเรื่อง
 
ออกรายงาน สร้างไฟล์ Excel ด้วย PHPExcel ใน Codeigniter 
http://www.ninenik.com/content.php?arti_id=691 via @ninenik
 
ดังนั้นวิธีการใช้งานร่วมกับ PHPExcel จะยึดตามบทความเก่า สามารถย้อนกลับไปอ่านเพิ่มเติมได้ 
 
เริ่มต้นให้ย้อนไปในส่วนของไฟล์ Ajaxdata.php ที่อยู่โฟลเดอร์ apps > controllers 
อ้างอิงจากบทความ
 
ใช้งาน Server side processing สำหรับ DataTable ร่วมกับ Codeigniter ตอนที่ 3 
http://www.ninenik.com/content.php?arti_id=815 via @ninenik
 
ไฟล์ Ajaxdata.php นี้เป็นเป็นส่วนของ server side script ที่ใช้ร่วมกับ DataTable
โดยส่วนที่เราต้องเพิ่มคือ ทำการสร้างตัวแปร session เพื่อเก็บคำสั่ง SQL ทุกๆ ครั้งที่มีการเรียกดูข้อมูล
ไม่ว่าจะเป็นแสดงหน้าข้อมูล การจัดเรียงข้อมูล หรือการค้นหาข้อมูล ค่าทำสั่ง SQL ก็จะเปลี่ยนไปตาม
เงื่อนไขที่ส่งมาจาก DataTable เราจะใช้
 
$this->db->last_query();
 
ทำการเก็บค่าคำสั่ง SQL (ค่าคำสั่งไม่ใช่ค่าผลลัพธ์) ดังนั้น ค่าที่ได้ ก็จะประมาณนี้
 
SELECT * FROM `tbl_provinces` ORDER BY `province_id` ASC LIMIT 10
 
โดยคำสั่ง SQL นี้เราจะเก็บไว้ในตัวแปร session เพื่อนำไปใช้ในส่วนของ PHPExcel
ดูส่วนที่เพิ่มเข้ามา เกี่ยวกับการเก็บตัวแปร session ที่บรรทัดที่ highlight ไว้
 
ไฟล์ Ajaxdata.php ที่อยู่โฟลเดอร์ apps > controllers 
 
<?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();         
    }
}
 
 
ต่อไปก็ส่วนของไฟล์ที่ทำการส่งออกไฟล์ Excel ให้เราสร้างไฟล์ชื่อ
Export.php ไว้ในโฟล์เดอร์ apps > controllers  ดังนี้
 
<?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();    
		}
    }
}
 
ไฟล์ Export.php จะทำหน้าที่ในการสร้างไฟล์ Excel และส่งออกไฟล์ตามเงื่อนไขตัวแปร session ที่สอดคล้อง
กับ DataTable โดยมีรูปแบบข้อมูล ตามที่เรากำหนด เราสามารถจัดรูปแบบคอลัมน์ ฟิลด์ข้อมูล ชื่อไฟล์ต่างๆ
ได้ในไฟล์นี้
 
 
และส่วนสุดท้าย ส่วนของการเรียกใช้งาน เนื่องจากเราไม่ได้ใช้งานการส่งออกรายการด้วย javascript
ด้วยวิธีเดิมแล้ว ดังนั้นเราก็จะเรียกใช้ไฟล์ที่เกี่ยวข้างเฉพาะที่จำเป็นเท่านั้น
 
<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>
 
เนื่องจากเราต้องสร้างปุ่มแบบกำหนดเอง เพื่อใช้ในการทำการไปเปิดไฟล์ Export.php ให้ทำการสร้างไฟล์
Excel สามารถทำได้ดังนี้
 
// สร้างปุ่มแบบกำหนดเองให้กับ 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 ดึงข้อมูลเฉพาะหน้าปัจจุบัน
		}
    }
};


 
นำปุ่มที่เรากำหนดเองไปใช้งานในขั้นตอนการเรียกใช้ DataTable จะได้โค้ดทั้งหมดเป็นดังนี้
 

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

 
 
ดูส่วนที่ปรับเพิ่มเติมในบรรทัดที่ทำการ highlight ไว้ 
เรามาลองทดสอบดูผลลัพธ์กัน เริ่มต้น ดูการแสดงของปุ่มที่เราสร้าง เมื่อเราคลิกที่ปุ่ม Export ปุ่มหลักแรก
ก็จะขึ้นปุ่มย่อย มีปุ่ม Excel current Page กับ Excel All Page อยู่ด้วยดังรูป 
 
 


 
 
ทดสอบกดปุ่ม Export current Page จะเป็นการแสดงรายการหน้าปัจจุบัน ที่แสดงแค่ 10 รายการ
การกดปุ่ม Export จะเป็นเปิดหน้าต่างใหม่เพื่อดาวน์โหดลไฟล์แบบ popup ดังนั้นถ้าบราเซอร์เรามีการบล็อก
การเปิด popup เป็นค่าเริ่มต้นไว้ ให้เราอนุญาตให้สามารถเปิด popup ก่อน ผลลัพธ์ไฟล์ excel ที่ได้จะเป็นดังรูป

 
 


 
 
สุดท้ายทดสอบกดปุ่ม Export All Page จะเป็นการแสดงรายการทั้งหมดในไฟล์ excel ดังรูป 
(ในรูปจะเห็นแค่บางส่วน)


 


 
 
เท่านี้เราก็สามารถส่งออกเอกสารไฟล์ excel จากข้อมูลที่มีจำนวนมากๆ รองรับเงื่อนไขการเรียงข้อมูล การค้นหา
ที่สอดคล้องกับ DataTable ได้อย่างง่ายแล้ว ส่วนการจัดรูปแบบของข้อมูลใน excel สามารถหาอ่านเพิ่มเติมเกี่ยว
กับ PHPExcel ได้
 




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









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



Tags:: codeigniter excel datatable phpexcel ออกรายงาน




เนื้อหาพิเศษ เฉพาะสำหรับสมาชิก

กรุณาล็อกอิน และลงชื่อติดตาม


สมัครสมาชิกได้ที่        ล็อกอินได้ที่   



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


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