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.

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.