Export data in excel using php and cakephp

This simple tutorial belongs to newbie php and cakephp developer, Here i’ll tell you the simplest trick to export your mysql data into excel/csv, Every time we need this while generating reports so this is the simplest line of code to do this But If you want more customize report you must use any third party report grnerator plugin which provide more flexibility to generate reports.

Now first i’ll tell how to do this in core php almost same line of code will follow in cakephp.


Method-1

Create a function in php named exportInExcel()

<?php
function exportInExcel($fileName, $headerRow, $data) {
  ini_set('max_execution_time', 1600); 
  $fileContent = implode("\t ", $headerRow)."\n";
  foreach($data as $result) {
   $fileContent .=  implode("\t ", $result)."\n";
  }
 header('Content-type: application/ms-excel'); 
 header('Content-Disposition: attachment; filename='.$fileName);
 echo $fileContent;
exit;
}
?>

Now call this function where you want to export your data into excel..
Lets assume we have a table of books and we need to export from mysql database to excel.

<?php
$fileName = "bookreport_".date("d-m-y:h:s").".xls";
//$fileName = "bookreport_".date("d-m-y:h:s").".csv";
$headerRow = array("Book Title", "ISBN No.", "Auther");
$data = array(
           array('Book Title1', '1111111111', 'Rohit Kumar-1'),
           array('Book Title2', '2222222222', 'Rohit Kumar-2'),
           array('Book Title3', '3333333333', 'Rohit Kumar-3'),
           array('Book Title4', '4444444444', 'Rohit Kumar-4') 
          );
exportInExcel($fileName, $headerRow, $data);
?>

Note: where $data values come from database, must ensure that array should same as you seen in example.



Method-2

In this method we have created simple php class for mysql to csv export via PHP. So you can add this simple class in your project and use this class to export MySql data in Excel CSV format easily.

PHP Class – Export MYSQL data to CSV using PHP

<?php
class H_Mysql_Export {
    public $headerAry = array();
    public $dataAry = array();
    public $directory = './';
    public $filename = 'h_export_';
    public $filepath = '';
 
    public function init(){
        $this->filepath = $this->directory.$this->filename.time();  
    }
 
    public function csv(){
        $this->init();
        $this->filepath = $this->filepath.".csv";
        $handle = fopen($this->filepath, 'w+');
        fputcsv($handle, $this->headerAry);
        foreach($this->dataAry as $csvdata){
            fputcsv($handle, $csvdata); 
        }       
        fclose($handle);
    }
 
    public function download(){     
        if (file_exists($this->filepath)) {
            header('Content-Description: File Transfer');
            header('Content-Type: application/octet-stream');
            header('Content-Disposition: attachment; filename='.basename($this->filepath));
            header('Content-Transfer-Encoding: binary');
            header('Expires: 0');
            header('Cache-Control: must-revalidate');
            header('Pragma: public');
            header('Content-Length: ' . filesize($this->filepath));
            ob_clean();
            flush();
            readfile($this->filepath);
        }
        else {
            echo $this->filepath.' Doesnt Exist!';  
        }
    }
 
    public function delete(){
        unlink($this->filepath);    
    }
}
?>

Below we have creates simple php code to export static data into CSV format using above class.

$export = new H_Mysql_Export();
$export->headerAry = array();
$export->dataAry = array();
$export->csv();
$export->download();

Using PHP and mysql

mysql_connect("localhost","root","");
mysql_select_db("db");
 
$query = mysql_query("SELECT * FROM cds");
while($row = mysql_fetch_assoc($query)){
    $data[] = $row;
}
 
print_r($data);
 
include('classFile.php');
 
$export = new H_Mysql_Export();
$export->headerAry = array('Title','Interpret','jahr','ID');    
$export->dataAry = $data;                   
$export->filename = 'Sample';                   
$export->directory = 'files/';                  
$export->csv();                         
$export->download();                        
$export->delete();                      // AUTOMATIC DELETE DOWNLOADED FILE



Method-3: in Cakephp

Now lets talk about the cakephp just like core php we can do same thing but here we’ll create a component to do this in better manner.

Go to app/Controller/Component and create file name ExportXlsComponent.php

ExportXlsComponent.php

<?php
App::uses('Component', 'Controller');
class ExportXlsComponent extends Component {
 
function export($fileName, $headerRow, $data) {
 ini_set('max_execution_time', 1600); 
  $fileContent = implode("\t ", $headerRow)."\n";
  foreach($data as $result) {
   $fileContent .=  implode("\t ", $result)."\n";
  }
 header('Content-type: application/ms-excel'); 
 header('Content-Disposition: attachment; filename='.$fileName);
 echo $fileContent;
exit;
}
?>

After that call this component in your controller file where you want to export data into excel.

BooksController.php

<?php
class BooksController extends AppController {
public $components = array('ExportXls');
 
 function reports() {
  $this-autoRender = false;
  $this->layout = false;
$fileName = "bookreport_".date("d-m-y:h:s").".xls";
//$fileName = "bookreport_".date("d-m-y:h:s").".csv";
$headerRow = array("Book Title", "ISBN No.", "Auther");
$data = array(
           array('Book Title1', '1111111111', 'Rohit Kumar-1'),
           array('Book Title2', '2222222222', 'Rohit Kumar-2'),
           array('Book Title3', '3333333333', 'Rohit Kumar-3'),
           array('Book Title4', '4444444444', 'Rohit Kumar-4') 
          );
  $this->ExportXls->export($fileName, $headerRow, $data);
  }
}
?>

Thanks 🙂

FREE PHP Script: Export MYSQL data to CSV using PHP

Following are the list of popular top rated hand picked Export MYSQL data to CSV using PHP.

I Hope you liked Hand-picked list of Export MYSQL data to CSV using PHP, Don’t forget to Subscribe My Public Notebook for more useful Hand-picked PHP and MySql code examples, tutorials and articles.