Bladephp: Now with 8+ codeigniter, php, and laravel project 8+ php project From $37/Month From Rs. 2499/Month

How to Create Dynamic Excel File Using PHPExcel Library in Codeigniter

by
Category:codeigniter
codeigniter phpexcel
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 Excel data 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();

	$default_border = array(
		'style' => PHPExcel_Style_Border::BORDER_THIN,
		'color' => array('rgb' => '000000'),
	);

	$acc_default_border = array(
		'style' => PHPExcel_Style_Border::BORDER_THIN,
		'color' => array('rgb' => 'c7c7c7'),
	);
	$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,
		),
	);
	$style_header = 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' => 12,
			'name' => 'Arial',
			'bold' => true,
		),
		'alignment' => array(
			'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
			'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT,
		),
	);
	$account_value_style_header = array(
		'borders' => array(
			'bottom' => $default_border,
			'left' => $default_border,
			'top' => $default_border,
			'right' => $default_border,
		),
		'font' => array(
			'color' => array('rgb' => '000000'),
			'size' => 12,
			'name' => 'Arial',
		),
		'alignment' => array(
			'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
			'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT,
		),
	);
	$text_align_style = array(
		'alignment' => array(
			'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
			'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
		),
		'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' => 12,
			'name' => 'Arial',
			'bold' => true,
		),
	);

	$objPHPExcel->setActiveSheetIndex(0)->mergeCells('A1:H1');
	$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Purchase Bonuses Report');

	$objPHPExcel->getActiveSheet()->getStyle('A1')->applyFromArray($top_header_style);
	$objPHPExcel->getActiveSheet()->getStyle('B1')->applyFromArray($top_header_style);
	$objPHPExcel->getActiveSheet()->getStyle('C1')->applyFromArray($top_header_style);
	$objPHPExcel->getActiveSheet()->getStyle('D1')->applyFromArray($top_header_style);
	$objPHPExcel->getActiveSheet()->getStyle('E1')->applyFromArray($top_header_style);
	$objPHPExcel->getActiveSheet()->getStyle('F1')->applyFromArray($top_header_style);
	$objPHPExcel->getActiveSheet()->getStyle('G1')->applyFromArray($top_header_style);
	$objPHPExcel->getActiveSheet()->getStyle('H1')->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()->getStyle('A2')->applyFromArray($style_header);
	$objPHPExcel->getActiveSheet()->getStyle('B2')->applyFromArray($style_header);
	$objPHPExcel->getActiveSheet()->getStyle('C2')->applyFromArray($style_header);
	$objPHPExcel->getActiveSheet()->getStyle('D2')->applyFromArray($style_header);
	$objPHPExcel->getActiveSheet()->getStyle('E2')->applyFromArray($style_header);
	$objPHPExcel->getActiveSheet()->getStyle('F2')->applyFromArray($style_header);
	$objPHPExcel->getActiveSheet()->getStyle('G2')->applyFromArray($style_header);
	$objPHPExcel->getActiveSheet()->getStyle('H2')->applyFromArray($style_header);

	$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->getBonusPurchase();
	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

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

8 Comments

Himeko

25 Aug 2017

thanks! it's working, again thank you for this article, this very help for my project :)
Budi

11 Dec 2017

Thanks!.....Work Banget
Shailendra Singh

03 Jan 2018

Thanks...........
Salman

17 Apr 2018

Thanks Man! May you got much more success :) (Y)
shanmugam

27 Jul 2018

Hi , Can you suggest me how to dowload excel in .xlsx format from your coding. Thanks
Gurjant singh

12 Oct 2018

Thanks.........
Ahmed Imran

13 Nov 2018

how to setup controlloer ? Please Demo and Source Code Download Link.
Ahmed Imran

13 Nov 2018

Anebody Help Please. Configuration File send me. engimranah@gmail.com

Html code work in comment box


Related Post