Export Data to Excel in CodeIgniter with Example

 

Data Export to Excel (xls or xlsx) format is a feature of web applications to allow users to save data from MySQL table into Excel format for further use.

If you’re PHP developer and looking for solution to implement data export to excel in CodeIgniter, then you’re here at right place.

In this post you will learn how to export dynamic data into Excel format using CodeIgniter. We will use PHP library PhpSpreadsheet to create and save dynamic Excel file to export and save data. 

 

We will cover this tutorial in easy steps with live demo and link to download complete source code of live demo.

Before start implementing this example, we hope that you have setup CodeIgniter application with database connection details to use with this example. So let’s start.

Step1: Create MySQL Database Table

As we will cover this tutorial with live example, so first we will create MySQL database table emp.

CREATE TABLE `emp` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `skills` varchar(255) NOT NULL,
  `address` varchar(255) NOT NULL,
  `designation` varchar(255) NOT NULL,
  `age` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

We will insert few records for this example to export records into Excel file.

INSERT INTO `emp` (`id`, `name`, `skills`, `address`, `designation`, `age`) VALUES
(1, 'Smith s', 'Java', 'Sydney', 'Software Engineer', 34),
(2, 'David', 'PHP', 'London', 'Web Developer', 28),
(3, 'Rhodes', 'jQuery', 'New Jersy', 'Web Developer', 30),
(4, 'Sara', 'JavaScript', 'Delhi', 'Web Developer', 25),
(5, 'Shyrlin', 'NodeJS', 'Tokiyo', 'Programmer', 35),
(6, 'Steve', 'Angular', 'London', 'Web Developer', 28),
(7, 'Cook', 'MySQL', 'Paris', 'Web Developer', 26),
(8, 'Root', 'HTML', 'Paris', 'Web Developer', 28),
(9, 'William', 'jQuery', 'Sydney', 'Web Developer', 23),
(10, 'Nathan', 'PHP', 'London', 'Web Developer', 28),
(11, 'Shri', 'PHP', 'Delhi', 'Web Developer', 38),
(12, 'Jay', 'PHP', 'Delhi, India', 'Web Developer', 30);

Step2: Download PhpSpreadsheet Libraray

As we will use PHP libraray PhpSpreadsheet to create and save dynamic Excel file, so we will rub below composer command to download PhpSpreadsheet library into to use in project folder. It will create a new folder called vendor and it will download phpoffice/phpspreadsheet library into it.

 

$ composer require phpoffice/phpspreadsheet

Then open application/config/config.php file and set you vendor directory path.

$config['composer_autoload'] = 'vendor/autoload.php';

Step3: Create Model for Employee Data

As we will display employee records and export into Excel file, so first we will create model file EmployeeModel.php in application/models directory and define method to get employee list.

<?php
if (!defined('BASEPATH'))
exit('No direct script access allowed');
class EmployeeModel extends CI_Model {
	public function employeeList() {
		$this->db->select(array('id', 'name', 'skills', 'address', 'designation', 'age'));
		$this->db->from('emp');
		$this->db->limit(10);  
		$query = $this->db->get();
		return $query->result_array();
	}
}
?>

Step4: Create Employee Controllers

Now we will create controllers file Employee.php in application/controllers directory and load EmployeeModel models. We will also use
PhpSpreadsheet library inside in controllers. We will create method createExcel() to create and save Excel file using PhpSpreadsheet library.

<?php
if (!defined('BASEPATH'))
	exit('No direct script access allowed');
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
class Employee extends CI_Controller {
	public function __construct() {
        parent::__construct();
		$this->load->model('EmployeeModel');
    }    
	public function index() {
        $data['page'] = 'export-excel';
        $data['title'] = 'Export Excel data';
        $data['employeeData'] = $this->EmployeeModel->employeeList();
		$this->load->view('employee/employee', $data);
    }
	public function createExcel() {
		$fileName = 'employee.xlsx';  
		$employeeData = $this->EmployeeModel->employeeList();
		$spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
       	$sheet->setCellValue('A1', 'Id');
        $sheet->setCellValue('B1', 'Name');
        $sheet->setCellValue('C1', 'Skills');
        $sheet->setCellValue('D1', 'Address');
	$sheet->setCellValue('E1', 'Age');
        $sheet->setCellValue('F1', 'Designation');       
        $rows = 2;
        foreach ($employeeData as $val){
            $sheet->setCellValue('A' . $rows, $val['id']);
            $sheet->setCellValue('B' . $rows, $val['name']);
            $sheet->setCellValue('C' . $rows, $val['skills']);
            $sheet->setCellValue('D' . $rows, $val['address']);
	    $sheet->setCellValue('E' . $rows, $val['age']);
            $sheet->setCellValue('F' . $rows, $val['designation']);
            $rows++;
        } 
        $writer = new Xlsx($spreadsheet);
		$writer->save("upload/".$fileName);
		header("Content-Type: application/vnd.ms-excel");
        redirect(base_url()."/upload/".$fileName);              
    }    
}
?>

Step5: Create View for Employee Listing and Export Excel

We will create views employee/employee.php in application/views directory to display employee list and export excel link to download employee records into excel file.

<?php
<table class="table table-hover tablesorter">
<thead>
	<tr>
		<th class="header">Id.</th>
		<th class="header">Name</th> 
		<th class="header">Skills</th>
		<th class="header">Address</th>  
		<th class="header">Age</th>
		<th class="header">Designation</th>                 
	</tr>
</thead>
<a class="pull-right btn btn-warning btn-large" style="margin-right:40px" href="<?php echo site_url(); ?>/employee/createexcel"><i class="fa fa-file-excel-o"></i> Export to Excel</a>
<tbody>
	<?php
	if (isset($employeeData) && !empty($employeeData)) {
		foreach ($employeeData as $key => $emp) {
			?>
			<tr>
				<td><?php echo $emp['id']; ?></td>   
				<td><?php echo $emp['name']; ?></td> 
				<td><?php echo $emp['skills']; ?></td>
				<td><?php echo $emp['address']; ?></td> 
				<td><?php echo $emp['age']; ?></td>
				<td><?php echo $emp['designation']; ?></td>                       
			</tr>
			<?php
		}
	} else {
		?>
		<tr>
			<td colspan="5" class="alert alert-danger">No Records founds</td>    
		</tr>
	<?php } ?>			 
</tbody>
</table>   
?>

Create RESTful API using CodeIgniter

 

REST (Representational state transfer) API is a set of methods like GET, PUT, POST, DELETE to create web services. The RESTful Web services allow to request to access and manipulate textual representations of data using uniform and predefined set of stateless methods. Web services are useful when develop a application for web and mobile.

In our previous tutorial you have learned how to create Restful API using PHP and MySQL. In this tutorial you will learn how to create RESTful API using CodeIgniter.

We will cover this tutorial to create RESTful API with methods GET, PUT, POST, DELETE with live example.

 

Before proceeding with this tutorial, we hope that you have setup your CodeIgniter project with MySQL database connection details to use with this example. So let’s start

Step1: Create MySQL Database Table

As we will implement methods GET, PUT, POST, DELETE, so we will create employee table to add, edit delete and display employee operations with REST API.

CREATE TABLE `employee` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `skills` varchar(255) NOT NULL,
  `address` varchar(255) NOT NULL,
  `designation` varchar(255) NOT NULL,
  `age` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
ALTER TABLE `employee`
  ADD PRIMARY KEY (`id`);
ALTER TABLE `employee`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
COMMIT;

Step2: Create REST API Config File

First we will create REST config file application/config/rest.php. We will create config file using following code.

<?php
defined('BASEPATH') OR exit('No direct script access allowed');
$config['force_https'] = FALSE;
$config['rest_default_format'] = 'json';
$config['rest_supported_formats'] = [
    'json',
    'array',
    'csv',
    'html',
    'jsonp',
    'php',
    'serialized',
    'xml',
];
$config['rest_status_field_name'] = 'status';
$config['rest_message_field_name'] = 'error';
$config['enable_emulate_request'] = TRUE;
$config['rest_realm'] = 'REST API';
$config['rest_auth'] = FALSE;
$config['auth_source'] = 'ldap';
$config['allow_auth_and_keys'] = TRUE;
$config['auth_library_class'] = '';
$config['auth_library_function'] = '';
$config['rest_valid_logins'] = ['admin' => '1234'];
$config['rest_ip_whitelist_enabled'] = FALSE;
$config['rest_handle_exceptions'] = TRUE;
$config['rest_ip_whitelist'] = '';
$config['rest_ip_blacklist_enabled'] = FALSE;
$config['rest_ip_blacklist'] = '';
$config['rest_database_group'] = 'default';
$config['rest_keys_table'] = 'keys';
$config['rest_enable_keys'] = FALSE;
$config['rest_key_column'] = 'key';
$config['rest_limits_method'] = 'ROUTED_URL';
$config['rest_key_length'] = 40;
$config['rest_key_name'] = 'X-API-KEY';
$config['rest_enable_logging'] = FALSE;
$config['rest_logs_table'] = 'logs';
$config['rest_enable_access'] = FALSE;
$config['rest_access_table'] = 'access';
$config['rest_logs_json_params'] = FALSE;
$config['rest_enable_limits'] = FALSE;
$config['rest_limits_table'] = 'limits';
$config['rest_ignore_http_accept'] = FALSE;
$config['rest_ajax_only'] = FALSE;
$config['rest_language'] = 'english';
$config['check_cors'] = FALSE;
$config['allowed_cors_headers'] = [
  'Origin',
  'X-Requested-With',
  'Content-Type',
  'Accept',
  'Access-Control-Request-Method'
];
$config['allowed_cors_methods'] = [
  'GET',
  'POST',
  'OPTIONS',
  'PUT',
  'PATCH',
  'DELETE'
];
$config['allow_any_cors_domain'] = FALSE;
$config['allowed_cors_origins'] = [];

Step3: Create REST Library Files

We will create libaray files for handling RESTful web services operations. We will create application/libraries/Rest_lib.php and application/libraries/Format.php. The Rest_lib.php library will be extended into Emp.php API controller. Both the files are already created and available in download project files.

Step4: Create REST API Controller

We will create application/controllers/api/Emp.php to handle RESTful API methods like GET, PUT, POST and DELETE to handle web services operations.

<?php
require APPPATH . 'libraries/Rest_lib.php';
class Emp extends Rest_lib {    
	public function __construct() {
       parent::__construct();
       $this->load->database();
    }
    public function index_get($id = 0){
        if(!empty($id)){
            $empData = $this->db->get_where("employee", ['id' => $id])->row_array();
        }else{
            $empData = $this->db->get("employee")->result();
        }     
        $this->response($empData, Rest_lib::HTTP_OK);
	}    
    public function index_post(){
        $postData = $this->input->post();
        $this->db->insert('employee',$postData);     
        $this->response(['Employee created successfully.'], Rest_lib::HTTP_OK);
    }    
    public function index_put($id){
        $putData = $this->put();
        $this->db->update('employee', $putData, array('id'=>$id));
     
        $this->response(['Employee updated successfully.'], Rest_lib::HTTP_OK);
    }    
    public function index_delete($id){
        $this->db->delete('employee', array('id'=>$id));       
        $this->response(['Employee deleted successfully.'], Rest_lib::HTTP_OK);
    }    	
}

Step5: Run RESTful API

The following RESTful web service URL create to perform GET, PUT, POST and DELETE operation for employee.

https://webdamn.com/demo/restfull_api_codeigniter/api/emp

Now we will make request to this web service using Postman browser extension.