Creating Excel Reports With CodeIgniter

In this tutorial I want to share how to create an excel report with CodeIgniter. here we will use the codeIgniter Excel Generator library. This Excel Generator is a class derived from PHPExcel. Then how to use it?Additional ingredients

Download Excel Generator here https://github.com/didanurwanda/Excel-Generator
Download PHPExcel here phpexcel.codeplex.co
CREATE TABLE IF NOT EXISTS users (
id int(3) NOT NULL AUTO_INCREMENT,
name varchar(100) NOT NULL,
sex_sex varchar(10) NOT NULL,
address varchar(200) DEFAULT NULL,
email varchar(100) DEFAULT NULL,
PRIMARY KEY(id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO users (id, name, gender, address, email) VALUES
(1, ‘Dida Nurwanda’, ‘Male’, ‘Pandeglang’, ‘[email protected]’),
(2, ‘Siwi Septi Hastuti’, ‘Women’, ‘Pandeglang’, ‘-‘),
(3, ‘Ahmad’, ‘Male’, ‘Attack’, ‘[email protected]’),
(4, ‘Saepul’, ‘Male’, ‘Lebak’, ‘[email protected]’);
Create Databases and Tables
then open the phpexcel file that was downloaded earlier then extract the classes folder to the library folder in your CodeIgniter project and rename it to “PHPExcel”. Don’t forget to also extract Excel_generator.php, of course it’s still in the library folder. if everything is set, set your database configuration then create a new controller because just an example here we use welcome.php

<?php

class Welcome extends CI_Controller {

public function __construct() {
    parent::__construct();
    $this->load->database();
    $this->load->library('Excel_generator');
}


public function index() {
    $query = $this->db->get('users');
    $this->excel_generator->set_query($query);
    $this->excel_generator->set_header(array('Name', 'Gender', 'Address', 'Email'));
    $this->excel_generator->set_column(array('name', 'gender', 'address', 'email'));
    $this->excel_generator->set_width(array(25, 15, 30, 15));
    $this->excel_generator->exportTo2007('Users Report');
}

}
Controller welcome.php
Then open your browser and open your project link, there will be a download dialog, please open the excel file. For the results as follows.

Cool isn’t it, hehe.
Actually the main script to create an excel file is just

$query = $this->db->get(‘users’);
$this->excel_generator->set_query($query);
$this->excel_generator->set_column(array(‘name’, ‘gender’, ‘address’, ’email’));
$this->excel_generator->exportTo2007(‘Users Report’);

Then what’s the difference if the script is just like above? ok I explain one by one.

$this->excel_generator->set_query($query);
Used to call queries from the database.

$this->excel_generator->set_column(array(‘name’, ‘gender’, ‘address’, ’email’));
Used to display which fields will be displayed in excel. These fields are retrieved from a database table.

$this->excel_generator->exportTo2007(‘Users Report’);
Used to export reports into .xlsx files (Excel 2007) with the name Report Users

$this->excel_generator->exportTo2003(‘Users Report’);
Used to export reports into .xls files (Excel 2003) with the name Report Users

$this->excel_generator->set_width(array(25, 15, 30, 15));
Used to add column width in excel, the number must match the number of columns you input.

$this->excel_generator->set_header(array(‘Name’, ‘Gender’, ‘Address’, ‘Email’));
Used to add a Title to each column in Excel.

$this->excel_generator->start_at(8);
Used to start report generation from line 8. This can be used to add a Title or Document Header to your report.