CSV Helper (PHP 5)

by ifunk
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);
    }
}

?>

Report

More on Helpers

Tags

Advertising

Comments

  • mimyabu posted on 04/29/11 06:54:49 AM
    Hi. thanks for this helper. i've used it on my project w/o a problem on localhost. but when i uploaded it on site, the popup for file download doesnt appear but instead open the export page showing the content of csv file. can someone help me please? am i missing something?
  • noamnativ posted on 01/06/11 05:55:02 AM
    Note that this occurs only when you are using the helper inside a controller.
    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
  • salvin posted on 06/28/10 08:59:59 AM
    for all these having problem opening the file in Excel for polish this is what i had to do to make it work propperly (that way might be useful for other languages too)

    (...)
            if ($to_encoding) {
                $output = (iconv("ISO-8859-2", "windows-1250",iconv("UTF-8","ISO-8859-2",$output)));
    (...)
    double change of the charset keeps letters display corectly ( direct conversion from utf-8 to windows-1250 looses chars.

    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
  • codebuzz posted on 11/24/09 10:57:47 PM
    Hi,

    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.
  • navy_coolguy posted on 10/13/09 09:25:27 AM
    Hello Everyone,
    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
  • icc97 posted on 06/18/09 06:48:05 AM
    Worked beautifully, thank you.

    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
  • Hunanyan posted on 05/07/09 03:19:21 AM
    Hi Adam, first wanna thank you for a wonderfull 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
    • ifunk posted on 05/07/09 04:24:30 AM
      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?

      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.
      • Hunanyan posted on 05/07/09 07:16:11 AM
        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?

        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
        • Avariel posted on 12/01/09 05:35:06 AM
          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
          Might be a bit late, but I think I worked it out.
          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.
  • godjen99 posted on 07/30/08 12:47:31 PM
    First, nice helper. Very easy and stright forward to use. Thanks!

    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" :)
  • Vasiok posted on 07/30/08 04:52:15 AM
    Well i am trying to create a link on the view/..../..thtml

    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

  • bikes302 posted on 07/20/08 05:28:17 PM
    Thanks a lot for this helper. This makes generating spreadsheets so much easier. Using the latest version Adam posted on PHP4. Thanks for the rewrite!
  • scrapmetalca posted on 07/03/08 02:41:57 PM
    I got Adam's stuff from above and have nearly an identical view file and findAll call. However, the csv file I get has the contents of the web page instead of the csv data. Anyone know why?
    • draikin posted on 10/02/08 11:38:10 AM
      I got Adam's stuff from above and have nearly an identical view file and findAll call. However, the csv file I get has the contents of the web page instead of the csv data. Anyone know why?
      I have the same problem? Did you get a solution on this?
      • draikin posted on 10/02/08 01:25:09 PM
        I got Adam's stuff from above and have nearly an identical view file and findAll call. However, the csv file I get has the contents of the web page instead of the csv data. Anyone know why?
        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.
        • naveed posted on 03/30/10 10:10:24 AM
          I got Adam's stuff from above and have nearly an identical view file and findAll call. However, the csv file I get has the contents of the web page instead of the csv data. Anyone know why?
          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
          • furl posted on 06/10/10 04:40:10 PM
            I got Adam's stuff from above and have nearly an identical view file and findAll call. However, the csv file I get has the contents of the web page instead of the csv data. Anyone know why?
            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!!
  • Xanadu posted on 06/05/08 03:10:17 AM
    First of all, thanks for this code.

    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");
  • ifunk posted on 02/21/08 04:39:51 PM
    I had to make this work with PHP4 for a current project, in case anyone is interested. I took out the encoding stuff and php5 tempfile buffer, and replaced fputcsv with something else. It just buffers it to a string, then dumps it at the end. Works well for what I am doing.
    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/).
  • sdc53 posted on 02/21/08 01:16:38 PM
    I had to make this work with PHP4 for a current project, in case anyone is interested. I took out the encoding stuff and php5 tempfile buffer, and replaced fputcsv with something else. It just buffers it to a string, then dumps it at the end. Works well for what I am doing.

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

    ?>
  • aaron posted on 02/13/08 09:26:36 AM
    looks like this thread hasn't been updated in a while... nonetheless, i just stumbled upon this great helper. I was trying to write similar functionality into my app, and this has done most of the trick so far.

    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.
  • gr8whitesavage posted on 09/18/07 02:58:13 PM
    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?
    • ifunk posted on 09/18/07 04:36:42 PM
      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.
  • pearcec posted on 09/14/07 09:06:59 PM
    Warning: fopen(php://temp/maxmemory:5242880): failed to open stream: Success in

    I get this. I am using PHP 4. Like the idea of it. Plan on using it. Do you have any help?
    • ifunk posted on 09/14/07 11:51:21 PM
      Warning: fopen(php://temp/maxmemory:5242880): failed to open stream: Success in

      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.
      • ifunk posted on 09/15/07 10:09:34 PM
        I have added PHP4 and CakePHP 1.1.x.x compatibility on my dev box for this class, and also added some more features. I will post as soon as I refine my method/variable names.
        • danmac posted on 10/23/07 04:42:17 PM
          I have added PHP4 and CakePHP 1.1.x.x compatibility on my dev box for this class, and also added some more features. I will post as soon as I refine my method/variable names.
          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.
          • ifunk posted on 10/23/07 06:30:43 PM
            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;
        • pearcec posted on 09/16/07 09:01:57 AM
          I have added PHP4 and CakePHP 1.1.x.x compatibility on my dev box for this class, and also added some more features. I will post as soon as I refine my method/variable names.
          I have to release something Monday. I wouldn't mind testing it now if you post it. christian at pearcec dot com
  • nikchankov posted on 09/11/07 07:57:05 AM
    when I start with Multilang in Cake 1.2 in feb 2007 in Google group I spoke with some of the project owners and they said that parser for __() content is not available. Then I wrote one my self -quick and dirty, of course which I'm using still. Unfortunately I never find an article how to use cake extract and when cake extract appear in the cake script. Recently I notice that there is extract option, but I thought it's something for extracting files or similar. I will give a try with it other than that - I am using the same approaches which you describe in that great article.
  • francky06l posted on 09/10/07 03:55:32 PM
    Nice well done, just a potential modification (I need to deal with it sometimes).
    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)...
    • ifunk posted on 09/10/07 06:40:36 PM

      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.

login to post a comment.