Code Playground.

How to Export Data in Excel format in Codeigniter using PHPExcel Library

CFG

How to Export Data in Excel format in Codeigniter using PHPExcel Library

An Excel worksheet is a collection of cells where you can keep and manipulate data. Each Excel workbook can contain multiple worksheets.If we want to maintain an Excel worksheet of our website data then we have the option to type one by one data in our excel worksheet, for this provision today we know how to export data from database in Excel sheet 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.

<?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();
    }
}
?>

Step 3: Create Database

For this tutorial, you need a MySQL database with the following table:

<?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');
?>

Step 4: Create Controller and load class

Syntax: Load “excel” class in controller.

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

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

<?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);        
    }
    
}
?>

Step 5: Create Model

Create a model file named Export_model.php inside “application/models” folder.

<?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();
    }
}
?>

Step 6: Create views

Create a views file named index.php inside “application/views/export” folder.

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

 




CFG