PHPExcel is the best PHP library that allows users to export data to Excel or CSV file format. In this tutorial, we would love to share with you how to export data to Excel or CSV in CodeIgniter using PHPExcel.
Codeigniter Export Excel,CSV
Here are steps:
- Download Codeigniter Latest
- Basic Configurations
- Download phpExcel Library
- Create Library
- Create Table in DB
- Configure Database
- Make New Controller
- Create model
- Create Views
- Test This Project
Download Codeigniter Project
In this step, we will download the latest version of Codeigniter, Go to this link Download Codeigniter download the fresh setup of codeigniter and unzip the setup in your local system xampp/htdocs/ . And change the download folder name “demo”
Basic Configurations
Set up base URL in application/config/config.php:
$config['base_url'] = 'http://localhost/demo/';
Download phpExcel Library
Download phpExcel library from this link, and extract it into an application/third_party folder.
Create Library
Create Excel.php file into application/library, So go to application/library and create one file name Excel.php and add the following code into it:
<?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 Table in DB
Run the following 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', '[email protected]', '21-02-2011', '9000000001'),
(2, 'Admin', 'second', '[email protected]', '21-02-2011', '9000000002'),
(3, 'User', 'third', '[email protected]', '21-02-2011', '9000000003'),
(4, 'Editor', 'fouth', '[email protected]', '21-02-2011', '9000000004'),
(5, 'Writer', 'fifth', '[email protected]', '21-02-2011', '9000000005'),
(6, 'Contact', 'sixth', '[email protected]', '21-02-2011', '9000000006'),
(7, 'Manager', 'seven', '[email protected]', '21-02-2011', '9000000007');
Configure Database
Set up database details like db username, db user password, and db name in application/config/database.php file:
$db['default'] = array(
'dsn' => '',
'hostname' => 'localhost',
'username' => 'root',
'password' => '',
'database' => 'demo',
'dbdriver' => 'mysqli',
'dbprefix' => '',
'pconnect' => FALSE,
'db_debug' => (ENVIRONMENT !== 'production'),
'cache_on' => FALSE,
'cachedir' => '',
'char_set' => 'utf8',
'dbcollat' => 'utf8_general_ci',
'swap_pre' => '',
'encrypt' => FALSE,
'compress' => FALSE,
'stricton' => FALSE,
'failover' => array(),
'save_queries' => TRUE
);
Make New Controller
Create Export.php controller file and methods in it to handle export CSV or excel files from the database:
<?php
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');
}
public function index() {
$data['export_list'] = $this->export->exportList();
$this->load->view('export', $data);
}
// create xlsx
public function generateXls() {
// create file name
$fileName = 'data-'.time().'.xlsx';
// load excel library
$this->load->library('excel');
$listInfo = $this->export->exportList();
$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 ($listInfo as $list) {
$objPHPExcel->getActiveSheet()->SetCellValue('A' . $rowCount, $list->first_name);
$objPHPExcel->getActiveSheet()->SetCellValue('B' . $rowCount, $list->last_name);
$objPHPExcel->getActiveSheet()->SetCellValue('C' . $rowCount, $list->email);
$objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $list->dob);
$objPHPExcel->getActiveSheet()->SetCellValue('E' . $rowCount, $list->contact_no);
$rowCount++;
}
$filename = "tutsmake". date("Y-m-d-H-i-s").".csv";
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="'.$filename.'"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'CSV');
$objWriter->save('php://output');
}
}
?>
Create model
Create Export_model.php file in application/models folder to implement queries in it for export data:
<?php
defined('BASEPATH') OR exit('No direct script access allowed');
class Export_model extends CI_Model {
public function __construct()
{
$this->load->database();
}
public function exportList() {
$this->db->select(array('id', 'first_name', 'last_name', 'email', 'dob', 'contact_no'));
$this->db->from('import');
$query = $this->db->get();
return $query->result();
}
}
?>
Create Views
Create export.php in application/views/ folder and implement export csv or excel button in it:
<!DOCTYPE html>
<html>
<head>
<title>Codeigniter Export Example</title>
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.1.3/css/bootstrap.min.css" />
<script src="https://code.jquery.com/jquery-3.4.1.min.js"></script>
</head>
<body>
<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($export_list) && !empty($export_list)) {
foreach ($export_list as $key => $list) {
?>
<tr>
<td><?php echo $list->first_name; ?></td>
<td><?php echo $list->last_name; ?></td>
<td><?php echo $list->email; ?></td>
<td><?php echo $list->dob; ?></td>
<td><?php echo $list->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="export/generateXls"><i class="fa fa-file-excel-o"></i> Export Data</a>
</div>
</body>
</html>
Test This Project
Go to the browser and hit below the url.
http://localhost/demo/excel
Conclusion
In this codeigniter excel csv tutorial, we have successfully create a list of users and successfully export data as csv format, If you want to change format like excel,xls, xlse, etc. So you can change easily and download.
Rrecommended Posts
Import Data From Excel & CSV to mysql Using Codeigniter
Learn Remove index.php in web url using .htaccess file Codeigniter
You may like
- Import Data From Excel & CSV to mysql Using Codeigniter
- Adding Multiple Markers To Google Maps From Database PHP Codeigniter
- Integrate Razorpay with PHP Codeigniter
- Implement Google Column Chart With PHP Codeigniter
- Google Bar & Line Charts Days Wise MySQL PHP Codeigniter
- Codeigniter Pagination Library Example
- Morris Area & Line Chart With Codeigniter Example
If you have any questions or thoughts to share, use the comment form below to reach us.
Thanks bro.It was very helpful for me.