Hello Readers! In this blog we are going to export data from database using PHPExcel. PHPExcel supports reading and writing of many formats in one API. PHPExcel is a library that provides a set of classes for the PHP programming language, which allow us to write to and read from different spreadsheet file formats, like Excel .xls, Excel 2007 .xlsx, CSV, Libre/OpenOffice Calc .ods PDF, etc.

Some of PHPExcel features are:

• Create an in-memory spreadsheet representation
• Set spreadsheet meta data (author, title, description, ...)
• Add worksheets to spreadsheet
• Add data and formulas to individual cells
• Merge cells
• Protect ranges of cells with a password
• Supports setting cell width and height
• Supports different fonts and font styles
• Supports formatting, styles, cell borders, fills, gradients, ...
• Supports hyperlinks
• Supports different data types for individual cells
• Supports cell text wrapping
• Supports conditional formatting
• Supports column auto-sizing
• Supports rich-text strings
• Supports autofilter
• Supports "freezing" cell panes
• Supports cell-level security
• Supports workbook-level security
• Supports worksheet-level protection
• Group rows/columns
• Cell data validation
• Insert/remove rows/columns
• Named ranges
• Worksheet references
• Calculate formula values
• Add comments to a cell
• Add images to your spreadsheet
• Set image styles
• Set printing options
• Output your spreadsheet object to different file formats
• Read different file formats into your spreadsheet object

Create following function in controller file:

public function excel(){ 
      // create new empty worksheet and set default font 
      $this->PhpExcel->createWorksheet() 
            ->setDefaultFont('Calibri', 12); 

        // define table cells 
        $table = array( 
            array('label' => __('id')), 
            array('label' => __('name')), 
            array('label' => __('department')), 
            array('label' => __('number')), 
            array('label' => __('address')), 
            array('label' => __('salary')), 
        ); 
        // print_r($table);die(); 

        // add heading with different font and bold text 
        $this->PhpExcel->addTableHeader($table, array('name' => 'Cambria', 'bold' => true)); 
 
        // add data 
        $data=$this->Employee->find('all'); 
        // print_r($data);die(); 
        foreach ($data as $d) { 
            $this->PhpExcel->addTableRow(array( 
                $d['Employee']['id'], 
                $d['Employee']['emp_name'], 
                $d['Employee']['emp_dept'], 
                $d['Employee']['emp_number'], 
                $d['Employee']['emp_address'], 
                $d['Employee']['emp_salary'] 
            )); 
          } 
        // close table and output 
        $this->PhpExcel->addTableFooter() 
            ->output(); 
    }

add these two lines in controller:

App::import('Vendor', 'PHPExcel'); 
App::import('Vendor', 'Examples');

in AppController.php:

class AppController extends Controller{
	public $components = array('PhpExcel');
}

And at last create a view excel.ctp file. And add the following link in your display.ctp file:

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