Lembar kerja Excel adalah kumpulan sel tempat Anda dapat menyimpan dan memanipulasi data. Setiap buku kerja Excel dapat berisi beberapa lembar kerja. Jika kita ingin memelihara lembar kerja Excel dari data website kita maka kita memiliki opsi untuk mengetik satu per satu data di lembar kerja excel kita, untuk bekal hari ini kita mengetahui cara mengekspor data dari database di lembar Excel menggunakan Codeigniter
Pertama, kita perlu mendownload Library PHPExcel, lalu mengekstrak Library PHPExcel
Langkah 1. Ekstrak Perpustakaan PHPExcel
Catatan. Salin dan Tempel di dalam folder "application/third_party".
Langkah 2. Buat berkas
Buat file bernama Excel. php di dalam folder “application/libraries”.
Langkah 3. Buat Basis Data
Untuk tutorial ini, Anda memerlukan database MySQL dengan tabel berikut
<?php //Table structure for table employee CREATE TABLE `import` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary Key', `first_name` varchar(100) NOT NULL COMMENT 'First Name', `last_name` varchar(100) NOT NULL COMMENT 'Last Name', `email` varchar(255) NOT NULL COMMENT 'Email Address', `dob` varchar(20) NOT NULL COMMENT 'Date of Birth', `contact_no` int(11) NOT NULL COMMENT 'Contact No', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='datatable demo table' AUTO_INCREMENT=1; //Dumping data for table `import` INSERT INTO `import` (`id`, `first_name`, `last_name`, `email`, `dob`, `contact_no`) VALUES (1, 'Admin', '1st', 'admin@crowdforgeeks.com', '21-02-2011', '9000000002'), (2, 'User', '4rth', 'user@crowdforgeeks.com', '21-02-2011', '9000000003'), (3, 'Editor', '3rd', 'editor@crowdforgeeks.com', '21-02-2011', '9000000004'), (4, 'Writer', '2nd', 'writer@crowdforgeeks.com', '21-02-2011', '9000000005'), (5, 'Contact', 'one', 'contact@crowdforgeeks.com', '21-02-2011', '9000000006'), (6, 'Manager', '1st', 'manager@crowdforgeeks.com', '21-02-2011', '9000000007'); ?>_Langkah 4. Buat Pengontrol dan muat kelas
Sintaksis. Muat kelas "excel" di controller
<?php // load library $this->load->library('excel'); ?>Buat file pengontrol seperti contactus. php di dalam folder “application/controllers”.
<?php if (!defined('BASEPATH')) exit('No direct script access allowed'); class Export extends CI_Controller { // construct public function __construct() { parent::__construct(); // load model $this->load->model('Export_model', 'export'); } // export xlsx|xls file public function index() { $data['page'] = 'export-excel'; $data['title'] = 'Export Excel data | CrowdforGeeks'; $data['employeeInfo'] = $this->export->employeeList(); // load view file for output $this->load->view('export/index', $data); } // create xlsx public function createXLS() { // create file name $fileName = 'data-'.time().'.xlsx'; // load excel library $this->load->library('excel'); $empInfo = $this->export->employeeList(); $objPHPExcel = new PHPExcel(); $objPHPExcel->setActiveSheetIndex(0); // set Header $objPHPExcel->getActiveSheet()->SetCellValue('A1', 'First Name'); $objPHPExcel->getActiveSheet()->SetCellValue('B1', 'Last Name'); $objPHPExcel->getActiveSheet()->SetCellValue('C1', 'Email'); $objPHPExcel->getActiveSheet()->SetCellValue('D1', 'DOB'); $objPHPExcel->getActiveSheet()->SetCellValue('E1', 'Contact_No'); // set Row $rowCount = 2; foreach ($empInfo as $element) { $objPHPExcel->getActiveSheet()->SetCellValue('A' . $rowCount, $element['first_name']); $objPHPExcel->getActiveSheet()->SetCellValue('B' . $rowCount, $element['last_name']); $objPHPExcel->getActiveSheet()->SetCellValue('C' . $rowCount, $element['email']); $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $element['dob']); $objPHPExcel->getActiveSheet()->SetCellValue('E' . $rowCount, $element['contact_no']); $rowCount++; } $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel); $objWriter->save(ROOT_UPLOAD_IMPORT_PATH.$fileName); // download file header("Content-Type: application/vnd.ms-excel"); redirect(HTTP_UPLOAD_IMPORT_PATH.$fileName); } } ?>Langkah 5. Buat Model
Buat file model bernama Export_model. php di dalam folder "application/models".
<?php if (!defined('BASEPATH')) exit('No direct script access allowed'); class Export_model extends CI_Model { // get employee list public function employeeList() { $this->db->select(array('e.id', 'e.first_name', 'e.last_name', 'e.email', 'e.dob', 'e.contact_no')); $this->db->from('import as e'); $query = $this->db->get(); return $query->result_array(); } } ?>_Langkah 6. Buat tampilan
Buat file view bernama index. php di dalam folder "application/views/export".
<div class="table-responsive"> <table class="table table-hover tablesorter"> <thead> <tr> <th class="header">First Name</th> <th class="header">Last Name</th> <th class="header">Email</th> <th class="header">DOB</th> <th class="header">Contact Name</th> </tr> </thead> <tbody> <?php if (isset($employeeInfo) && !empty($employeeInfo)) { foreach ($employeeInfo as $key => $element) { ?> <tr> <td><?php echo $element['first_name']; ?></td> <td><?php echo $element['last_name']; ?></td> <td><?php echo $element['email']; ?></td> <td><?php echo $element['dob']; ?></td> <td><?php echo $element['contact_no']; ?></td> </tr> <?php } } else { ?> <tr> <td colspan="5">There is no employee.</td> </tr> <?php } ?> </tbody> </table> <a class="pull-right btn btn-primary btn-xs" href="<?php echo site_url()?>export/createxls"><i class="fa fa-file-excel-o"></i> Export Data</a> </div>
Membagikan
- Membagikan
Salinan