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

 
 


The following two tabs change content below.
Abhay Anand has experience in the field of Software Development. He can program Android, PHP, JavaScript, Html, Css. He develops web and Android applications. You can reach him at anand.abhay1910@gmail.com
  • 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