Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • How to export data in Excel sheet from database

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 137
    Comment on it

    Hello Readers!

    Here is a simple line of code to generate an excel file of data fetched from database. Users benefits with the compact and secure form of saving data.

     

    Firstly create a helper file in view/Helper folder and paste the following code into your file naming it CsvHelper.php:

     

    <?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: text/csv');
        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);
    }
    }
    ?>
    

     

    Next you are required to add an export link to your display.ctp file:

     

    <?php echo $this->Html->link('Export',array('controller'=>'employees','action'=>'export'), array('target'=>'_blank'));?>
    

     

    Now, add the following function in your Controller file:

     

    public function export()
    {
    	$this->set('posts', $this->Employee->find('all'));
    	$this->layout = null;
    	$this->autoLayout = false;
    	Configure::write('debug','0');
    }

     

    At last, create a view for export function and paste the following code to it:

     

    <?php
    
       $line= $posts[0]['Employee'];
       $this->CSV->addRow(array_keys($line));
       foreach ($posts as $post)
        {
           $line= $post['Employee']; 
           $this->CSV->addRow($line);
        }
       $filename='posts';
       echo  $this->CSV->render($filename);
    ?>
    

     

    That's all you have to do!

    Happy Coding :)

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: