Dec 29, 2013

Ketan Patel

Export Data into Excel or CSV file in CakePHP

Once I was working on cakePHP demo project in which I need to export the data to CSV format. As it was specific to cakephp I have to find the way how to accomplish this. So for that I googled and found one easy and effective solution that I am going to share with you. I hope you find it helpful.

Export Data into CSV file in Cakephp





In this tutorial we are using one helper class called ‘CsvHelper.php’.If you are new in CakePHP then you can refer this link to know about Helpers in CakePHP.

http://book.cakephp.org/2.0/en/views/helpers.html

You just have to simply follow the steps and you’ll get the result in no time.So lets begin the process.


Step :1 Save the following content in the file named as CsvHelper.php your app/View/Helper directory.
<?php
class CsvHelper extends AppHelper
{
var $delimiter = ',';
var $enclosure = '"';
var $filename = 'Export.csv';
var $line = array();
var $buffer;

function CsvHelper()
{
    $this->clear();
}
function clear() 
{
    $this->line = array();
    $this->buffer = fopen('php://temp/maxmemory:'. (5*1024*1024), 'r+');
}

function addField($value) 
{
    $this->line[] = $value;
}

function endRow() 
{
    $this->addRow($this->line);
    $this->line = array();
}

function addRow($row) 
{
    fputcsv($this->buffer, $row, $this->delimiter, $this->enclosure);
}

function renderHeaders() 
{
    header('Content-Type: text/csv');
    header("Content-type:application/vnd.ms-excel");
    header("Content-disposition:attachment;filename=".$this->filename);
}

function setFilename($filename) 
{
    $this->filename = $filename;
    if (strtolower(substr($this->filename, -4)) != '.csv') 
    {
        $this->filename .= '.csv';
    }
}

function render($outputHeaders = true, $to_encoding = null, $from_encoding ="auto") 
{
    if ($outputHeaders) 
    {
        if (is_string($outputHeaders)) 
        {
            $this->setFilename($outputHeaders);
        }
        $this->renderHeaders();
    }
    rewind($this->buffer);
    $output = stream_get_contents($this->buffer);

    if ($to_encoding) 
    {
        $output = mb_convert_encoding($output, $to_encoding, $from_encoding);
    }
    return $this->output($output);
}
}
?>

Step:2 Adding Helper in your controller.

Add the following line in the controller for which you want to use this helper to export the data.
var $helpers = array('Html', 'Form','Csv'); 

Step:3 Create Export method in the controller(Example: PostController).
function export()
{
    $this->set('posts', $this->Post->find('all'));
    $this->layout = null;
    $this->autoLayout = false;
    Configure::write('debug','0');
}



Note: One thing to note here, you can use any of findTypes or custom query. But in each type you need to check the array key in the result array. They can be different. To know more about findtypes, data retrieving methods and their return array structure please click here


Example

If you use $this->Post->find('all');  then the result array has Model name as array key (Here Post is Model name). Result array would be like this:


Array
(
    [0] => Array
        (
            [Post] => Array
                (
                    [id] => 1
                    [title] => this is title one
                    [description] => this is description one
                )

        )

    [1] => Array
        (
            [Post] => Array
                (
                    [id] => 2
                    [title] => this is title two
                    [description] => this is description two
                )

        )

)

But if you use custom query like $this->Post->query('select * from posts'); then the result array would be different. It has table name as array key in the result array.(Here posts table name is as a array key)


Array
(
    [0] => Array
        (
            [posts] => Array
                (
                    [id] => 1
                    [title] => this is title one
                    [description] => this is description one
                )

        )

    [1] => Array
        (
            [posts] => Array
                (
                    [id] => 2
                    [title] => this is title two
                    [description] => this is description two
                )

        )

)
So, according to the data retrieving method you have used, you need to make changes in the next files.  

Step:4 Create file named export.ctp in app/View/ your controller(Posts here) folder and add the following code.
<?php

$line= $posts[0]['Post'];
$this->CSV->addRow(array_keys($line));
 foreach ($posts as $post)
 {
   $line= $post['Post']; 
   $this->CSV->addRow($line);
 }
 $filename='posts';
 echo  $this->CSV->render($filename);
?>

Step:5 Create the export data link.

<?php echo $this->Html->link('Export',array('controller'=>'posts','action'=>'export'), array('target'=>'_blank'));?>
That’s it you’re Done.

Ketan Patel

About Ketan Patel -

I have developed a wide range of websites using CorePHP, Opencart, CakePHP and CodeIgniter including sites for startup companies and small businesses. Apart from my blogging life, I like to read Novels, Listening music and Net surfing.

Subscribe to this Blog via Email :