How to Create Dynamic Excel File Using PHPExcel Library in Codeigniter

In this tutorial, we will learn how to create an excel file in PHP Codeigniter with help of the PHP excel library. The data of Excel will come through the MySQL database.

Extract the zip file using winzip or winrar and save the phpexcel folder under the codeigniter application directory (application/third_party/(save paste your phpexcel folder here).

Now create a Library file Excel.php under the directory(application/libraries/Excel.php) and paste following code in it.

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

require_once APPPATH.'/third_party/PHPExcel.php';
class Excel extends PHPExcel
{
    public function __construct()
    {
        parent::__construct();
    }
}
?>

Controller function code

public function exportpurchase()
{
	$this->load->library('excel');
	require_once './application/third_party/PHPExcel.php';
	require_once './application/third_party/PHPExcel/IOFactory.php';

	// Create new PHPExcel object
	$objPHPExcel = new PHPExcel();

	// Style your excel sheet.
	$outlet_style_header = array(
		'font' => array(
			'color' => array('rgb' => '000000'),
			'size' => 10,
			'name' => 'Arial',
			'bold' => true,
		),
	);
	$top_header_style = array(
		'borders' => array(
			'bottom' => $default_border,
			'left' => $default_border,
			'top' => $default_border,
			'right' => $default_border,
		),
		'fill' => array(
			'type' => PHPExcel_Style_Fill::FILL_SOLID,
			'color' => array('rgb' => 'ffff03'),
		),
		'font' => array(
			'color' => array('rgb' => '000000'),
			'size' => 15,
			'name' => 'Arial',
			'bold' => true,
		),
		'alignment' => array(
			'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
			'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
		),
	);
	
	$objPHPExcel->setActiveSheetIndex(0)->mergeCells('A1:H1');
	$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Bonuses Report');

	$objPHPExcel->getActiveSheet()->getStyle('A1')->applyFromArray($top_header_style);
	
	$objPHPExcel->getActiveSheet()->setCellValue('A2', 'Date & Time');
	$objPHPExcel->getActiveSheet()->setCellValue('B2', 'Product Code');
	$objPHPExcel->getActiveSheet()->setCellValue('C2', 'Product Name');
	$objPHPExcel->getActiveSheet()->setCellValue('D2', 'Outlet');
	$objPHPExcel->getActiveSheet()->setCellValue('E2', 'Bill No');
	$objPHPExcel->getActiveSheet()->setCellValue('F2', 'Supplier');
	$objPHPExcel->getActiveSheet()->setCellValue('G2', 'Quantity');
	$objPHPExcel->getActiveSheet()->setCellValue('H2', 'Value');

	$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(25);
	$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(25);
	$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(25);
	$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(25);
	$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(25);
	$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(25);
	$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(25);
	$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(25);
	$objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(30);

	$row = 3;
	$custDtaData  = $this->Constant_model->getBonus();
	foreach ($custDtaData as $value)
	{
		$totalvalue = $value->purchase_price * $value->bonusqty;
		$objPHPExcel->getActiveSheet()->setCellValue('A'.$row, $value->created_datetime);
		$objPHPExcel->getActiveSheet()->setCellValue('B'.$row, $value->product_code);
		$objPHPExcel->getActiveSheet()->setCellValue('C'.$row, $value->productname);
		$objPHPExcel->getActiveSheet()->setCellValue('D'.$row, $value->outletsname);
		$objPHPExcel->getActiveSheet()->setCellValue('E'.$row, $value->bill_no);
		$objPHPExcel->getActiveSheet()->setCellValue('F'.$row, $value->suppliersname);
		$objPHPExcel->getActiveSheet()->setCellValue('G'.$row, $value->bonusqty);
		$objPHPExcel->getActiveSheet()->setCellValue('H'.$row, round($totalvalue,2));
		$row++;
	}

	header('Content-Type: application/vnd.ms-excel');
	header('Content-Disposition: attachment;filename="Cutomer_Report.xls"');
	header('Cache-Control: max-age=0');
	$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
	$objWriter->save('php://output');
}

Controller call to model to get data from database.

public function getBonusPurchase()
{
	$this->db->select('*');
	$this->db->from('bonuses');
	$query = $this->db->get();
	return $query->result();
}