CSV Helper (PHP 5)
I was recently outputting some comma-delimited data and thought I would benefit from a simple csv helper. I hope this helper benefits someone else as well. This is my first submission so please give any criticism.
This CSV helper enables you to output data in a csv format. It can automatically output the necessary headers so that it will open in Microsoft Excel.
Sample Usage
Download code
$line = array('First Name', 'Last Name', 'Gender', 'City');
$csv->addRow($line);
$line = array('Adam', 'Royle', 'M', 'Brisbane');
$csv->addRow($line);
$line = array('Skrimpy', 'Bopimpy', 'M', 'North Sydney');
$csv->addRow($line);
$line = array('Sarah', 'Jincera"s', 'F', 'Melbourne');
$csv->addRow($line);
echo $csv->render('Subscribers.csv');
This will output the following:
Download code
"First Name","Last Name",Gender,City
Adam,Royle,M,Brisbane
Skrimpy,Bopimpy,M,"North Sydney"
Sarah,"Jincera""s",F,Melbourne
Instead of the addRow() method, you can also add one field at a time.
Download code
$csv->addField('Adam');
$csv->addField('Royle');
$csv->addField('M');
$csv->addField('Brisbane');
$csv->endRow();
If you don't want to render headers simply use:
Download code
$csv->render(false);
Save the following file as csv.php into your app/views/helpers/ directory, and include 'Csv' in your $helpers array in your controller.
Helper Class:
Download code
<?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);
}
}
?>
Comments
Comment
1 optional iconv call
When you have real UTF-8 language, you can come up with a CSV that is not readable under windows (ie : majority of people use excel for viewing csv). So maybe 2 parameters for an optional call to iconv or mb_convert in order to convert the encoding (ie UTF-8 CP1251 -> Cyrillic for example)...
Comment
2 Re optional iconv call
Thanks for your comments franck. I've never had to use UTF-8 so I've never run into these issues. I've made some modifications to the render() method. Let me know if this looks good to you or if there is anything you would change.
Question
3 Where did you find information about cake extract
Bug
4 Warning fopen
I get this. I am using PHP 4. Like the idea of it. Plan on using it. Do you have any help?
Comment
5 Re warning fopen
Hi Christian, yes the above code will only work in PHP 5 (as mentioned in the title). It utilises a few PHP5 functions such as fputcsv(), fopen() in temp memory and stream_get_contents().
I don't plan on converting it to PHP4 as I no longer use it, but will consider if enough people want me to. So cast your votes people.
Comment
6 PHP4 compatibility
Comment
7 Test it
I have to release something Monday. I wouldn't mind testing it now if you post it. christian at pearcec dot com
Comment
8 Undefined variable csv
Notice: Undefined variable: csv in /home/rwkelly/public_html/cake/app/controllers/users_controller.php on line 174
Fatal error: Call to a member function addField() on a non-object in /home/rwkelly/public_html/cake/app/controllers/users_controller.php on line 174
I'm getting these errors when I follow this tutorial. I added 'Csv' to the $helpers array. I added the helper file to the proper place. I also tried setting $csv = new CsvHelper(); but that didn't help at all. What have I missed?
Comment
9 Re undefined variable csv
Hi Richard, you need to use this helper from one of your views.
Comment
10 Re PHP4 compatibility
Any news on this? I have a 1.1 project that I need to deploy on a server with PHP4. This class would be really handy for it.
Comment
11 PHP4 Compatibility
Please see my updated class below. There is a new method called addGrid() which accepts data in the same format as returned by $model->findAll(), automatically adding field names as the first row (this behaviour can be overridden in the second argument).
http://ifunk.net/cakephp/helpers/csv.php.txt
Here is an example (obviously you need an "orders" table and an Order model for this to work):
Controller Class:
<?php class OrdersController extends AppController {function view() {
$this->set('data', $this->Order->findAll());
}
}
?>
View Template:
<?php
$csv->addGrid($data);
echo $csv->render();
?>
Would be great if I could get some feedback on this class. Once it is more tested I will write up and post a detailed tutorial, with info on how to attach to an email using phpmailer, etc.
Also, you may notice that Excel automatically removes zeroes from the beginning of a numeric string. I have added in known workaround for this behaviour, but it only works in Excel so it should be used only when needed. Eg.
$csv->preserveLeadingZerosInExcel = true;Comment
12 Still active question mark
It would be even more awesome if we could figure out a way to include associated model data into the report as well - e.g. for Orders: Product info, Customer info, etc.
Obviously there are many potential pitfalls, but I think it could be done well.
Let me know if anyone is interested or if someone has begun this already.
Comment
13 I rewrote this for PHP4
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 = '';
}
function makecsv($fields = array(), $delimiter = ',', $enclosure = '"') {
$str = '';
$escape_char = '\\';
foreach ($fields as $value) {
if (strpos($value, $delimiter) !== false ||
strpos($value, $enclosure) !== false ||
strpos($value, "\n") !== false ||
strpos($value, "\r") !== false ||
strpos($value, "\t") !== false ||
strpos($value, ' ') !== false) {
$str2 = $enclosure;
$escaped = 0;
$len = strlen($value);
for ($i=0;$i<$len;$i++) {
if ($value[$i] == $escape_char) {
$escaped = 1;
} else if (!$escaped && $value[$i] == $enclosure) {
$str2 .= $enclosure;
} else {
$escaped = 0;
}
$str2 .= $value[$i];
}
$str2 .= $enclosure;
$str .= $str2.$delimiter;
} else {
$str .= $value.$delimiter;
}
}
$str = substr($str,0,-1);
$str .= "\n";
return ($str);
}
function addField($value) {
$this->line[] = $value;
}
function endRow() {
$this->addRow($this->line);
$this->line = array();
}
function addRow($row) {
$this->buffer .= $this->makecsv($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) {
if ($outputHeaders) {
if (is_string($outputHeaders)) {
$this->setFilename($outputHeaders);
}
$this->renderHeaders();
}
return $this->output($this->buffer);
}
}
?>
Comment
14 PHP4
I extended this helper a few days after I submitted the article with PHP4 compatibility and a few new features.
http://ifunk.net/cakephp/helpers/csv.php.txt
I will be writing a tutorial that will be placed on my new blog (http://blogs.bigfish.tv/adam/).
Comment
15 HTTP Headers
I have found just one bug (or flaw). In Opera it automagicallly renamed the file to have a .xls extension.
In order to fix this, I have changed the response header Content-Type to 'application/csv' (instead of 'application/vnd.ms-excel'), which is a more general mimetype declaration. It worked in IE7, Firefox 2 and Opera 9!
The code:
header("Content-Type: application/csv");Comment
16 Getting the web page instead of the data in the csv file
Comment
17 Awesome
Comment
18 trying to create the csv with a button help please
in the thtml file i write just :
">click to execute csv
if ( isset($execute) ){
myfunction();
}
?>
and in the controller
function myfunction(){
echo $csv->render('Subscribers.csv');
$csv = $this->Csv->findAll();
$this->set(array(
'csv' => $csv
));
}
and getting :
Notice (8): Undefined variable: csv [APP\controllers\orders_controller.php, line 171]
Code
echo $csv->render('Subscribers.csv');
Fatal error: Call to a member function render() on a non-object in C:\Program Files\a\www\dmesystem\app\controllers\orders_controller.php on line 171
help me please
Comment
19 Just a small addition to allow for spaces in file names
However, using the http://ifunk.net/cakephp/helpers/csv.php.txt code, you can't have files with spaces in the name when calling $this->csv->render().
Example
$this->csv->render( 'This is a great helper.csv' ).So to fix this, I changed some code on my end. In the renderHeaders function, I change the line
header("Content-disposition:attachment;filename=".$this->filename);
to this
header( "Content-disposition:attachment;filename=\"{$this->filename}\"" );Now you can have a file name like "This is a super.csv" :)
Comment
20 Getting the web page instead of the data in the csv file
I have the same problem? Did you get a solution on this?
Comment
21 Getting the web page instead of the data in the csv file
I found the solution on this in a Newsgroup article.
In the controller method you must add the following:
$this->layout = null;
$this->autoLayout = false;
Now the data are in the file not the HTML page.
Comment
22 Thanks! Thanks! Thanks!
I have tried many libreries and helps, but always with problems in exporting excel(CSV) files.
Your code is working and exporting datas into the CSV file, but always puts datas starting with 6-th line, putting 5 empty lines at the begining of the file.
Can you tell me why please?
Thanks a lot
Comment
23 Check spaces in your php files
I suspect you've got spaces/line-breaks outside of the php tags in your other php files, eg your models, views, etc. I don't think it would be the result of the csv helper.
Comment
24 problem with UTF8
Yes, the problem was in it, thanks again!
Wanna ask you another question about UTF-8, in the database have a datas in UTF-8 and need export them to the .csv(utf-8):
In the view I´m putting this:
echo $csv->render('Subscribers.csv', 'UTF-8', mb_detect_encoding($line));
but in the CSV recieve caracters like iñan.
What can I do?
thanks
Comment
25 Lovely bit of code
First I had to figure out how to create a view that didn't display any code except for the csv.
To do this I added an export function to my controller and set the layout to be 'ajax' which just happens to contain no html outside the main content. It looked something like this:
class MyController extends AppController {
$helpers = array('HTML', 'Form', 'Csv');
...
function export($id = null) {
$this->layout = 'ajax';
...
}
}
Then you create an export.ctp view with the code