I want to integrate Excel/CSV Export option with my CakePHP website, any idea about available components or helpers?
Thanks !
This solution is for CakePHP 2.0.. u can also integrate it in CakePHP 1.3
Step 1: Save the following file as Csv.php into 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: Add this Helper to your controller:
var $helpers = array('Html', 'Form','Csv');
Step 3: create a method “download” at controller for eg. homes_controller.php
<?php
function download()
{
$this->set('orders', $this->Order->find('all'));
$this->layout = null;
$this->autoLayout = false;
Configure::write('debug', '0');
}
?>
Step 4: put this link on page from where you have to download CSV
<?php
echo $this->Html->link('Download',array('controller'=>'homes','action'=>'download'), array('target'=>'_blank'));
?>
Step: 5 (final step)
Put this code on View/Homes/download.ctp
<?php
$line= $orders[0]['Order'];
$this->CSV->addRow(array_keys($line));
foreach ($orders as $order)
{
$line = $order['Order'];
$this->CSV->addRow($line);
}
$filename='orders';
echo $this->CSV->render($filename);
?>
Recently, I've worked in some projects that required exporting data in XLS and CSV. These helpers work really nice:
http://bakery.cakephp.org/articles/ifunk/2007/09/10/csv-helper-php5
http://bakery.cakephp.org/articles/wasenbr/2007/04/12/excel-xls-helper
There is an article in the bakery about using PHPExcel within cakePHP. This library provides options for writing spreadsheet data in a number of different formats. Examples given in the linked bakery article are for xls and xlsx files, but csv is also an option.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With