Excel is a spreadsheet which contains cells in form of rows and columns. We can say it is a computer program which is used to record and manipulate data in rows and columns.

In this blog we will learn how export data reports in excel format using Codeigniter framework.

So lets get started with a new controller from which report will export.

Step 1: create new file and name it home.php

And its code will go like this:-

<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed'); 
class Home extends CI_Controller {
	public function __construct() {
		parent::__construct();
		$this->load->library('excel');
    }

        public function index()
    {
                $data['rs'] =  $this->db->get('users');
                $this->load->view('home', $data);
    }
        public function excel()
    {
                $this->excel->setActiveSheetIndex(0);
                //name the worksheet
                $this->excel->getActiveSheet()->setTitle('Countries');
                //set cell A1 content with some text
                $this->excel->getActiveSheet()->setCellValue('A1', 'Country Excel Sheet');
                $this->excel->getActiveSheet()->setCellValue('A4', 'S.No.');
                $this->excel->getActiveSheet()->setCellValue('B4', 'Country Code');
                $this->excel->getActiveSheet()->setCellValue('C4', 'Country Name');
                //merge cell A1 until C1
                $this->excel->getActiveSheet()->mergeCells('A1:C1');
                //set aligment to center for that merged cell (A1 to C1)
                $this->excel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                //make the font become bold
                $this->excel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
                $this->excel->getActiveSheet()->getStyle('A1')->getFont()->setSize(16);
                $this->excel->getActiveSheet()->getStyle('A1')->getFill()->getStartColor()->setARGB('#333');
       for($col = ord('A'); $col <= ord('C'); $col++){ //set column dimension $this->excel->getActiveSheet()->getColumnDimension(chr($col))->setAutoSize(true);
                 //change the font size
                $this->excel->getActiveSheet()->getStyle(chr($col))->getFont()->setSize(12);
                $this->excel->getActiveSheet()->getStyle(chr($col))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        }
                //retrive contries table data
                $rs = $this->db->get('users');
                $exceldata="";
        foreach ($rs->result_array() as $row){
                $exceldata[] = $row;
        }
                //Fill data
                $this->excel->getActiveSheet()->fromArray($exceldata, null, 'A4');
                $this->excel->getActiveSheet()->getStyle('A4')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                $this->excel->getActiveSheet()->getStyle('B4')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                $this->excel->getActiveSheet()->getStyle('C4')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                $filename='PHPExcelDemo.xls'; //save our workbook as this file name
                header('Content-Type: application/vnd.ms-excel'); //mime type
                header('Content-Disposition: attachment;filename="'.$filename.'"'); //tell browser what's the file name
                header('Cache-Control: max-age=0'); //no cache
                //save it to Excel5 format (excel 2003 .XLS file), change this to 'Excel2007' (and adjust the filename extension, also the header mime type)
                //if you want to save it as .XLSX Excel 2007 format
                $objWriter = PHPExcel_IOFactory::createWriter($this->excel, 'Excel5');
                //force user to download the Excel file without writing it to server's HD
                $objWriter->save('php://output');

    }

}
 
/* End of file welcome.php */
/* Location: ./application/controllers/home.php */

In the code above we have include the excel library, You can download it from here.

You have to extract the zip files and put the folder inside your third_party folder.

Step 2: Now you have to create a new file inside libraries folder and name it to Excel.php

It's code will go like this:-

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

require_once APPPATH."/third_party/PHPExcel/Classes/PHPExcel.php";

class Excel extends PHPExcel {
    public function __construct() {
        parent::__construct();
    }
}

In step one are calling this file and this file will call the third party which we have already copied into third_party folder.

Now it's all done, you just need to call excel function of home.php controller and it will generate and download the data from database to excel format.

If you have any questions, please feel free to write in comment section.