How to Export Data in Excel format in Codeigniter using PHPExcel Library
An Excel worksheet is a collection of cells where you can keep and manipulate data. Each Excel workbook can contain multiple worksheets.If we want to maintain an Excel worksheet of our website data then we have the option to type one by one data in our excel worksheet, for this provision today we know how to export data from database in Excel sheet using Codeigniter.
First, we need to download PHPExcel Library, then extract PHPExcel Library
Also Read:-How to Export Data in Excel format in Codeigniter using PHPExcel Library
Step 1: Extract PHPExcel Library
Note: Copy and Paste inside “application/third_party” folder.
Step 2: Create file
Create a file named Excel.php inside “application/libraries” folder.
<?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();
}
}
?>
Also Read:-Codeigniter Remove index.php using htaccess
Step 3: Create Database
For this tutorial, you need a MySQL database with the following table:
<?php
//Table structure for table employee
CREATE TABLE `import` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary Key',
`first_name` varchar(100) NOT NULL COMMENT 'First Name',
`last_name` varchar(100) NOT NULL COMMENT 'Last Name',
`email` varchar(255) NOT NULL COMMENT 'Email Address',
`dob` varchar(20) NOT NULL COMMENT 'Date of Birth',
`contact_no` int(11) NOT NULL COMMENT 'Contact No',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='datatable demo table' AUTO_INCREMENT=1;
//Dumping data for table `import`
INSERT INTO `import` (`id`, `first_name`, `last_name`, `email`, `dob`, `contact_no`) VALUES
(1, 'Admin', '1st', 'admin@crowdforgeeks.com', '21-02-2011', '9000000002'),
(2, 'User', '4rth', 'user@crowdforgeeks.com', '21-02-2011', '9000000003'),
(3, 'Editor', '3rd', 'editor@crowdforgeeks.com', '21-02-2011', '9000000004'),
(4, 'Writer', '2nd', 'writer@crowdforgeeks.com', '21-02-2011', '9000000005'),
(5, 'Contact', 'one', 'contact@crowdforgeeks.com', '21-02-2011', '9000000006'),
(6, 'Manager', '1st', 'manager@crowdforgeeks.com', '21-02-2011', '9000000007');
?>
Step 4: Create Controller and load class
Syntax: Load “excel” class in controller.
<?php
// load library
$this->load->library('excel');
?>
Also Read:-Codeigniter Get File Extension Before Upload
Create a controller file like contactus.php inside “application/controllers” folder.
<?php
if (!defined('BASEPATH'))
exit('No direct script access allowed');
class Export extends CI_Controller {
// construct
public function __construct() {
parent::__construct();
// load model
$this->load->model('Export_model', 'export');
}
// export xlsx|xls file
public function index() {
$data['page'] = 'export-excel';
$data['title'] = 'Export Excel data | CrowdforGeeks';
$data['employeeInfo'] = $this->export->employeeList();
// load view file for output
$this->load->view('export/index', $data);
}
// create xlsx
public function createXLS() {
// create file name
$fileName = 'data-'.time().'.xlsx';
// load excel library
$this->load->library('excel');
$empInfo = $this->export->employeeList();
$objPHPExcel = new PHPExcel();
$objPHPExcel->setActiveSheetIndex(0);
// set Header
$objPHPExcel->getActiveSheet()->SetCellValue('A1', 'First Name');
$objPHPExcel->getActiveSheet()->SetCellValue('B1', 'Last Name');
$objPHPExcel->getActiveSheet()->SetCellValue('C1', 'Email');
$objPHPExcel->getActiveSheet()->SetCellValue('D1', 'DOB');
$objPHPExcel->getActiveSheet()->SetCellValue('E1', 'Contact_No');
// set Row
$rowCount = 2;
foreach ($empInfo as $element) {
$objPHPExcel->getActiveSheet()->SetCellValue('A' . $rowCount, $element['first_name']);
$objPHPExcel->getActiveSheet()->SetCellValue('B' . $rowCount, $element['last_name']);
$objPHPExcel->getActiveSheet()->SetCellValue('C' . $rowCount, $element['email']);
$objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $element['dob']);
$objPHPExcel->getActiveSheet()->SetCellValue('E' . $rowCount, $element['contact_no']);
$rowCount++;
}
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
$objWriter->save(ROOT_UPLOAD_IMPORT_PATH.$fileName);
// download file
header("Content-Type: application/vnd.ms-excel");
redirect(HTTP_UPLOAD_IMPORT_PATH.$fileName);
}
}
?>
Step 5: Create Model
Create a model file named Export_model.php inside “application/models” folder.
<?php
if (!defined('BASEPATH'))
exit('No direct script access allowed');
class Export_model extends CI_Model {
// get employee list
public function employeeList() {
$this->db->select(array('e.id', 'e.first_name', 'e.last_name', 'e.email', 'e.dob', 'e.contact_no'));
$this->db->from('import as e');
$query = $this->db->get();
return $query->result_array();
}
}
?>
Step 6: Create views
Create a views file named index.php inside “application/views/export” folder.
Also Read:-How to fetch single row from database in php codeigniter?
<div class="table-responsive">
<table class="table table-hover tablesorter">
<thead>
<tr>
<th class="header">First Name</th>
<th class="header">Last Name</th>
<th class="header">Email</th>
<th class="header">DOB</th>
<th class="header">Contact Name</th>
</tr>
</thead>
<tbody>
<?php
if (isset($employeeInfo) && !empty($employeeInfo)) {
foreach ($employeeInfo as $key => $element) {
?>
<tr>
<td><?php echo $element['first_name']; ?></td>
<td><?php echo $element['last_name']; ?></td>
<td><?php echo $element['email']; ?></td>
<td><?php echo $element['dob']; ?></td>
<td><?php echo $element['contact_no']; ?></td>
</tr>
<?php
}
} else {
?>
<tr>
<td colspan="5">There is no employee.</td>
</tr>
<?php } ?>
</tbody>
</table>
<a class="pull-right btn btn-primary btn-xs" href="<?php echo site_url()?>export/createxls"><i class="fa fa-file-excel-o"></i> Export Data</a>
</div>
Also Read:-How to get previous page url in codeigniter?
