Kategori
PHP

Export from Database to Excel File in the CodeIgniter

Case Study : Export from Database (Mysql) to Excel File in the CodeIgniter

Requirements : Webserver Package, already installed. CodeIgniter.

Export data is needed if we are developing an application, especially for the office applications. Most of the offices are still using excel to manage their data, because Excel is one of the earliest applications used to manage office data.

Step 1 : CI Configuration

  1. Open the config.php file located in the system-application-config
  2. Change base_url, adjust according where your ci folder are. FOr example: your ci folder located in www/ci folder
    so change the line $config['base_url']="http://example.com/";
    with
    $config['base_url']="http://localhost/ci";
  3. Setting the database. Open file database.php located in the same folder as config.php. Change hostname, username, password, and the database name according to your mysql setting. For example :
    $db['default']['hostname'] = "localhost";
    $db['default']['username'] = "root";
    $db['default']['password'] = "";
    $db['default']['database'] = "db_tutorial";

Step 2 : Prepare the Database

  1. Make a database named db_tutorial.
  2. Prepare a table named tb_book (for table’s structure, see picture below)
  3. Insert some sample data, for example insert some sample data just like the picture below
  4. Ok, we’re done with database !

Step 3 : Create the Controller

  1. Type the following script,
    [sourcecode language=”php”]
    <?php
    class Buku_con extends Controller {
    public function Buku_con() {
    parent::__construct();
    $this->load->model(‘buku_model’);//load the model
    $this->load->library(‘pagination’);
    }
    //function to get the data from tb_book
    function getBuku() {
    $data[‘title’] = ‘menampilkan isi buku’;
    $data[‘detail’] = $this->buku_model->getBuku();//call the model and save the result in $detail
    $this->load->view(‘buku_view’, $data);
    }
    //function to export to excel
    function toExcelAll() {
    $query[‘data1’] = $this->buku_model->ToExcelAll();
    $this->load->view(‘excel_view’,$query);
    }
    }
    ?>[/sourcecode]
  2. Save with the name buku_con.php in the system-application-controllers folder
  3. Penjelasan :See at the script’s comment.

Step 4 : Create the Model

  1. Type the following script,
    [sourcecode language=”php”]
    <?php
    class Buku_model extends Model {
    function Buku_model() {
    parent::Model();
    }
    function getBuku() {
    $this->db->select(‘*’);
    $this->db->from(‘tb_book’);
    $this->db->order_by(‘id’,’DESC’);
    $getData = $this->db->get();
    if($getData->num_rows() > 0)
    return $getData->result_array();
    else
    return null;
    }
    //query for get all data
    function toExcelAll() {
    $this->db->select(‘*’);
    $this->db->from(‘tb_book’);
    $this->db->order_by(‘id’,’desc’);
    $getData = $this->db->get();
    if($getData->num_rows() > 0)
    return $getData->result_array();
    else
    return null;
    }
    }
    ?>
    [/sourcecode]
  2. Save with the name buku_model.php in the system-application-models folder

Step 5 : Create the View (View for showing the export link)

  1. Type the following script,
    [sourcecode language=”php”]
    <h4>Book Data</h4>
    <?php if(count($detail) > 0) { ?>
    <table border="1">
    <tr>
    <th>ID</th>
    <th>Title</th>
    <th>Author</th>
    </tr>
    <?php
    foreach($detail as $rows) {
    echo "<tr>";
    echo "
    <td>". $rows[‘id’]."</td>
    <td>". $rows[‘title’] ."</td>
    <td>". $rows[‘author’] ."</td>
    "; } ?>
    </table>
    <?php } ?>
    <br> <br>
    <a href=’toExcelAll’><span style=’color:green;’>Export All Data</span></a>
    [/sourcecode]
  2. Save with the name buku_view.php in the system-application-views folder
  3. Explanation: $detail taken from controller, this variabel filled with the content of tb_book (the model’s result), and then the data showed in the array form.

Step 6 : Create the View (View for setting and formatting Excel File)

  1. Type the following script,
    [sourcecode language=”php”]
    <?php
    header("Content-type: application/octet-stream");
    header("Content-Disposition: attachment; filename=exceldata.xls");
    header("Pragma: no-cache");
    header("Expires: 0");
    ?>
    <table border=’1′ width="70%">
    <tr>
    <td>ID</td>
    <td>TITLE</td>
    <td>AUTHOR</td>
    </tr>
    <?
    foreach($data1 as $item) {
    ?>
    <tr>
    <td><?=$item[‘id’]?></td>
    <td><?=$item[‘title’]?></td>
    <td><?=$item[‘author’]?></td>
    </tr>
    <? } ?>
    </table>
    [/sourcecode]
  2. Save with the name excel_view.php in the system-application-views folder

Step 7 : Testing the Code

  1. Go to http://localhost/namaFolderCIKamu/buku_con/getBuku
  2. You will see something like this,
  3. Click on the Export All Data link, the browser will automatic download the excel file
  4. Open the excel file, and you will see the content from tb_book 🙂

Happy Trying 🙂 Happy Coding 🙂

Kategori
PHP

Export dari Database ke File Excel dalam CodeIgniter

Studi Kasus : Melakukan Export data dari Database (Mysql) ke file Excel dalam CodeIgniter

Requirements : Webserver Package, already installed. CodeIgniter.

Export data sangat dibutuhkan apabila kita mendevelop aplikasi, terutama untuk aplikasi perkantoran. Sebagian besar perkantoran masih menggunakan excel untuk memanage data mereka, karena excel memang salah satu aplikasi paling awal yang digunakan untuk memanajemen data perkantoran.

Step 1 : Konfigurasi CI

  1. Buka file config.php yang berada dalam folder system-application-config-config.php
  2. Ubah base url, sesuaikan dengan lokasi dimana folder CI-mu berada (tempat ekstrak-an tadi). Contoh : Folder CI anda berada dalam folder www/ci
    maka ubah baris $config['base_url']="http://example.com/";
    dengan
    $config['base_url'] = "http://localhost/ci/";
  3. Setting database. Buka file database.php yang berada dalam folder yang sama dengan config.php. Ubah hostname, username, password, dan nama database. Sesuaikan dengan pengaturan mysql anda. Contoh :
    $db['default']['hostname'] = "localhost";
    $db['default']['username'] = "root";
    $db['default']['password'] = "";
    $db['default']['database'] = "db_tutorial";

Step 2 : Siapkan Database

  1. Buat database dengan nama db_tutorial (via phpmyadmin)
  2. Siapkan tabel dengan nama tb_book (untuk strukturnya, lihat gambar dibawah ini)
  3. Insertkan beberapa sample data, sebagai contoh insertkan data seperti tabel dibawah ini.
  4. Ok, we’re done with database !

Step 3 : Membuat Controller

  1. Ketikkan script berikut,
    [sourcecode language=”php”]
    <?php
    class Buku_con extends Controller {
    public function Buku_con() {
    parent::__construct();
    $this->load->model(‘buku_model’);//load the model
    $this->load->library(‘pagination’);
    }
    //function to get the data from tb_book
    function getBuku() {
    $data[‘title’] = ‘menampilkan isi buku’;
    $data[‘detail’] = $this->buku_model->getBuku();//call the model and save the result in $detail
    $this->load->view(‘buku_view’, $data);
    }
    //function to export to excel
    function toExcelAll() {
    $query[‘data1’] = $this->buku_model->ToExcelAll();
    $this->load->view(‘excel_view’,$query);
    }
    }
    ?>[/sourcecode]
  2. Simpan dengan nama buku_con dalam folder system-application-controllers
  3. Penjelasan :Lihat di komen script.

Step 4 : Membuat Model

  1. Ketikkan script berikut,
    [sourcecode language=”php”]
    <?php
    class Buku_model extends Model {
    function Buku_model() {
    parent::Model();
    }
    function getBuku() {
    $this->db->select(‘*’);
    $this->db->from(‘tb_book’);
    $this->db->order_by(‘id’,’DESC’);
    $getData = $this->db->get();
    if($getData->num_rows() > 0)
    return $getData->result_array();
    else
    return null;
    }
    //query for get all data
    function toExcelAll() {
    $this->db->select(‘*’);
    $this->db->from(‘tb_book’);
    $this->db->order_by(‘id’,’desc’);
    $getData = $this->db->get();
    if($getData->num_rows() > 0)
    return $getData->result_array();
    else
    return null;
    }
    }
    ?>
    [/sourcecode]
  2. Simpan dalam folder system-application-models, dengan nama buku_model.php

Step 5 : Membuat View (yang menampilkan link untuk export)

  1. Ketikkan script berikut,
    [sourcecode language=”php”]
    <h4>Book Data</h4>
    <?php if(count($detail) > 0) { ?>
    <table border="1">
    <tr>
    <th>ID</th>
    <th>Title</th>
    <th>Author</th>
    </tr>
    <?php
    foreach($detail as $rows) {
    echo "<tr>";
    echo "
    <td>". $rows[‘id’]."</td>
    <td>". $rows[‘title’] ."</td>
    <td>". $rows[‘author’] ."</td>
    "; } ?>
    </table>
    <?php } ?>
    <br> <br>
    <a href=’toExcelAll’><span style=’color:green;’>Export All Data</span></a>
    [/sourcecode]
  2. Simpan dalam folder system-application-views, dengan nama buku_view.php
  3. Penjelasan : $detail didapatkan dari controller, variabel ini berisi data dari tb_book (hasil dari model). Kemudian data ditampilkan dalam bentuk arrays.

Step 6 : Membuat View (view untuk menconvert ke excel)

  1. Ketikkan script berikut,
    [sourcecode language=”php”]
    <?php
    header("Content-type: application/octet-stream");
    header("Content-Disposition: attachment; filename=exceldata.xls");
    header("Pragma: no-cache");
    header("Expires: 0");
    ?>
    <table border=’1′ width="70%">
    <tr>
    <td>ID</td>
    <td>TITLE</td>
    <td>AUTHOR</td>
    </tr>
    <?
    foreach($data1 as $item) {
    ?>
    <tr>
    <td><?=$item[‘id’]?></td>
    <td><?=$item[‘title’]?></td>
    <td><?=$item[‘author’]?></td>
    </tr>
    <? } ?>
    </table>
    [/sourcecode]
  2. Simpan dalam folder system-application-views, dengan nama excel_view.php

Step 7 : Testing Code

  1. Buka browser, pergi ke http://localhost/namaFolderCIKamu/buku_con/getBuku
  2. Anda akan melihat tampilan seperti berikut,
  3. Klik pada link Export to excell, maka browser akan otomatis mendownload file excel,
  4. Buka file excel yang telah di download, maka data dari tb_book akan ditampilkan 🙂

Silahkan mencoba 🙂 Happy Coding 🙂