PHPExcel in CodeIgniter Complete Tutorial with Code Download

Hope you know how to configure CodeIgniter, so here we will see how to implement PHPExcel in CodeIgniter for Import and Export MySQL Data in Excel.

STEP: 1

Download the PHPExcel library and paste it into application/third_party/. So your ‘third_party’ folder should have one Folder ‘PHPExcel‘ and one File ‘PHPExcel.php‘

STEP : 2

Import Controller
Paste this code in your controller.

public function import(){
	    error_reporting(0);
	    
	    // load model
        $this->load->model('Import_model', 'import');
        $this->load->helper(array('url','html','form'));
	    
	    date_default_timezone_set('Asia/Kolkata');
	    $c_date             = date("d-m-Y h:i:s A");
	    
	    $type = $this->input->post('type');
	    
	    
	    
	    if($type=="update"){
	        //TRUNCATE table
	        $this->db->truncate('partner');
	    }
	    
	    if ($this->input->post('submit')) {
                 
                $path = 'assets/uploads/';
                require_once APPPATH . "/third_party/PHPExcel.php";
                $config['upload_path'] = $path;
                $config['allowed_types'] = 'xlsx|xls|csv';
                $config['remove_spaces'] = TRUE;
                $this->load->library('upload', $config);
                $this->upload->initialize($config);            
                if (!$this->upload->do_upload('uploadFile')) {
                    $error = array('error' => $this->upload->display_errors());
                } else {
                    $data = array('upload_data' => $this->upload->data());
                }
                if(empty($error)){
                  if (!empty($data['upload_data']['file_name'])) {
                    $import_xls_file = $data['upload_data']['file_name'];
                } else {
                    $import_xls_file = 0;
                }
                $inputFileName = $path . $import_xls_file;
                 
                try {
                    $inputFileType = PHPExcel_IOFactory::identify($inputFileName);
                    $objReader = PHPExcel_IOFactory::createReader($inputFileType);
                    $objPHPExcel = $objReader->load($inputFileName);
                    $allDataInSheet = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true);
                    $flag = true;
                    $i=0;
                    foreach ($allDataInSheet as $value) {
                      if($flag){
                        $flag =false;
                        continue;
                      }
                        $inserdata[$i]['admin_id']      = $value['B'];
                        $inserdata[$i]['name']          = $value['C'];
                        $inserdata[$i]['person_name']   = $value['D'];
                        $inserdata[$i]['email']         = $value['E'];
                        $inserdata[$i]['pwd']           = md5($value['F']);
                        $inserdata[$i]['phone']         = $value['G'];
                        $inserdata[$i]['bp_code']       = $value['H'];
                        $inserdata[$i]['state']         = $value['I'];
                        $inserdata[$i]['pincode']       = $value['J'];
                        $inserdata[$i]['city']          = $value['K'];
                        $inserdata[$i]['address']       = $value['L'];
                        $inserdata[$i]['location_id']   = $value['M'];
                        $inserdata[$i]['gst']           = $value['N'];
                        $inserdata[$i]['c_date']        = $c_date;
                        $inserdata[$i]['otp']           = $value['P'];
                        $inserdata[$i]['status']        = $value['Q'];
                      $i++;
                    }               
                    $result = $this->import->importData($inserdata);   
                    if($result){
                        
                        //Remove file from folder
                            $folder_path = "assets/uploads/";
                            // specified folder
                            $files = glob($folder_path.'/*'); 
                            // Deleting all the files in the list
                            foreach($files as $file) {
                                if(is_file($file)){ 
                                    unlink($file);
                                }
                            }
    			        redirect('home?msg=addOk');
    			    }else{
    			        redirect('home?msg=addErr');
    			    }        
      
              } catch (Exception $e) {
                   die('Error loading file "' . pathinfo($inputFileName, PATHINFO_BASENAME)
                            . '": ' .$e->getMessage());
                }
              }else{
                  echo $error['error'];
                }
                 
                 
        }
	}

100

public function import(){

error_reporting(0);

// load model

$this->load->model('Import_model', 'import');

$this->load->helper(array('url','html','form'));

date_default_timezone_set('Asia/Kolkata');

$c_date = date("d-m-Y h:i:s A");

$type = $this->input->post('type');

if($type=="update"){

//TRUNCATE table

$this->db->truncate('partner');

}

if ($this->input->post('submit')) {

$path = 'assets/uploads/';

require_once APPPATH . "/third_party/PHPExcel.php";

$config['upload_path'] = $path;

$config['allowed_types'] = 'xlsx|xls|csv';

$config['remove_spaces'] = TRUE;

$this->load->library('upload', $config);

$this->upload->initialize($config);

if (!$this->upload->do_upload('uploadFile')) {

$error = array('error' => $this->upload->display_errors());

} else {

$data = array('upload_data' => $this->upload->data());

}

if(empty($error)){

if (!empty($data['upload_data']['file_name'])) {

$import_xls_file = $data['upload_data']['file_name'];

} else {

$import_xls_file = 0;

}

$inputFileName = $path . $import_xls_file;

try {

$inputFileType = PHPExcel_IOFactory::identify($inputFileName);

$objReader = PHPExcel_IOFactory::createReader($inputFileType);

$objPHPExcel = $objReader->load($inputFileName);

$allDataInSheet = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true);

$flag = true;

$i=0;

foreach ($allDataInSheet as $value) {

if($flag){

$flag =false;

continue;

}

$inserdata[$i]['admin_id'] = $value['B'];

$inserdata[$i]['name'] = $value['C'];

$inserdata[$i]['person_name'] = $value['D'];

$inserdata[$i]['email'] = $value['E'];

$inserdata[$i]['pwd'] = md5($value['F']);

$inserdata[$i]['phone'] = $value['G'];

$inserdata[$i]['bp_code'] = $value['H'];

$inserdata[$i]['state'] = $value['I'];

$inserdata[$i]['pincode'] = $value['J'];

$inserdata[$i]['city'] = $value['K'];

$inserdata[$i]['address'] = $value['L'];

$inserdata[$i]['location_id'] = $value['M'];

$inserdata[$i]['gst'] = $value['N'];

$inserdata[$i]['c_date'] = $c_date;

$inserdata[$i]['otp'] = $value['P'];

$inserdata[$i]['status'] = $value['Q'];

$i++;

}

$result = $this->import->importData($inserdata);

if($result){

//Remove file from folder

$folder_path = "assets/uploads/";

// specified folder

$files = glob($folder_path.'/*');

// Deleting all the files in the list

foreach($files as $file) {

if(is_file($file)){

unlink($file);

}

redirect('home?msg=addOk');

}else{

redirect('home?msg=addErr');

}

} catch (Exception $e) {

die('Error loading file "' . pathinfo($inputFileName, PATHINFO_BASENAME)

. '": ' .$e->getMessage());

}

}else{

echo $error['error'];

}

In this Method you can see we are loading a model also “$this->load->model(‘Import_model’, ‘import’);” . so we will create a model later.

STEP : 3

Export Controller
Paste this code in your controller.

public function export() {
	    error_reporting(0);
	     $this->load->model('Export_model', 'export');
        // create file name
        $fileName = 'Partner-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', 'id');
        $objPHPExcel->getActiveSheet()->SetCellValue('B1', 'admin_id');
        $objPHPExcel->getActiveSheet()->SetCellValue('C1', 'name');
        $objPHPExcel->getActiveSheet()->SetCellValue('D1', 'person_name');
        $objPHPExcel->getActiveSheet()->SetCellValue('E1', 'email');
        $objPHPExcel->getActiveSheet()->SetCellValue('F1', 'pwd');
        $objPHPExcel->getActiveSheet()->SetCellValue('G1', 'phone');
        $objPHPExcel->getActiveSheet()->SetCellValue('H1', 'bp_code');
        $objPHPExcel->getActiveSheet()->SetCellValue('I1', 'state');
        $objPHPExcel->getActiveSheet()->SetCellValue('J1', 'pincode');
        $objPHPExcel->getActiveSheet()->SetCellValue('K1', 'city');
        $objPHPExcel->getActiveSheet()->SetCellValue('L1', 'address');
        $objPHPExcel->getActiveSheet()->SetCellValue('M1', 'location_id');
        $objPHPExcel->getActiveSheet()->SetCellValue('N1', 'gst');
        $objPHPExcel->getActiveSheet()->SetCellValue('O1', 'c_date');
        $objPHPExcel->getActiveSheet()->SetCellValue('P1', 'otp');
        $objPHPExcel->getActiveSheet()->SetCellValue('Q1', 'status');
        // set Row
        $rowCount = 2;
        foreach ($listInfo as $list) {
            $objPHPExcel->getActiveSheet()->SetCellValue('A' . $rowCount, $list->id);
            $objPHPExcel->getActiveSheet()->SetCellValue('B' . $rowCount, $list->admin_id);
            $objPHPExcel->getActiveSheet()->SetCellValue('C' . $rowCount, $list->name);
            $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $list->person_name);
            $objPHPExcel->getActiveSheet()->SetCellValue('E' . $rowCount, $list->email);
            $objPHPExcel->getActiveSheet()->SetCellValue('F' . $rowCount, $list->pwd);
            $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, $list->phone);
            $objPHPExcel->getActiveSheet()->SetCellValue('H' . $rowCount, $list->bp_code);
            $objPHPExcel->getActiveSheet()->SetCellValue('I' . $rowCount, $list->state);
            $objPHPExcel->getActiveSheet()->SetCellValue('J' . $rowCount, $list->pincode);
            $objPHPExcel->getActiveSheet()->SetCellValue('K' . $rowCount, $list->city);
            $objPHPExcel->getActiveSheet()->SetCellValue('L' . $rowCount, $list->address);
            $objPHPExcel->getActiveSheet()->SetCellValue('M' . $rowCount, $list->location_id);
            $objPHPExcel->getActiveSheet()->SetCellValue('N' . $rowCount, $list->gst);
            $objPHPExcel->getActiveSheet()->SetCellValue('O' . $rowCount, $list->c_date);
            $objPHPExcel->getActiveSheet()->SetCellValue('P' . $rowCount, $list->otp);
            $objPHPExcel->getActiveSheet()->SetCellValue('Q' . $rowCount, $list->status);
            $rowCount++;
        }
        $filename = "partner-csv-". date("Y-m-d-H-i-s").".csv";
        $fileName = "partner-". date("Y-m-d-H-i-s A").".xlsx";
        
        header( "Content-type: application/vnd.ms-excel" );
        header('Content-Disposition: attachment; filename="'.$fileName.'"');
        header("Pragma: no-cache");
        header("Expires: 0");
        //$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'CSV');
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $objWriter->save('php://output');
        
        redirect('partners');
        
    }

public function export() {

error_reporting(0);

$this->load->model('Export_model', 'export');

// create file name

$fileName = 'Partner-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', 'id');

$objPHPExcel->getActiveSheet()->SetCellValue('B1', 'admin_id');

$objPHPExcel->getActiveSheet()->SetCellValue('C1', 'name');

$objPHPExcel->getActiveSheet()->SetCellValue('D1', 'person_name');

$objPHPExcel->getActiveSheet()->SetCellValue('E1', 'email');

$objPHPExcel->getActiveSheet()->SetCellValue('F1', 'pwd');

$objPHPExcel->getActiveSheet()->SetCellValue('G1', 'phone');

$objPHPExcel->getActiveSheet()->SetCellValue('H1', 'bp_code');

$objPHPExcel->getActiveSheet()->SetCellValue('I1', 'state');

$objPHPExcel->getActiveSheet()->SetCellValue('J1', 'pincode');

$objPHPExcel->getActiveSheet()->SetCellValue('K1', 'city');

$objPHPExcel->getActiveSheet()->SetCellValue('L1', 'address');

$objPHPExcel->getActiveSheet()->SetCellValue('M1', 'location_id');

$objPHPExcel->getActiveSheet()->SetCellValue('N1', 'gst');

$objPHPExcel->getActiveSheet()->SetCellValue('O1', 'c_date');

$objPHPExcel->getActiveSheet()->SetCellValue('P1', 'otp');

$objPHPExcel->getActiveSheet()->SetCellValue('Q1', 'status');

// set Row

$rowCount = 2;

foreach ($listInfo as $list) {

$objPHPExcel->getActiveSheet()->SetCellValue('A' . $rowCount, $list->id);

$objPHPExcel->getActiveSheet()->SetCellValue('B' . $rowCount, $list->admin_id);

$objPHPExcel->getActiveSheet()->SetCellValue('C' . $rowCount, $list->name);

$objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $list->person_name);

$objPHPExcel->getActiveSheet()->SetCellValue('E' . $rowCount, $list->email);

$objPHPExcel->getActiveSheet()->SetCellValue('F' . $rowCount, $list->pwd);

$objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, $list->phone);

$objPHPExcel->getActiveSheet()->SetCellValue('H' . $rowCount, $list->bp_code);

$objPHPExcel->getActiveSheet()->SetCellValue('I' . $rowCount, $list->state);

$objPHPExcel->getActiveSheet()->SetCellValue('J' . $rowCount, $list->pincode);

$objPHPExcel->getActiveSheet()->SetCellValue('K' . $rowCount, $list->city);

$objPHPExcel->getActiveSheet()->SetCellValue('L' . $rowCount, $list->address);

$objPHPExcel->getActiveSheet()->SetCellValue('M' . $rowCount, $list->location_id);

$objPHPExcel->getActiveSheet()->SetCellValue('N' . $rowCount, $list->gst);

$objPHPExcel->getActiveSheet()->SetCellValue('O' . $rowCount, $list->c_date);

$objPHPExcel->getActiveSheet()->SetCellValue('P' . $rowCount, $list->otp);

$objPHPExcel->getActiveSheet()->SetCellValue('Q' . $rowCount, $list->status);

$rowCount++;

}

$filename = "partner-csv-". date("Y-m-d-H-i-s").".csv";

$fileName = "partner-". date("Y-m-d-H-i-s A").".xlsx";

header( "Content-type: application/vnd.ms-excel" );

header('Content-Disposition: attachment; filename="'.$fileName.'"');

header("Pragma: no-cache");

header("Expires: 0");

//$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'CSV');

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');

$objWriter->save('php://output');

redirect('partners');

}

In this Method you can see we are loading a model also “$this->load->model(‘Export_model’, ‘import’);” . so next we will create 2 model one for import and second for Export.

STEP : 4

Import Model
Craete a file ‘Import_model.php‘ in ‘models‘ folder and Paste this code.

<?php
defined('BASEPATH') OR exit('No direct script access allowed');
 
    class Import_model extends CI_Model {
 
        public function __construct()
        {
            $this->load->database();
        }
        
        public function importData($data) {
  
            $res = $this->db->insert_batch('partner_new',$data);
            if($res){
                return TRUE;
            }else{
                return FALSE;
            }
      
        }
    }
?>

<?php

defined('BASEPATH') OR exit('No direct script access allowed');

class Import_model extends CI_Model {

public function __construct()

{

$this->load->database();

}

public function importData($data) {

$res = $this->db->insert_batch('partner_new',$data);

if($res){

return TRUE;

}else{

return FALSE;

}

STEP : 5

Export Model
Craete a file ‘Export_model.php‘ in ‘models‘ folder and Paste this code.

<?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('*');
            $this->db->from('partner_new');
            $query = $this->db->get();
            return $query->result();
        }
    }
?>

<?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('*');

$this->db->from('partner_new');

$query = $this->db->get();

return $query->result();

}

DONWLOAD COMPLETE CODE

STEP: 1

STEP : 2

STEP : 3

STEP : 4

STEP : 5

Leave a Reply Cancel reply