FPDF dengan CodeIgniter

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

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