Generate Dynamic Excel File Using PHP Excel Library In Codeigniter

In this tutorial, we will learn how to generate a excel file in php codeigniter with help of php excel library. The Excel data will come through mysql database.
We will also format the text and row in excel file. We are not using any modal class. All the code is done in the controller class

So to achieve this goal, follow these steps.

Step 1.

Download the codeigniter framework for codeigniter website and set up on local server. And config it.

Step 2.

Download the php excel library from here/(Download).
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.

[code]
//path application/third_party/Excel.php
<?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();
}
}
[/code]

Step 3.

Create a mysql table(countries).

[code]
—
— Table structure for table `countries`
—

CREATE TABLE IF NOT EXISTS `countries` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`country_code` varchar(2) NOT NULL DEFAULT ”,
`country_name` varchar(100) NOT NULL DEFAULT ”,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=243 ;
[/php]

and create some entries in the countries table

[code]
—
— Dumping data for table `countries`
—

INSERT INTO `countries` (`id`, `country_code`, `country_name`) VALUES
(1, ‘US’, ‘United States’),
(2, ‘CA’, ‘Canada’),
(3, ‘AF’, ‘Afghanistan’),
(4, ‘AL’, ‘Albania’),
(5, ‘DZ’, ‘Algeria’),
(6, ‘DS’, ‘American Samoa’),
(7, ‘AD’, ‘Andorra’),
(8, ‘AO’, ‘Angola’),
(9, ‘AI’, ‘Anguilla’),
(10, ‘AQ’, ‘Antarctica’),
(11, ‘AG’, ‘Antigua and/or Barbuda’),
(12, ‘AR’, ‘Argentina’),
(13, ‘AM’, ‘Armenia’),
(14, ‘AW’, ‘Aruba’),
(15, ‘AU’, ‘Australia’),
(16, ‘AT’, ‘Austria’),
(17, ‘AZ’, ‘Azerbaijan’),
(18, ‘BS’, ‘Bahamas’),
(19, ‘BH’, ‘Bahrain’),
(20, ‘BD’, ‘Bangladesh’),
(21, ‘BB’, ‘Barbados’),
(22, ‘BY’, ‘Belarus’),
(23, ‘BE’, ‘Belgium’),
(24, ‘BZ’, ‘Belize’),
(25, ‘BJ’, ‘Benin’),
(26, ‘BM’, ‘Bermuda’),
(27, ‘BT’, ‘Bhutan’),
(28, ‘BO’, ‘Bolivia’),
(29, ‘BA’, ‘Bosnia and Herzegovina’),
(30, ‘BW’, ‘Botswana’),
(31, ‘BV’, ‘Bouvet Island’),
(32, ‘BR’, ‘Brazil’),
(33, ‘IO’, ‘British lndian Ocean Territory’),
(34, ‘BN’, ‘Brunei Darussalam’),
(35, ‘BG’, ‘Bulgaria’),
(36, ‘BF’, ‘Burkina Faso’),
(37, ‘BI’, ‘Burundi’),
(38, ‘KH’, ‘Cambodia’),
(39, ‘CM’, ‘Cameroon’),
(40, ‘CV’, ‘Cape Verde’),
(41, ‘KY’, ‘Cayman Islands’),
(42, ‘CF’, ‘Central African Republic’),
(43, ‘TD’, ‘Chad’),
(44, ‘CL’, ‘Chile’),
(45, ‘CN’, ‘China’),
(46, ‘CX’, ‘Christmas Island’),
(47, ‘CC’, ‘Cocos (Keeling) Islands’),
(48, ‘CO’, ‘Colombia’),
(49, ‘KM’, ‘Comoros’),
(50, ‘CG’, ‘Congo’)
[/code]

Step 4.

Create a controller (home.php) under the directory(application/controller/home.php) and paste the following code.

[code]
<?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(‘countries’);
$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(‘countries’);
$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 */
[/code]

This Controller will fetch all the data form mysql countries table and write this in the excel file and also show in html format for show to the user.

Step 5.

Create a view file (home.php) under the directory(application/view/home.php) and paste the following code.

[code]<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<title>Create simple website using codeigniter</title>
<!– Latest compiled and minified CSS –>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.2.0/css/bootstrap.min.css">
<!– Latest compiled and minified Jquery library –>
<img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" data-wp-preserve="%3Cscript%20src%3D’https%3A%2F%2Fcode.jquery.com%2Fjquery-2.1.1.min.js’%3E%3C%2Fscript%3E" data-mce-resize="false" data-mce-placeholder="1" class="mce-object" width="20" height="20" alt="<script>" title="<script>" />

<!– Latest compiled and minified JavaScript –>
<img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" data-wp-preserve="%3Cscript%20src%3D%22https%3A%2F%2Fmaxcdn.bootstrapcdn.com%2Fbootstrap%2F3.2.0%2Fjs%2Fbootstrap.min.js%22%3E%3C%2Fscript%3E" data-mce-resize="false" data-mce-placeholder="1" class="mce-object" width="20" height="20" alt="<script>" title="<script>" />
</head><body>

<div class="navbar-header">
<button type="button" class="navbar-toggle" data-toggle="collapse" data-target="#bs-example-navbar-collapse-1"> <span class="sr-only">Toggle navigation</span><span class="icon-bar"></span><span class="icon-bar"></span><span class="icon-bar"></span></button> <a class="navbar-brand" href="<?php echo base_url() ?>">CodeIgniter integration with php excel</a>
</div>

<li class="active"><a href="<?php echo base_url()?>home/excel"><i class="glyphicon glyphicon-log-in"></i>  Export Excel</a></li>

<thead>

<th>Country code</th>

<th>Country name</th>

</thead>

<tbody>
<?php foreach ($rs->result() as $row): ?>

<tr>

<td><?php echo $row->country_code ?></td>

<td><?php echo $row->country_name?></td>

</tr>

<?php endforeach; ?>
</tbody>

</table>

</div>

</body>
</html>[/code]

Now run the application and export the excel your downloaded excel file will show like this.

This code is running successfully . if you getting any error please comment out.

Note: I have used Codeigniter Version 2. Also enable your mode rewrite if you are getting any errors.

Don’t forget to share your doubts in the comment box and also share this post on social media and with your friends because “You share, I share, let’s make the world aware”.

You may want to take a look at the following related posts:

Also for more awesome tutorials, please don’t forget to like our facebook page Meul Tech .

Bonus: We also give training on following topics:

1. Angular Training in Mumbai.

2. Bootstrap Training Course in Mumbai.

3. Web Designing Training in Mumbai.

4. UI / UX Training.

5. IOS Training Institute in Mumbai.