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,
    <?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);
    }
    }
    ?>
  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,
    <?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;
    }
    }
    ?>
    
  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,
    <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>
    
  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,
    <?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>
    
  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 :)

Hal yang juga menarik:

Hak Cipta

Semua skrip dan teknik dalam artikel di itx.web.id boleh digunakan sebagaimana kehendakmu tanpa perlu mencantumkan sumber. Kamu tidak boleh mengkopi seluruh artikel, dalam Bahasa Indonesia ataupun diterjemahkan ke dalam bahasa lain.


Related Articles:

Artikel terkait:

: PHP

About the author

obviously, a girl. with sweet smile, off course. turning her 21 years trapped on Informatics departement and just started to having some crush with it lately. she uses wordpress. she loves php. she lo

8 Comments

Tinggalkan Balasan

Alamat surel Anda tidak akan dipublikasikan. Ruas yang wajib ditandai *

x( X-( B-) ;-) :wink: :twisted: :roll: :oops: :mrgreen: :lol: :idea: :evil: :cry: :arrow: :D :?: :-| :-x :-o :-P :-D :-? :-)) :-) :-( :-& :) :( :!: 8-O 8-) 8) (Русский) (yahoo) (worship) (woot) (wave) (unsure) (tongue) (thinking) (tears) (taser) (smileydance) (sleeping) (sick) (scenic) (rofl) (rock) (party) (panic) (okok) (nottalking) (ninja) (music) (muscle) (muhaha) (money) (mmm) (lonely) (lol) (lmao) (idiot) (hungry) (highfive) (heart_beat) (heart) (headspin) (hassle) (haha) (gym) (griltongue) (goodluck) (girlkiss) (funkydance) (fish_hit) (eyeroll) (evilsmirk) (evil_grin) (drinking) (doh) (devil) (dance) (cry) (cozy) (coffee) (brokenheart) (bringit) (blush) (bigeyes) (beer) (banana_rock) (banana_ninja) (banana_cool) (applause) (annoyed) (angry) (K) (: