Bagaimana cara mengekspor dari codeigniter 3 ke excel?

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

<?php
if (!defined('BASEPATH')) exit('No direct script access allowed');

require_once APPPATH . "/third_party/PHPExcel.php";
class Excel extends PHPExcel {
    public function __construct() {
        parent::__construct();
    }
}
?>

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', '[email protected]', '21-02-2011', '9000000002'),
(2, 'User', '4rth', '[email protected]', '21-02-2011', '9000000003'),
(3, 'Editor', '3rd', '[email protected]', '21-02-2011', '9000000004'),
(4, 'Writer', '2nd', '[email protected]', '21-02-2011', '9000000005'),
(5, 'Contact', 'one', '[email protected]', '21-02-2011', '9000000006'),
(6, 'Manager', '1st', '[email protected]', '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

Bagaimana cara mengekspor catatan ke file Excel atau CSV menggunakan framework codeigniter PHP?

Bagaimana cara mengekspor Excel di CodeIgniter 4?

Bagaimana cara menggunakan pustaka Excel di codeigniter?

Bagaimana cara mengekspor file CSV di CodeIgniter 4?