First Step:- Download this excel library here : click here
Next, we need to download phpExcel library from this link, and extract into application/third_party folder. After extract this library move to PHPExcel folder like application/third_party/PHPExcel and also move PHPExcel.php file to application/third_party/PHPExcel.php
Now we need to create Excel.php file into application/library, So go to application/library and create one file name Excel.php and put the below code here.
<?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();
}
}Create Database With Table
you can use the below sql query for creating a table in your database.
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 varchar(50) NOT NULL COMMENT 'Contact No',
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='datatable demo table' AUTO_INCREMENT=1;
INSERT INTO import (id, first_name, last_name, email, dob, contact_no) VALUES
(1, 'Team', 'test', 'info@test.com', '21-02-2011', '9000000001'),
(2, 'Admin', 'second', 'admin@test.com', '21-02-2011', '9000000002'),
(3, 'User', 'third', 'user@test.com', '21-02-2011', '9000000003'),
(4, 'Editor', 'fouth', 'editor@test.com', '21-02-2011', '9000000004'),
(5, 'Writer', 'fifth', 'writer@test.com', '21-02-2011', '9000000005'),
(6, 'Contact', 'sixth', 'contact@test.com', '21-02-2011', '9000000006'),
(7, 'Manager', 'seven', 'manager@test.com', '21-02-2011', '9000000007');
Now we need to create a controller name Export.php. In this controller we will create some method/function. We will build some of the methods like :
defined('BASEPATH') OR 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');
$this->load->library('excel');
}
public function list_of_grievance_excel_download()
{
$fileName = 'data-' . time() . '.xlsx';
$empInfo = $this->export->getallgrievanceData_excel();
$objPHPExcel = new PHPExcel();
$objPHPExcel->setActiveSheetIndex(0);
// set Header
$objPHPExcel->getActiveSheet()->SetCellValue('A1', 'Name of Person/StartUp/Incubators');
$objPHPExcel->getActiveSheet()->SetCellValue('B1', 'Query Type');
$objPHPExcel->getActiveSheet()->SetCellValue('C1', 'Email');
$objPHPExcel->getActiveSheet()->SetCellValue('D1', 'Contact_No');
$objPHPExcel->getActiveSheet()->SetCellValue('E1', 'Query Type');
$objPHPExcel->getActiveSheet()->SetCellValue('F1', 'Query/Grievance Description');
$objPHPExcel->getActiveSheet()->SetCellValue('G1', 'Query Date');
$objPHPExcel->getActiveSheet()->SetCellValue('H1', 'Status');
$objPHPExcel->getActiveSheet()->SetCellValue('I1', 'Reply Comments');
$objPHPExcel->getActiveSheet()->SetCellValue('J1', 'Reply Date');
// set Row
$rowCount = 2;
foreach ($empInfo as $element) {
$objPHPExcel->getActiveSheet()->SetCellValue('A' . $rowCount, $element['first_name'].' '.$element['last_name']);
$objPHPExcel->getActiveSheet()->SetCellValue('B' . $rowCount, $element['post_type']);
$objPHPExcel->getActiveSheet()->SetCellValue('C' . $rowCount, $element['email_id']);
$objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $element['mobile']);
$objPHPExcel->getActiveSheet()->SetCellValue('E' . $rowCount, $element['query']);
$objPHPExcel->getActiveSheet()->SetCellValue('F' . $rowCount, $element['comments']);
$objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, date("d-m-Y",$element['added_on']));
if (($element['is_closed']==0)) {
if (!empty($element['user_id'])) {
if($element['is_reply']==1){
$status = "Replied";
}else{
$status='Pending';
}
} else {
if ($element['is_reply'] == 0) {
$status= 'Pending';
} else {
$status= 'Replied';
}
}
}else{
$status = 'Closed';
}
$objPHPExcel->getActiveSheet()->SetCellValue('H' . $rowCount, $status);
if($element['is_reply']==1){
$objPHPExcel->getActiveSheet()->SetCellValue('I' . $rowCount, $element['reply_comments']);
$objPHPExcel->getActiveSheet()->SetCellValue('J' . $rowCount, date('d-m-Y',strtotime($element['reply_date'])));
}
$rowCount++;
}
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
$file_path . 'assets/Grievance/';
$objWriter->save(file_path . $fileName);
// download file
header("Content-Type: application/vnd.ms-excel");
redirect(file_path . $fileName);
}