How to Export data from Database to Excel Sheet(.xls) using Codeigniter PHP Tutorial

This tutorial explains how to export or download MySql database into Excel Sheet(.xls or .xlsx) using Codeigniter PHP.

 



Introduction

Excel Sheet is a collection of cells where you keep and manipulate the data.

In this tutorial, we will learn how to export data from Mysql Database to Excel Sheet in PHP using Codeigniter Framework. For that, we will use PHPReport Library. By using that library, we can easily export MySql data into Excel Sheet.

Firstly, we will create a database, student_details and download PHPReport library. When we database created, then we will set an Excel template. We will explain how to set Excel template below. We will also make a Controller to use the model to get data from database and export this data into Excel Sheet.

Create Database

To fetch data from database to insert into excel Sheet, we need to create a database.

--
-- Database: `student_details`
--

-- --------------------------------------------------------

--
-- Table structure for table `student`
--

CREATE TABLE IF NOT EXISTS `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `emai_id` varchar(50) NOT NULL,
  `phone_number` varchar(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

--
-- Dumping data for table `student`
--

INSERT INTO `student`(`id`, `name`, `emai_id`, `phone_number`) VALUES
(1, 'Abhay', 'anand.abhay1910@gmail.com', '99xxxxxxxx'),
(2, 'Anand', 'anand@gmail.com', '87xxxxxxxx'),
(3, 'Vikram Kumar', 'kv@gmail.com', '77xxxxxxxx'),
(4, 'Anurag', 'anurag@gmail.com', '78xxxxxxxx');


.

Download Library

We need to download PHPReport Library and extract this downloaded file into libraries directory of your CI project.

Set Excel Sheet Template

Now we need to create a empty excel(.xlsx or .xls) file. We need to define a rule in Excel Sheet to fill the data. the rule is {id:column name}. The id is defined in the controller and same ‘id’ name, we will use here. For eg. I am going to set ‘id’ is ‘student’.

excel sheet

Create Controller and Model

To get data from database and export it into Excel Sheet, we need to create a Controller and Model. Here my controller name is excel and model name is excel_model. We need to load PHPReport library in the controller. Now, here is the code to export or download Excel Sheet with Data from MySql Database.

excel.php(Controller)

<?php 
if (!defined('BASEPATH')) exit('No direct script access allowed');  
 
class Excel extends CI_Controller {

  /**
   * @desc : load list modal and helpers
   */
      function __Construct(){
        parent::__Construct();
        $this->load->model('excel_model'); 
        $this->load->helper(array('form', 'url'));
        $this->load->helper('download');
        $this->load->library('PHPReport');
	        
        }

  /**
   *  @desc : This function is used to get data from database 
   *  And export data into excel sheet
   *  @param : void
   *  @return : void
   */
    public function index(){
      // get data from databse
      $data = $this->excel_model->getdata();

      $template = 'Myexcel.xlsx';
      //set absolute path to directory with template files
      $templateDir = __DIR__ . "/../controllers/";

      //set config for report
      $config = array(
        'template' => $template,
        'templateDir' => $templateDir
      );


      //load template
      $R = new PHPReport($config);

      $R->load(array(
              'id' => 'student',
              'repeat' => TRUE,
              'data' => $data   
          )
      );
      
      // define output directoy 
      $output_file_dir = "/tmp/";
     

      $output_file_excel = $output_file_dir  . "Myexcel.xlsx";
      //download excel sheet with data in /tmp folder
      $result = $R->render('excel', $output_file_excel);
     }
}


excel_model.php (Model)

<?php

class Excel_model extends CI_Model{
  
/**
* @desc load both db
*/
function __construct(){
parent::__Construct();


$this->db = $this->load->database('default', TRUE,TRUE);
}
  function getdata(){
     $this->db->select('*');
     $query = $this->db->get('student');
     return $query->result_array();
  }

}

Result:

Downloaded Excel Sheet with data.
 
export excel sheet from databse studytutorial

 
 


  • Mikky

    “Unable to load the requested class: PHPReport “

    • abhayanand1910

      Add PHPReport library into library directory of CI and include ‘$this->load->library(‘PHPReport’)’ into your controller

  • Mikky

    instead of taking the table from database..I have two additional two fields in view page. so I have to directly export the table from the view page to excel..Is there any source code for directly clicking the button and export to excel..i had tried with onclick fuction using javascript nd jquery.but its not working..

    • abhayanand1910

      If you want to directly export from view then send data as array and use above process to export excel sheet

  • Sathish Siva

    how to export the database table to excel using laravel4

  • Janak Vyas

    when i click download not generate download file and xls file its only url open

  • abhayanand1910

    Click on PHPReport Library text to download library

  • Shrikant Gupta

    Hi,
    thanks for this amazing tutorial, i used this code and successfully download users data in excel file but it’s work fine on localhost when i was upload this code on my website its not work i am getting file not found page, why i don’t understand, can you solve this please. thanks in advance.

    • abhayanand1910

      Please make sure, you added correct path of template directory…. And what is the error displayed on your screen

      • Shrikant Gupta

        thanks for reply, yes path is correct and error is file not found,
        when i remove or comment last line of code $objWriter->save(‘php://output’);
        then excel file will download but blank file. it’s why i don’t know.

        • abhayanand1910

          Please Use $R->render(‘excel’, $output_file_excel); ….
          $output_file_excel is the file name with directory path

  • Boby Gantez
    • abhayanand1910

      Thanks Boby, now we can be download PHPReport library

  • Aashish Sonawane
    • abhayanand1910

      Thanks Aashish, now we can be download PHPReport library

  • DavisTom

    shows me:
    Message: require_once(): Failed opening required ‘PHPExcel.php’ (include_path=’/Applications/MAMP/htdocs/aroundlocalhost/system/libraries’)

    • abhayanand1910

      Remove including path

  • Ranjeet Kasture

    The error I am getting is as follows

    Severity: Warning

    Message: require_once(PHPExcel.php): failed to open stream: No such file or directory

    Filename: PHPReport/PHPReport.php

    • abhayanand1910

      Re-Download PHPReport library, you can find PHPExcel.php file

  • kshitij

    I get these errors : Class ‘PHPExcel_Style_Fill’ not found and Class ‘PHPExcel_Style_Fill’ not found

    • abhayanand1910

      Re-download PHPReport libaray

      • nana

        we can i get the phpreport libary

  • nana

    after i create the file excel,,where can i put the file?

    • abhayanand1910

      Please read tutorial once

  • Suryanand

    This is the error, i get when i try to render the excel. Can anyone please help on this. i’m new to this.

    A PHP Error was encountered

    Severity: Warning

    Message: ZipArchive::close(): Failure to create temporary file: Bad file descriptor

    Filename: Writer/Excel2007.php

    Line Number: 398

    Backtrace:

    File: C:xampphtdocsCodeIgniterapplicationlibrariesPHPReportPHPExcelWriterExcel2007.php

    Line: 398

    Function: close

    File: C:xampphtdocsCodeIgniterapplicationlibrariesPHPReportPHPReport.php

    Line: 975

    Function: save

    File: C:xampphtdocsCodeIgniterapplicationlibrariesPHPReportPHPReport.php

    Line: 933

    Function: renderXlsx

    File: C:xampphtdocsCodeIgniterapplicationcontrollersExcel.php

    Line: 55

    Function: render

    File: C:xampphtdocsCodeIgniterindex.php

    Line: 315

    Function: require_once

    An uncaught Exception was encountered

    Type: PHPExcel_Writer_Exception

    Message: Could not close zip file /tmp/Myexcel.xlsx.

    Filename: C:xampphtdocsCodeIgniterapplicationlibrariesPHPReportPHPExcelWriterExcel2007.php

    Line Number: 399

    Backtrace:

    File: C:xampphtdocsCodeIgniterapplicationlibrariesPHPReportPHPReport.php

    Line: 975

    Function: save

    File: C:xampphtdocsCodeIgniterapplicationlibrariesPHPReportPHPReport.php

    Line: 933

    Function: renderXlsx

    File: C:xampphtdocsCodeIgniterapplicationcontrollersExcel.php

    Line: 55

    Function: render

    File: C:xampphtdocsCodeIgniterindex.php

    Line: 315

    Function: require_once

    • abhayanand1910

      Insure given directory path is correct and PHP5.0 provides Zip class (inbuilt) but PHP7.0 dosenot provide Zip class hence, you need to install it.

  • nabil

    Unable to load template file: /assets/files/demo.ods
    i did this
    //
    $template =’demo.ods’;
    $templateDir=’/assets/files/’;

    • abhayanand1910

      it works for xlsx

  • nabil

    //set absolute path to directory with template files
    $templateDir = __DIR__ . “/../controllers/”;
    and plz tell me what does that means

    • abhayanand1910

      Please provide your path directory in which you store Xlsx file

  • nabil

    $this->excel->getActiveSheet()->setTitle(‘Users list’);
    $this->excel->setActiveSheetIndex(0);
    $data=$this->Welcome_model->get_user_table();
    //var_dump($data);
    $this->excel->getActiveSheet()->fromArray($data, null, “A1”);
    $this->excel->getActiveSheet()->fromArray($data, null, “A2”);
    $this->excel->setActiveSheetIndex(0);
    //$this->excel->getActiveSheet()->fromArray($data);
    i am getting this error:->
    Parameter $source should be an array.

  • Nguyễn Cương

    Could not close zip file /tmp/Myexcel.xlsx.

    • abhayanand1910

      Provide your existing directory Path

  • Sree

    It works only once for me. After adding another entry when i tried export function, it still gave me the file with previous data.

    • abhayanand1910

      I think, You are doing something wrong

  • ekta

    i use this but nothing display,pls helpme

    • What did you do?

      • ekta

        here is my excel_model
        https://uploads.disquscdn.com/images/827ee69b8e7e362ff99ae73fd62508e05ef82460a2a2179a36cdece495f605bf.png

        i use this in controller
        $template=’Myexcel.xlsx’;
        $templateDir=__DIR__.”/../controllers/teacher/”;
        https://uploads.disquscdn.com/images/a31c4137939b9867219839a5410e19e90021324d06a197105a8f56fc31719ac1.png
        i dnt know where is the mistake i doing?

        • You doing wrong code… Please do $R = new PHPReport($config);
          Your are using ‘$config’

          • ekta

            when i change

            Backtrace:

            File: C:xampphtdocsjivanistame1applicationlibrariesPHPReportPHPReport.php

            Line: 183

            Function: loadTemplate

            File: C:xampphtdocsjivanistame1applicationlibrariesPHPReportPHPReport.php

            Line: 157

            Function: init

            File: C:xampphtdocsjivanistame1applicationcontrollersexcel.php

            Line: 35

            Function: __construct

            —————-

            when i change likes:

            $templateDir = __DIR__ .”..controllers”;

            An uncaught Exception was encountered

            Type: ParseError

            Message: syntax error, unexpected ‘Myexcel’ (T_STRING)

            Filename: C:xampphtdocsjivanistame1applicationcontrollersexcel.php

            Line Number: 47

            Backtrace:

            File: C:xampphtdocsjivanistame1index.php

            Line: 315

            Function: require_once

            /tmp directory not generat

          • ekta

            I CHANGE $R = new PHPReport($config);

            i found this error::::

            An uncaught Exception was encountered

            Type: Exception

            Message: Unable to load template file: C:xampphtdocsjivanistame1applicationcontrollers/../controllers/Myexcel.xlsx

            Filename: C:xampphtdocsjivanistame1applicationlibrariesPHPReportPHPReport.php

            Line Number: 200

            Backtrace:

            File: C:xampphtdocsjivanistame1applicationlibrariesPHPReportPHPReport.php

            Line: 183

            Function: loadTemplate

            File: C:xampphtdocsjivanistame1applicationlibrariesPHPReportPHPReport.php

            Line: 157

            Function: init

            File: C:xampphtdocsjivanistame1applicationcontrollersexcel.php

            Line: 38

            Function: __construct

            File: C:xampphtdocsjivanistame1index.php

            Line: 315

            Function: require_once

            ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

            when i change

            $templateDir = __DIR__ . “/../controllers/”;

            To this

            $templateDir = __DIR__ . “..controllers”;

            ERROR Display This>>>>>>

            An uncaught Exception was encountered

            Type: ParseError

            Message: syntax error, unexpected ‘Myexcel’ (T_STRING)

            Filename: C:xampphtdocsjivanistame1applicationcontrollersexcel.php

            Line Number: 51

            Backtrace:

            File: C:xampphtdocsjivanistame1index.php

            Line: 315

            Function: require_once

            AND NOTGENERATE ANY DIRECTORY

          • C:xampphtdocsjivanistame1applicationcontrollers/../controllers/Myexcel.xlsx, this is incorrect Path. Please provide existing path

          • ekta

            Parse error: syntax error, unexpected ‘Myexcel’ (T_STRING) in C:xampphtdocsektajivanistame1applicationcontrollersexcel.php on line 48

            Line number 48 have this line:::::::

            $output_file_excel = $output_file_dir . “Myexcel.xlsx” ;

          • Please provide correct path of Myexcel.xlsx. Can you mail your code at anand.abhay1910@gmail.com

          • ekta

            ya sure its done

          • Nice, Glad its worked

          • ekta

            no in my system not worked :(,i use below version
            codeigniter version:3.1.2
            PHP version: 5.6.28
            what is wrong?

          • Its your excel problem

          • ekta

            i used excel 2013 ,can i change lower version ?

          • ekta

            can i change my excel version??,i used 2013

          • you can use Excel2007

      • ekta
        • What is the problem, you did face. This tutorial helps to generate Excel file in your given directory. Please check your dirctory

      • ekta
  • Prafful Panwar Mali

    is it PHPExcel ?

  • rahul

    When i run this code its through an error:– Please reply as soon as…..

    Fatal error: ‘break’ not in the ‘loop’ or ‘switch’ context in C:xampphtdocsissuetrackerapplicationlibrariesPHPExcelCalculationFunctions.php on line 582
    A PHP Error was encountered

    Severity: Compile Error

    Message: ‘break’ not in the ‘loop’ or ‘switch’ context

    Filename: Calculation/Functions.php

    Line Number: 582

    Backtrace:

    • I think, you are using PHP7.0. You need to open Functions.php file and comment break() at line 582

      • rahul

        After comment break, i get this error MSG…
        An uncaught Exception was encountered

        Type: PHPExcel_Writer_Exception

        Message: Could not open /tmp/Myexcel.xlsx for writing.

        Filename: C:xampphtdocsissuetrackerapplicationlibrariesPHPExcelWriterExcel2007.php

        Line Number: 241

        Backtrace:

        File: C:xampphtdocsissuetrackerapplicationlibrariesPHPReport.php
        Line: 975
        Function: save

        File: C:xampphtdocsissuetrackerapplicationlibrariesPHPReport.php
        Line: 933
        Function: renderXlsx

        File: C:xampphtdocsissuetrackerapplicationcontrollersExcel.php
        Line: 55
        Function: render

        File: C:xampphtdocsissuetrackerindex.php
        Line: 315
        Function: require_once

  • rahul

    I saved the PHPEXCEL library in and make a tmp folder in controller.Am i right or not?

    • tmp folder is a just example. provide your directory path

  • kvasan
    • kvasan

      my controller code…

      $template = ‘BranchAvailable_template.xlsx’;
      $templateDir = base_url().”assets/templates/”;

      //set config for report
      $config = array(
      ‘template’ => $template,
      ‘templateDir’ => $templateDir
      );

      • What is the error faced by you?

        • kvasan

          i placed my xls template in assets/templates/

          it says cannot be loaded (please refer my path code above…

        • kvasan

          Message: Unable to load template file:….

          my absolute path was public_html/assets/template/excelfilename.xls

    • Check your directory path of template

  • kvasan

    An uncaught Exception was encountered

    Type: Exception
    Message: Unable to load template file: http://gbscomputers.com/scope/assets/templates/BranchAvailable_template.xlsx
    Filename: /home/gbscompu/public_html/scope/application/libraries/PHPReport/PHPReport.php
    Line Number: 200

    Backtrace:
    File: /home/gbscompu/public_html/scope/application/libraries/PHPReport/PHPReport.php
    Line: 183

    Function: loadTemplate
    File: /home/gbscompu/public_html/scope/application/libraries/PHPReport/PHPReport.php
    Line: 157

    Function: init
    File: /home/gbscompu/public_html/scope/application/controllers/Purchaseditems.php
    Line: 140

    Function: __construct
    File: /home/gbscompu/public_html/scope/index.php
    Line: 315

    Function: require_once

  • Deva Sri

    how to download excel in downloads folder in local machine

    • Please provide your directory path in variable $output_file_excel, like $output_file_excel = “YOUR DIRECTORY PATH”

  • tgichuhi

    Thanks for this tutorial. Very helpful

  • Joan

    I would like to show the Total count alone in last line of the excel.. How can i show?? I have the total count details but when i display it is added in each row. Can anyone help me out pls???

    • IF you want to add single field then use repeat false and if you want to load in loop then use repeat true.
      all id add into array

  • Joan

    I have found the solution for the question was queried by me.. It works for me please make use of this..

    $R = new PHPReport($config);
    $R->load(
    array(
    [ ‘id’ => ‘id1’,
    ‘repeat’ => TRUE,
    ‘data’ => $data1
    ],
    [
    ‘id’ => ‘id2’,
    ‘repeat’ => FALSE,
    ‘data’ => $data2
    ]
    )
    );

  • karthik

    howto set template directory path

  • karthik

    i Had to download Php Report library but its does not working