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.

Download Code


Don't Miss

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


111 COMMENTS

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

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

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

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

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

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

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

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

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

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

  8. 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:

      • 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

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

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

  11. 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
    ]
    )
    );

  12. When I am downloading the file created with the script and opening in OpenOffice , its giving me the error that file is corrupt and can not be open. When I am clicking on Yes button for confirmation to open the file its giving other error i.e. Read Error. This file is opening in LibreOffice and MS Excel (with same warning but its opening).

    If anyone has idea, please let me know how to resolve the issue.

    Attaching the screen shots. https://uploads.disquscdn.com/images/294e1f55c7ad9751dc8e828bc693dfad1aad190d89bba267ccd16132d790a0be.jpg https://uploads.disquscdn.com/images/ceb7ee74f3eb5218d28c55eff19b9154db6e28497bc1fb0fef60d47df1514e7d.jpg

  13. I am trying to use this library with Codeigniter 3, but it gives me error:

    ================================================================
    Fatal error: Class ‘PHPExcel_Style_Fill’ not found in C:xampphtdocstestsystemcoreLoader.php on line 1292
    A PHP Error was encountered

    Severity: Error
    Message: Class ‘PHPExcel_Style_Fill’ not found
    Filename: core/Loader.php
    Line Number: 1292
    Backtrace:

    =================================================================

      • To download in local Machine use below code:

        header(‘Content-Description: File Transfer’);
        header(‘Content-Type: application/octet-stream’);
        header(“Content-Disposition: attachment; filename=”YOUR FULL FILE PATH””);
        readfile(TMP_FOLDER . $invoice_id . ‘.zip’);

        • Thanks for giving your time.
          I add this code in my file. but it did not show my data. it show my file source code.
          I have add this code after $result = $R->render(‘excel’, $output_file_excel);

        • Hi Abhay,
          Thanks for code.
          I made some change into my code & it works for me
          the Code is
          $output_file_excel = $output_file_dir . “Myexcel.xls”;
          //download excel sheet with data in /tmp folder
          $result = $R->render(‘excel’, $output_file_excel);

          force_download($output_file_excel, NULL);