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>   
?>