Export Data to Excel using Codeigniter
In this post, we will explain how to Export data into Excel in CodeIgniter. Export data functionality makes your web application user-friendly and helps the user to maintain list data. Excel is the best technique to Export data in a file and you can easily export data to Excel using Codeigniter.
First, we need to download PHPExcel Library, then extract PHPExcel Library
Step 1: Extract PHPExcel Library
Note: Copy and Paste inside “application/third_party” folder.
Step 2: Create file
Create a file named Excel.php inside “application/libraries” folder.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
<?php if (!defined('BASEPATH')) exit('No direct script access allowed'); /* * ======================================= * Author: Team Tech Arise * License: Protected * Email: info@techarise.com * * ======================================= */ require_once APPPATH . "/third_party/PHPExcel.php"; class Excel extends PHPExcel { public function __construct() { parent::__construct(); } } ?> |
Step 3: Create Database
For this tutorial, you need a MySQL database with the following table:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
<?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, 'Team', 'Tech Arise', 'info@techarise.com', '21-02-2011', '9000000001'), (2, 'Admin', '1st', 'admin@techarise.com', '21-02-2011', '9000000002'), (3, 'User', '4rth', 'user@techarise.com', '21-02-2011', '9000000003'), (4, 'Editor', '3rd', 'editor@techarise.com', '21-02-2011', '9000000004'), (5, 'Writer', '2nd', 'writer@techarise.com', '21-02-2011', '9000000005'), (6, 'Contact', 'one', 'contact@techarise.com', '21-02-2011', '9000000006'), (7, 'Manager', '1st', 'manager@techarise.com', '21-02-2011', '9000000007'); ?> |
Step 4: Create Controller and load class
Syntax: Load “excel” class in controller.
|
1 2 3 4 |
<?php // load library $this->load->library('excel'); ?> |
Create a controller file like contactus.php inside “application/controllers” folder.
|
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 |
<?php /** * Description of Export Controller * * @author TechArise Team * * @email info@techarise.com */ 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 | TechArise'; $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); } } ?> |
Step 5: Create Model
Create a model file named Export_model.php inside “application/models” folder.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
<?php /** * Description of Export Model * * @author TechArise Team * * @email info@techarise.com */ 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(); } } ?> |
Step 6: Create views
Create a views file named index.php inside “application/views/export” folder.
|
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 |
<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> |