How to generate Excel file using PHPExcel in Codeigniter

Here we will discuss how to create excel file from database by PHPExcel in codeigniter. We’ll Export Data from Database to Excel file in Codeigniter Framework by using PHPExcel library.

Explanation :

Step 1 :- Click here to download PHPExcel.

Step 2 :- Extract the downloaded PHPExcel lib files and copy Class directory inside files to application/third-party directory(folder).

Step 3 :- Create Excel.php in application/library folder.

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 4 :- Call Excel.php library in your controller.

Eg: $this->load->library(‘excel’);

Step 5 :- Controller Excel.php

if (!defined('BASEPATH')) exit('No direct script access allowed'); 
class Excel extends CI_Controller
{ 
   public function excel() 
   { 
      //load our new PHPExcel library 
      $this->load->library('excel'); 
      //activate worksheet number 1 
      $this->excel->setActiveSheetIndex(0); 
      //name the worksheet 
      $this->excel->getActiveSheet()->setTitle('Users list'); 
      // load database 
      $this->load->database(); 
      // load model 
      $this->load->model('userModel'); 
      // get all users in array formate 
      $users = $this->userModel->get_users(); 
      // read data to active sheet 
      $this->excel->getActiveSheet()->fromArray($users); 
      $filename='just_some_random_name.xls'; 
      //save our workbook as this file name 
      header('Content-Type: application/vnd.ms-excel'); 
      //mime type 
      header('Content-Disposition: attachment;filename="'.$filename.'"'); 
      //tell browser what's the file name 
      header('Cache-Control: max-age=0'); 
      //no cache 
      //save it to Excel5 format (excel 2003 .XLS file), change 
      this to 'Excel2007' (and adjust the filename extension, also the 
      header mime type) 
      //if you want to save it as.XLSX Excel 2007 format 
      $objWriter = PHPExcel_IOFactory::createWriter($this->excel, 'Excel5'); 
      //force user to download the Excel file without writing it to server's 
      HD 
      $objWriter->save('php://output');
   } 
}

Step 6 :- To download Excel file provide a link                

Eg:- <a href="<?php echo base_url('Excel/excel'); ?>Download</a>

*** To make title cells use this :- $this->excel->getActiveSheet()->fromArray($header);

*** Other cells can be shown as $this->excel->getActiveSheet()->fromArray($users, null, 'A2');

*** To style title cells

//change the font size        

$this->excel->getActiveSheet()->getStyle('A1:F1')->getFont()->setSize(20);

 //make the font become bold

$this->excel->getActiveSheet()->getStyle('A1:F1')->getFont()->setBold(true);

Hope this easy method for creating Excel file using the library phpexcel in codeigniter will help you.

For other Codeigniter solutions please check this link : Codeigniter