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
$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:
"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.
$csv->addField('Adam');
$csv->addField('Royle');
$csv->addField('M');
$csv->addField('Brisbane');
$csv->endRow();
If you don't want to render headers simply use:
$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:
<?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);
}
}
?>
In definition, Helpers should be used from the view.
When using the helper from the view, it doesn't happen.
If you want to use the helper within a controller, you will have to initiate the inner-helper in the parent helper, this way:
App::import('Helper','csv');
$csv = new csvHelper();
cheers
(...)
if ($to_encoding) {
$output = (iconv("ISO-8859-2", "windows-1250",iconv("UTF-8","ISO-8859-2",$output)));
(...)
And one additional thing
your input data (two leading chars) cannot start from string "ID" as Excel tries to interpret the file as some kind of SYLK format and echos errors on display.
hope that will be helpful
I am a newbie on cakephp. I want to put a download link on one of my view, so when user click on that link it will automatically generate a csv file and pop up for user to download.
I followed the instruction here http://blog.allmythingstodo.com/tag/cakephp/ and used this helper.
However, when I click on my link.. this is shown:
Notice (8): Undefined variable: csv [APP\controllers\dispatches_controller.php, line 158]
Fatal error: Call to a member function addRow() on a non-object in C:\xampp\htdocs\cake\app\controllers\dispatches_controller.php on line 158
Can anyone please help? I'm not sure where the problem is. Thank you.
Every comment in here helped me to start writing a code which exports data to an excel sheet. I would like to have button or link on Index.ctp which clicked on must export the information that is present in the Index.ctp to an excel or pdf or any other document which can display images
kindly help/guide me....
I have
Controller
Model
Views- add.ctp
- edit.ctp
- index.ctp
- view.ctp
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
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
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.
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
Rendering to UTF-8 indeed doesn't work, but UTF-16 does. I used:
echo $csv->render('Export.csv', 'UTF-16', 'auto');
and it works just fine.
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" :)
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
I have the same problem? Did you get a solution on this?
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.
Hi ,
I am still facing this problem(excel containing the web page) even after adding the above 2 lines to my controller. Could you let me know if you have made any other modifications?
Regards,
Naveed
Same problem. I found spaces/line-breaks outside of the php tags and I deleted them.
Now it works.. thanks!!
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");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/).
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);
}
}
?>
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.
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?
Hi Richard, you need to use this helper from one of your views.
I get this. I am using PHP 4. Like the idea of it. Plan on using it. Do you have any help?
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.
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.
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;I have to release something Monday. I wouldn't mind testing it now if you post it. christian at pearcec dot com
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)...
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.