How to Create Dynamic Excel File Using PHPExcel Library in Codeigniter

In this tutorial, we will learn how to create a excel file in php Codeigniter with help of phpexcel library. The data of Excel will come through mysql database.

Download the PHPExcel library

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();
}

if you have problem please write us we will help you.