codeigniter, mysql, php

Exporting your MySQL table data with PHPExcel + CodeIgniter


Exporting your MySQL table data with PHPExcel + CodeIgniter

http://www.facebook.com/plugins/like.php?channel_url=https%3A%2F%2Fs-static.ak.fbcdn.net%2Fconnect%2Fxd_proxy.php%3Fversion%3D3%23cb%3Df195a68dc416bf4%26origin%3Dhttp%253A%252F%252Fwww.dannyherran.com%252Ff100e4067be86da%26relation%3Dparent.parent%26transport%3Dpostmessage&extended_social_context=false&href=http%3A%2F%2Fwww.dannyherran.com%2F2011%2F03%2Fexporting-your-mysql-table-data-with-phpexcel-codeigniter%2F&layout=button_count&locale=en_US&node_type=link&sdk=joey&show_faces=false&width=90 http://platform.twitter.com/widgets/tweet_button.html#_=1323938558067&_version=2&count=horizontal&enableNewSizing=false&id=twitter-widget-0&lang=en&original_referer=http%3A%2F%2Fwww.dannyherran.com%2F2011%2F03%2Fexporting-your-mysql-table-data-with-phpexcel-codeigniter%2F&size=m&text=Exporting%20your%20MySQL%20table%20data%20with%20PHPExcel%20%2B%20CodeIgniter&url=http%3A%2F%2Fwww.dannyherran.com%2F2011%2F03%2Fexporting-your-mysql-table-data-with-phpexcel-codeigniter%2F&via=DannyHerran

 

PHPExcel + CodeIgniter Most of the time my clients need to download data from their database tables. Exporting to CSV is a pain in the rear for users and it leads to confusion (you know the colon and semicolon stuff). Today, I decided to make a very small controller that is portable and efficient for exporting full MySQL tables to Excel 2003 using PHPExcel and CodeIgniter.

First of all, you need PHPExcel which should be installed as a CodeIgniter library. In order to do this, you should follow the steps posted in the CodeIgniter Wiki.

Once you have PHPExcel installed and configured, make a controller exactly like this one:

Code:
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
class Table_export extends Controller {
    function __construct()
    {
        parent::Controller();
        // Here you should add some sort of user validation
        // to prevent strangers from pulling your table data
    }
    function index($table_name)
    {
        $query = $this->db->get($table_name);
        if(!$query)
            return false;
        // Starting the PHPExcel library
        $this->load->library('PHPExcel');
        $this->load->library('PHPExcel/IOFactory');
        $objPHPExcel = new PHPExcel();
        $objPHPExcel->getProperties()->setTitle("export")->setDescription("none");
        $objPHPExcel->setActiveSheetIndex(0);
        // Field names in the first row
        $fields = $query->list_fields();
        $col = 0;
        foreach ($fields as $field)
        {
            $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, 1, $field);
            $col++;
        }
        // Fetching the table data
        $row = 2;
        foreach($query->result() as $data)
        {
            $col = 0;
            foreach ($fields as $field)
            {
                $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $data->$field);
                $col++;
            }
            $row++;
        }
        $objPHPExcel->setActiveSheetIndex(0);
        $objWriter = IOFactory::createWriter($objPHPExcel, 'Excel5');
        // Sending headers to force the user to download the file
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="Products_'.date('dMy').'.xls"');
        header('Cache-Control: max-age=0');
        $objWriter->save('php://output');
    }
}

Whenever you need to export data from a MySQL table, you just need to call this controller and pass the table name as a parameter, sort of like http://www.yoursite.com/table_export/products. Obviously, you should always add some sort of security measure in order to prevent strangers from pulling all your table information. Just add some session protection to your constructor and you’re set.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s