In this post, we will explain how to Export data into Excel in CodeIgniter. Export data functionality makes your web application user-friendly and helps the user to maintain list data. Excel is the best technique to Export data in a file and you can easily export data to Excel using Codeigniter.
First, we need to download PHPExcel Library, then extract 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.
Step 3: Create Database
For this tutorial, you need a MySQL database with the following table:
Step 4: Create Controller and load class
Syntax:
Load “excel” class in controller.
Create a controller file like contactus.php inside “application/controllers” folder.
Step 5: Create Model
Create a model file named Export_model.php inside “application/models” folder.
Step 6: Create views
Create a views file named index.php inside “application/views/export” folder.
Demo [sociallocker] Download[/sociallocker]
First, we need to download PHPExcel Library, then extract 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.
if (!defined('BASEPATH')) exit('No direct script access allowed');
/*
* =======================================
* Author : Team Tech Arise
* License : Protected
* Email : info@techarise.com
*
* =======================================
*/
require_once APPPATH . "/third_party/PHPExcel.php";
class Excel extends PHPExcel {
public function __construct() {
parent::__construct();
}
}
?>
Step 3: Create Database
For this tutorial, you need a MySQL database with the following table:
//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, 'Team', 'Tech Arise', 'info@techarise.com', '21-02-2011', '9000000001'),
(2, 'Admin', '1st', 'admin@techarise.com', '21-02-2011', '9000000002'),
(3, 'User', '4rth', 'user@techarise.com', '21-02-2011', '9000000003'),
(4, 'Editor', '3rd', 'editor@techarise.com', '21-02-2011', '9000000004'),
(5, 'Writer', '2nd', 'writer@techarise.com', '21-02-2011', '9000000005'),
(6, 'Contact', 'one', 'contact@techarise.com', '21-02-2011', '9000000006'),
(7, 'Manager', '1st', 'manager@techarise.com', '21-02-2011', '9000000007');
?>
Step 4: Create Controller and load class
Syntax:
Load “excel” class in controller.
$this->load->library('excel');
?>
Create a controller file like contactus.php inside “application/controllers” folder.
/*
* To change this license header, choose License Headers in Project Properties.
* To change this template file, choose Tools | Templates
* and open the template in the editor.
*/
/**
* Description of Export Controller
*
* @author TechArise Team
*
* @email info@techarise.com
*/
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 | TechArise';
$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.
/*
* To change this license header, choose License Headers in Project Properties.
* To change this template file, choose Tools | Templates
* and open the template in the editor.
*/
/**
* Description of Export Model
*
* @author TechArise Team
*
* @email info@techarise.com
*/
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.
First Name
Last Name
DOB
Contact Name
if (isset($employeeInfo) && !empty($employeeInfo)) {
foreach ($employeeInfo as $key => $element) {
?>
}
} else {
?>
There is no employee.
Export Data
Demo [sociallocker] Download[/sociallocker]