Exporting data as CSV format using csv helper at CakePHP


Save the following file as csv.php into your app/views/helpers/ directory
Helper Class:



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

?>


Add this Helper to your controller:


 var $helpers = array('Html', 'Form','Csv');

Then create a method “download” at controller:


  function download(){
	$this->set('orders', $this->Order->find('all'));
	$this->layout = null;
	$this->autoLayout = false;
	Configure::write('debug', '0');
  }

at index.ctp,put this link:


 <?php echo $html->link('Download Data','download');?>

In download.ctp view file put the below code :


 <?php
	$line= $orders[0]['Order'];
	$csv->addRow(array_keys($line));
	foreach ($orders as $order){
	  $line = $order['Order'];
	  $csv->addRow($line);
	}
	$filename='orders';
	echo $csv->render($filename);
  ?>

This example is to write an array to a file with csv extension


 $list = array (
	array('aaa', 'bbb', 'ccc', 'dddd'),
	array('123', '456', '789'),
	array('"aaa"', '"bbb"')
  );
  $fh = fopen(WWW_ROOT."export.csv", 'w') or die("can't open file");
  foreach($list as $row){
	// write csv line to file
	fputcsv($fh, $row);
  }

 //close handle
 fclose($fh);

Read CSV file in CakePHP

http://code.google.com/p/parsecsv-for-php/downloads/detail?name=parsecsv-0.3.2.zip

Now copy the main class file to your app/vendors/ and name it parsecsv.php.

Now the above file will be look liked that :


 function download(){
	App::import("Vendor","parsecsv");
	$csv = new parseCSV();
	$filepath = WWW_ROOT."export.csv";
	$csv->auto($filepath);
	$this->set('orders',$csv->data);
	$this->layout = null;
	$this->autoLayout = false;
	Configure::write('debug', '0');
 }

					
Advertisements

9 responses to “Exporting data as CSV format using csv helper at CakePHP

  1. Sorry, I am working for this function. I made export csv file function successfully, but when I try to import again. It was failed. I am new in CakePHP too. So would you like to guide me how to make the import function. I am using 1.3. More thanks if it’s possible.

    Like

    • Thanks for visiting and comment to my site. as far as my concern may be you are trying to import large csv file with big size and thousands of rows. so the csv function can be failed. to load large amount of data you can use “LOAD DATA” MySQL statement. you can find help vising this link: https://dev.mysql.com/doc/refman/5.0/en/load-data.html. You can also do google to know about “LOAD DATA”. you can also increase php execution time from php.ini file. thanks

      Like

  2. could we modify the codes so when a specific function triggered, the system will generate a csv file and then auto download it to a specific folder on the server?
    i’m still using cakephp 1.3…
    many thanks!

    Like

  3. Thanks for this helpful post Ziyed. Btw, there is an extra row at the start of csv file when using your code, how do i remove it? Thanks in advance

    Like

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