Welcome, in this tutorial. Here I will explain how to export selected data into an excel file from database using PHP Codeigniter framework. In a web application export option makes your application more user-friendly and much better. Codeigniter is the easy framework of PHP, so you can easily implement export functionality to excel file. If you are using normal PHP or another PHP framework you use Excel Library anywhere. Download PHPExcel-1.8.
In this tutorial, I have attached an anchor tag, where you click and the export file will be download in your browser. I have used some jQuery and Ajax code to do this work very easily. let’s follow my steps:
Step 1: If you have to download the PHPExcel library, so that’s good and you can continue or download the PHPExcel library from the above link. Now place the PHPExcel library in your project folder under the third_party directory.
Path: Project/application/third_party/PHPExcel
Step 2: Now go to the library folder and create Excel.php file and paste the below code.
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\Classes\PHPExcel.php”;
class Excel extends PHPExcel {
public function __construct() {
parent::__construct();
}
}
?>
Step 3: Create an anchor tag where you click for export data to excel. For example:
<a href=”javascript:void(0)” onclick=”ExportData()”>Export to excel</a>
Step 4: Apply this JS code:
function ExportData(){
$.ajax({
type: ‘post’,
url: site_url+’/MyController/ExportDataToExcel’,
dataType:’json’,
success: function(result){
var $a = $(“<a>”);
$a.attr(“href”,result.file);
$(“body”).append($a);
$a.attr(“download”,”my-excel.xlsx”);
$a[0].click();
$a.remove();
}
});
}
Step 5: Now go to your controller page and trying with this example code:
<?php
defined(‘BASEPATH’) OR exit(‘No direct script access allowed’);
header(‘Access-Control-Allow-Origin: *’);
header(“Access-Control-Allow-Methods: GET, OPTIONS”);
class MyController extends MY_Controller {
// Export Excel
public function GetDataForExport2Excel($id){
$this->db->select(‘u.username,u.location,u.phone,u.email,u.status’);
$this->db->from(‘user as u’);
$query = $this->db->get();
return $query->result_array();
}
public function ExportDataToExcel(){
// Create file name
$fileName = ‘my-excel.xlsx’;
// load excel library
$this->load->library(‘excel’);
$workplanIfo = $this->GetDataForExport2Excel($id);
$objPHPExcel = new PHPExcel();
$objPHPExcel->setActiveSheetIndex(0);
// set Header
$objPHPExcel->getActiveSheet()->SetCellValue(‘A1’, ‘Name’);
$objPHPExcel->getActiveSheet()->SetCellValue(‘B1’, ‘Location’);
$objPHPExcel->getActiveSheet()->SetCellValue(‘C1’, ‘Phone’);
$objPHPExcel->getActiveSheet()->SetCellValue(‘D1’, ‘Email’);
$objPHPExcel->getActiveSheet()->SetCellValue(‘E1’, ‘Status’);
// set Row
$rowCount = 2;
foreach ($workplanIfo as $element) {
$objPHPExcel->getActiveSheet()->SetCellValue(‘A’ . $rowCount, $element[‘username’]);
$objPHPExcel->getActiveSheet()->SetCellValue(‘B’ . $rowCount, $element[‘location’]);
$objPHPExcel->getActiveSheet()->SetCellValue(‘C’ . $rowCount, $element[‘phone’]);
$objPHPExcel->getActiveSheet()->SetCellValue(‘D’ . $rowCount, $element[’email’]);
$objPHPExcel->getActiveSheet()->SetCellValue(‘E’ . $rowCount, $element[‘status’]);
$rowCount++;
}
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
ob_start();
$objWriter->save(“php://output”);
$xlsData = ob_get_contents();
ob_end_clean();
$response = array(
‘op’ => ‘ok’,
‘file’ => “data:application/vnd.ms-excel;base64,”.base64_encode($xlsData)
);
die(json_encode($response));
}
}
?>
Hope this tutorial will most helpful for you. If you have any query, you can ask here.