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.
- Import-Export-CSV-in-PHP:- Simple script to import/export CSV files in PHP
- phpCSV:- Export MYSQL data to CSV using PHP
- PHP-MSSQL-Server-Admin:- PHP MSSQL Server Admin or PHP Query Analyzer is a tool where you can admin a MSSQL Server with PHP, like PHPMyAdmin for
- php-opencart-export-to-mysql:- Export opencart goods to CSV file
- mysqli-table-export-to-csv:- Export database table records from mysqli to csv using php
- Export-To-CSV:- Export mysql table data to CSV using PHP
- PHP-MySQL-CSV-Export:- Oversimplified PHP Script for Exporting a MySQL Table’s Data for Export to a CSV file
- MICODIGOLIBRE.COM-mysql-to-csv:- MYSQL export to CSV with PHP
- php-to-csv:- Exporting Formated Data to csv using PHP from MySql
- Export-DB-Data-to-CSV-file:- Exporting the Mysql Table Data to CSV file 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.