3/27/12

Generating CSV file using CodeIgniter Framework


This post will help you to generate a CSV file using CodeIgniter. The data for the CSV File will be taken from the MySQL Database and will be put into the CSV File.

Lets Get Started


Before we start doing anything, we have make an csv_helper.php file. So follow the below steps :

1. Go to Application -> Helpers
2. Right Click and make a new php file with the name 'csv_helper'.
3. Copy the below Code into it.



<?php  if ( ! defined('BASEPATH')) exit('No direct script access allowed');

if ( ! function_exists('array_to_csv'))
{
    function array_to_csv($array, $download = "")
    {
        if ($download != "")
        {    
            header('Content-Type: application/csv');
            header('Content-Disposition: attachement; filename="' . $download . '"');
        }        

        ob_start();
        $f = fopen($download, 'wb') or show_error("Can't open php://output");
        $n = 0;        
        foreach ($array as $line)
        {
            $n++;
            if ( ! fputcsv($f, $line))
            {
                show_error("Can't write line $n: $line");
            }
        }
        fclose($f) or show_error("Can't close php://output");
        $str = ob_get_contents();
        ob_end_clean();

        if ($download == "")
        {
            return $str;    
        }
        else
        {    
            echo $str;
        }        
    }
}

if ( ! function_exists('query_to_csv'))
{
    function query_to_csv($query, $headers = TRUE, $download = "")
    {
        if ( ! is_object($query) OR ! method_exists($query, 'list_fields'))
        {
            show_error('invalid query');
        }
        
        $array = array();
        
        if ($headers)
        {
            $line = array();
            foreach ($query->list_fields() as $name)
            {
                $line[] = $name;
            }
            $array[] = $line;
        }
        
        foreach ($query->result_array() as $row)
        {
            $line = array();
            foreach ($row as $item)
            {
                $line[] = $item;
            }
            $array[] = $line;
        }

        echo array_to_csv($array, $download);
    }
}

/* End of file csv_helper.php */
/* Location: ./system/helpers/csv_helper.php */


After copying the above code now save the file.

Now goto you controller folder and make a new controller called Generate i.e. a new php file file inside the controller folder. Name the php file as generate.

Now copy the below code into it. :


<?php

class Generate extends CI_Controller
{

    function Generate()
    {
        parent::__construct();
        $this->load->database();
        $this->load->helper('url');
                $this->load->helper('csv');
    }

        function create_csv(){

            $query = $this->db->query('SELECT * FROM <tablename>');
            $num = $query->num_fields();
            $var =array();
            $i=1;
            $fname="";
            while($i <= $num){
                $test = $i;
                $value = $this->input->post($test);

                if($value != ''){
                        $fname= $fname." ".$value;
                        array_push($var, $value);

                    }
                 $i++;
            }

            $fname = trim($fname);

            $fname=str_replace(' ', ',', $fname);

            $this->db->select($fname);
            $quer = $this->db->get('<tablename>');
            
            query_to_csv($quer,TRUE,'Products_'.date('dMy').'.csv');
            
        }
}



The above function "query_to_csv" helps us to put the result of the query directly into the csv and then generate a CSV File called Product_todaysDate.csv. There is one more function called array_to_csv which put the data stored in the array.

So now the CSV File is ready to be generated on the function call.

SHARE THIS POST:

7 comments:

  1. hi thanks for the tutorial i did as your tutorial but when i run it , it downloads the csv file but when i open the downloded file it is just an empty excel sheet, any idea?

    ReplyDelete
    Replies
    1. Check whether the table has data. I know its silly to ask but.

      Delete
  2. what does this line do: $this->db->select($fname);

    ReplyDelete
    Replies
    1. With the above line mentioned by you, it selects that particular column from the table using $quer = $this->db->get('');

      Delete
  3. how to change the target directory of the file?

    ReplyDelete
    Replies
    1. Well you can use a php function called rename, after the file has been generated

      Delete
  4. it works when I use only two lines:
    $query = $this->salarie_model->donneesPersonnelsCSV();
    query_to_csv($query,TRUE,'RegistreDuPersonnel_'.date('dMy_H.i.s').'.csv');
    however I want the answers of my previous quetsions.
    thanks for the nice tutorial!

    ReplyDelete