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.

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:

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

	$this->load->library('excel');
 ?>

Create a controller file like contactus.php inside “application/controllers” folder.

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 * 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.

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 * 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.

    










            if (isset($employeeInfo) && !empty($employeeInfo)) {
                foreach ($employeeInfo as $key => $element) {
                    ?>







                }
            } else {
                ?>






        
            
                
First Name
                
Last Name
                                           
Email
                                      
DOB
                
Contact Name
            
        
        
                                
                        
                           
                         
                                               
                        
                    
                                    
                    
There is no employee.
                    
                    
    
     Export Data
 

Demo  [sociallocker] Download[/sociallocker]