Datatables memberikan ekstension berupa tombol untuk melakukan export dan pencetakan dari data pada datatables. Untuk pengaplikasian ekstension tersebut pada datatables clientside cukup mudah, tetapi untuk datatables serverside diperlukan perintah tambahan agar semua data di load semua. Karena prinsip datatables serverside hanya me-load data yang tampil saja, maka ketika dilakukan exporting dan printing data yang terekspor dan tercetak hanya data yang tampil pada saat itu.
Untuk membuatnya kita dapat menggunakan file pada tutorial sebelumnya CodeIgniter 3 – Membuat Datatables Server Side atau membuat file baru. Untuk database kita dapat menggunakan file database_daerah.sql yang ada di menu download.
Nah, yang pertama akan kita lakukan adalah membuat model.
Membuat Model
Buat sebuah model dengan nama Button_export_model.php, kemudian ketikkan script berikut:
|
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 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 |
<?php defined('BASEPATH') or exit('No direct script access allowed'); class Button_export_model extends CI_Model { function __construct() { parent::__construct(); } var $column_order = array(null, 'kelurahan.nama', 'kecamatan.nama', 'kabupaten.nama', 'provinsi.nama'); var $column_search = array('kelurahan.nama', 'kecamatan.nama', 'kabupaten.nama', 'provinsi.nama'); var $order = array('id_kel' => 'asc'); private function _get_datatables_query() { $this->db->select('kelurahan.nama AS nm_kel, kecamatan.nama AS nm_kec, kabupaten.nama AS nm_kab, provinsi.nama AS nm_prov'); $this->db->from('kelurahan'); $this->db->join('kecamatan', 'kelurahan.id_kec = kecamatan.id_kec', 'left'); $this->db->join('kabupaten', 'kecamatan.id_kab = kabupaten.id_kab', 'left'); $this->db->join('provinsi', 'kabupaten.id_prov = provinsi.id_prov', 'left'); $i = 0; foreach ($this->column_search as $item) { if ($_POST['search']['value']) { if ($i === 0) { $this->db->group_start(); $this->db->like($item, $_POST['search']['value']); } else { $this->db->or_like($item, $_POST['search']['value']); } if (count($this->column_search) - 1 == $i) $this->db->group_end(); } $i++; } if (isset($_POST['order'])) { $this->db->order_by($this->column_order[$_POST['order']['0']['column']], $_POST['order']['0']['dir']); } else if (isset($this->order)) { $order = $this->order; $this->db->order_by(key($order), $order[key($order)]); } } function get_datatables() { $this->_get_datatables_query(); if ($_POST["length"] != -1) { $this->db->limit($_POST['length'], $_POST['start']); } $query = $this->db->get(); return $query->result(); } public function count_filtered() { $this->_get_datatables_query(); $query = $this->db->get(); return $query->num_rows(); } public function count_all() { $this->db->from('kelurahan'); return $this->db->count_all_results(); } } |
Membuat Controller
Buat sebuah controller dengan nama Button_export.php, kemudian ketikkan script berikut:
|
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 defined('BASEPATH') or exit('No direct script access allowed'); class Button_export extends CI_Controller { public function __construct() { parent::__construct(); $this->load->model('Button_export_model', 'Button'); } public function index() { $this->load->view('button_export'); } public function listdata() { $list = $this->Button->get_datatables(); $data = array(); $no = $_POST['start']; if (isset($_POST['start']) && isset($_POST['draw'])) { $no = $_POST['start']; } else { die(); }; foreach ($list as $dt) { $no++; $row = array(); $row[] = $no; $row[] = $dt->nm_kel; $row[] = $dt->nm_kec; $row[] = $dt->nm_kab; $row[] = $dt->nm_prov; $data[] = $row; } $output = array( "draw" => $_POST['draw'], "recordsTotal" => $this->Button->count_all(), "recordsFiltered" => $this->Button->count_filtered(), "data" => $data, ); echo json_encode($output); } } |
Pada prinsipnya tidak ada perubahan pada file model dan controller dibandingkan dengan tutorial sebelumnya CodeIgniter 3 – Membuat Datatables Server Side, perbedaan hanya pada view saja.
Membuat View
Buat sebuah file view dengan nama button_export.php, kemudian ketikkan script berikut:
|
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 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 |
<!DOCTYPE html> <html> <head> <title>Codeigniter - Button Export dan Print Datatables Server Side</title> <!-- Font --> <link rel="preconnect" href="https://fonts.googleapis.com"> <link rel="preconnect" href="https://fonts.gstatic.com" crossorigin> <link href="https://fonts.googleapis.com/css2?family=Oswald:wght@300;400;600&display=swap" rel="stylesheet"> <!-- Bootstrap CSS --> <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@4.6.0/dist/css/bootstrap.min.css" integrity="sha384-B0vP5xmATw1+K9KRQjQERJvTumQW0nPEzvF6L/Z6nronJ3oUOFUFpCjEUQouq2+l" crossorigin="anonymous"> <!-- JQuery --> <script src="https://code.jquery.com/jquery-3.3.1.min.js" integrity="sha256-FgpCb/KJQlLNfOu91ta32o/NMZxltwRo8QtmkMRdAu8=" crossorigin="anonymous"></script> <!-- Datatables --> <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.11.4/css/jquery.dataTables.min.css"> <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/buttons/2.2.2/css/buttons.dataTables.min.css"> <script type="text/javascript" language="javascript" src="https://cdn.datatables.net/1.11.3/js/jquery.dataTables.min.js"></script> <style> body { font-family: 'Oswald', sans-serif; font-weight: 300; } </style> </head> <body> <div class="container"> <div class="row mt-4"> <div class="col-12"> <div class="card"> <div class="card-header"> Button Export dan Print Datatables Server Side </div> <div class="card-body"> <div class="responsive"> <table id="list_data" class="table display row-border table-striped compact" style="width: 100%;"> <thead> <tr> <th>No</th> <th>Kelurahan</th> <th>Kecamatan</th> <th>Kabupaten</th> <th>Provinsi</th> </tr> </thead> </table> </div> </div> </div> </div> </div> </div> <!-- DataTables --> <script src="https://cdn.datatables.net/buttons/2.2.2/js/dataTables.buttons.min.js"></script> <script src="https://cdnjs.cloudflare.com/ajax/libs/jszip/3.1.3/jszip.min.js"></script> <script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.53/pdfmake.min.js"></script> <script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.53/vfs_fonts.js"></script> <script src="https://cdn.datatables.net/buttons/2.2.2/js/buttons.html5.min.js"></script> <script src="https://cdn.datatables.net/buttons/2.2.2/js/buttons.print.min.js"></script> <script type="text/javascript"> var dataTable_; $(document).ready(function() { dataTable_ = $('#list_data').DataTable({ dom: 'Bfrtip', processing: true, serverSide: true, searching: true, info: true, paging: true, lengthChange: true, ordering: true, ajax: { url: '<?php echo base_url('button_export/listdata'); ?>', type: "POST" }, buttons: [{ extend: 'copyHtml5', titleAttr: 'Copy', "action": newexportaction }, { extend: 'excelHtml5', titleAttr: 'Excel', "action": newexportaction }, { extend: 'csvHtml5', titleAttr: 'CSV', "action": newexportaction }, { extend: 'pdfHtml5', titleAttr: 'PDF', "action": newexportaction }, { extend: 'print', titleAttr: 'Print', "action": newexportaction }, ] }); function newexportaction(e, dt, button, config) { var self = this; var oldStart = dt.settings()[0]._iDisplayStart; dt.one('preXhr', function(e, s, data) { data.start = 0; data.length = 2147483647; dt.one('preDraw', function(e, settings) { if (button[0].className.indexOf('buttons-copy') >= 0) { $.fn.dataTable.ext.buttons.copyHtml5.action.call(self, e, dt, button, config); } else if (button[0].className.indexOf('buttons-excel') >= 0) { $.fn.dataTable.ext.buttons.excelHtml5.available(dt, config) ? $.fn.dataTable.ext.buttons.excelHtml5.action.call(self, e, dt, button, config) : $.fn.dataTable.ext.buttons.excelFlash.action.call(self, e, dt, button, config); } else if (button[0].className.indexOf('buttons-csv') >= 0) { $.fn.dataTable.ext.buttons.csvHtml5.available(dt, config) ? $.fn.dataTable.ext.buttons.csvHtml5.action.call(self, e, dt, button, config) : $.fn.dataTable.ext.buttons.csvFlash.action.call(self, e, dt, button, config); } else if (button[0].className.indexOf('buttons-pdf') >= 0) { $.fn.dataTable.ext.buttons.pdfHtml5.available(dt, config) ? $.fn.dataTable.ext.buttons.pdfHtml5.action.call(self, e, dt, button, config) : $.fn.dataTable.ext.buttons.pdfFlash.action.call(self, e, dt, button, config); } else if (button[0].className.indexOf('buttons-print') >= 0) { $.fn.dataTable.ext.buttons.print.action(e, dt, button, config); } dt.one('preXhr', function(e, s, data) { settings._iDisplayStart = oldStart; data.start = oldStart; }); setTimeout(dt.ajax.reload, 0); return false; }); }); dt.ajax.reload(); }; }) </script> </body> </html> |
bagian untuk menampilkan button pada datatables
|
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 |
buttons: [{ extend: 'copyHtml5', titleAttr: 'Copy', "action": newexportaction }, { extend: 'excelHtml5', titleAttr: 'Excel', "action": newexportaction }, { extend: 'csvHtml5', titleAttr: 'CSV', "action": newexportaction }, { extend: 'pdfHtml5', titleAttr: 'PDF', "action": newexportaction }, { extend: 'print', titleAttr: 'Print', "action": newexportaction }, ] |
Bagian untuk mengolah data serverside terdapat pada fungsi newexportaction
|
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 |
function newexportaction(e, dt, button, config) { var self = this; var oldStart = dt.settings()[0]._iDisplayStart; dt.one('preXhr', function(e, s, data) { data.start = 0; data.length = 2147483647; dt.one('preDraw', function(e, settings) { if (button[0].className.indexOf('buttons-copy') >= 0) { $.fn.dataTable.ext.buttons.copyHtml5.action.call(self, e, dt, button, config); } else if (button[0].className.indexOf('buttons-excel') >= 0) { $.fn.dataTable.ext.buttons.excelHtml5.available(dt, config) ? $.fn.dataTable.ext.buttons.excelHtml5.action.call(self, e, dt, button, config) : $.fn.dataTable.ext.buttons.excelFlash.action.call(self, e, dt, button, config); } else if (button[0].className.indexOf('buttons-csv') >= 0) { $.fn.dataTable.ext.buttons.csvHtml5.available(dt, config) ? $.fn.dataTable.ext.buttons.csvHtml5.action.call(self, e, dt, button, config) : $.fn.dataTable.ext.buttons.csvFlash.action.call(self, e, dt, button, config); } else if (button[0].className.indexOf('buttons-pdf') >= 0) { $.fn.dataTable.ext.buttons.pdfHtml5.available(dt, config) ? $.fn.dataTable.ext.buttons.pdfHtml5.action.call(self, e, dt, button, config) : $.fn.dataTable.ext.buttons.pdfFlash.action.call(self, e, dt, button, config); } else if (button[0].className.indexOf('buttons-print') >= 0) { $.fn.dataTable.ext.buttons.print.action(e, dt, button, config); } dt.one('preXhr', function(e, s, data) { settings._iDisplayStart = oldStart; data.start = oldStart; }); setTimeout(dt.ajax.reload, 0); return false; }); }); dt.ajax.reload(); }; |
Fungsi ini yang akan me-load semua data sebelum diekspor maupun dicetak.
Jika semua script sudah sesuai maka akan didapatkan hasil sebagai berikut:
Selanjutnya tinggal tekan masing-masing tombol untuk melakukan ekspor data baik berupa copy clipboard, download file excel, download file csv, download file pdf, dan melakukan pencetakan data.
Demikian semoga bermanfaat. Terimakasih.
