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.
//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();
}
}
Step 3.
Create a mysql table(countries).
--
-- 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 ;
and create some entries in the countries table
--
-- 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')
Step 4.
Create a controller (home.php) under the directory(application/controller/home.php) and paste the following 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 */
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.
<!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 -->
<script src='https://code.jquery.com/jquery-2.1.1.min.js'></script>
<!-- Latest compiled and minified JavaScript -->
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.2.0/js/bootstrap.min.js"></script>
</head>
<body>
<div class="container">
<div class="row clearfix">
<div class="col-md-12 column">
<nav class="navbar navbar-default" role="navigation">
<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>
<ul class="nav navbar-nav pull-right">
<li class="active"><a href="<?php echo base_url()?>home/excel"><i class="glyphicon glyphicon-log-in"></i> Export Excel</a></li>
</ul>
</nav>
</div>
</div>
</div>
<div class="container">
<table style="width: 100%">
<thead><th>S N</th><th>Country code</th><th>Country name</th></thead>
<tbody>
<?php foreach ($rs->result() as $row): ?>
<tr><td><?php echo $row->id?></td><td><?php echo $row->country_code ?></td><td><?php echo $row->country_name?></td></tr>
<?php endforeach; ?>
</tbody>
</table>
</div>
</body>
</html>
Now run the application and export the excel your downloaded excel file will show like this.......
No comments:
Post a Comment