How To Export Excel and CSV File Using CodeIgniter

How to export data in excel and CSV Files Using CodeIgniter

September 2, 2019 XpertPhp Comments 0 Comment

Today, In this tutorial, we will be explaining how to export Excel and CSV File Using CodeIgniter. so let’s discuss Export Excel and CSV File.

It’s also very helpful in such as if you want to backup of data and you have data of CSV file then you can export the data into the database.

CSV extension stands for “Comma Separated Values” and contains all data in comma-separated. Normally, we have large data and need to export data into the database that time we use the following file types.

Overview

Step 1: Create a Database in table
Step 2: Connect to Database
Step 3: Download PhpExcel Library
Step 4: Create Controller
Step 5: Create a Model
Step 6: Create View File

Step 1: Create a Database in table
In this step, we have to create a table in the database, so we will create a database using the below code.

CREATE TABLE IF NOT EXISTS `register` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(64) NOT NULL,
  `last_name` varchar(64) NOT NULL,
  `address` text NOT NULL,
  `email` varchar(64) NOT NULL,
  `mobile` varchar(12) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=15 ;

CREATE TABLE IF NOT EXISTS `register` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`first_name` varchar(64) NOT NULL,

`last_name` varchar(64) NOT NULL,

`address` text NOT NULL,

`email` varchar(64) NOT NULL,

`mobile` varchar(12) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=15 ;

Step 2: Connect to Database
Go to the config folder and open database.php file some changes in this file like hostname, database username, database password, and database name.

$db['default'] = array(
	'dsn'	=> '',
	'hostname' => 'localhost',
	'username' => 'root',
	'password' => '',
	'database' => 'enter here database name',
	'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
);

$db['default'] = array(

'dsn' => '',

'hostname' => 'localhost',

'username' => 'root',

'password' => '',

'database' => 'enter here database name',

'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

);

Step 3: Download PhpExcel Library
First, we need to Download Download PhpExcel Library. then we will use that third party Library for Codeigniter excel export.
Step 4: Create Controller
In this step, we will create an Export.php file in the “application/controller” directory and paste the below code in this controller.
application/controller/Export.php

<?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');
    }    
 
    public function index() {
        $data['export_list'] = $this->export->exportList();
        $this->load->view('export_excel_file', $data);
    }
	
    public function create_excel() {
        // create file name
        $fileName = 'data-'.time().'.xlsx';  
        // load excel library
        $this->load->library('excel');
        $listInfo = $this->Export_model->getUsers();
        $objPHPExcel = new PHPExcel();
        $objPHPExcel->setActiveSheetIndex(0);
        // set Header
        $objPHPExcel->getActiveSheet()->SetCellValue('A1', 'First Name');
        $objPHPExcel->getActiveSheet()->SetCellValue('B1', 'Last Name');
        $objPHPExcel->getActiveSheet()->SetCellValue('C1', 'Address');
        $objPHPExcel->getActiveSheet()->SetCellValue('D1', 'Email');
        $objPHPExcel->getActiveSheet()->SetCellValue('E1', 'Mobile');       
        // 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->address);
            $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $list->email);
            $objPHPExcel->getActiveSheet()->SetCellValue('E' . $rowCount, $list->mobile);
            $rowCount++;
        }
        $filename = "file". 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'); 
    }     
}
?>

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

}

public function index() {

$data['export_list'] = $this->export->exportList();

$this->load->view('export_excel_file', $data);

}

public function create_excel() {

// create file name

$fileName = 'data-'.time().'.xlsx';

// load excel library

$this->load->library('excel');

$listInfo = $this->Export_model->getUsers();

$objPHPExcel = new PHPExcel();

$objPHPExcel->setActiveSheetIndex(0);

// set Header

$objPHPExcel->getActiveSheet()->SetCellValue('A1', 'First Name');

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

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

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

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

// 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->address);

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

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

$rowCount++;

}

$filename = "file". 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');

}

Step 5: Create a Model
In this step, we will create an Export_model.php file in the “application/models” directory and paste the below code in this model.
application/models/Export_model.php

<?php
defined('BASEPATH') OR exit('No direct script access allowed');
 
    class Export_model extends CI_Model {
 
        public function __construct()
        {
            $this->load->database();
        }
        
        public function getUsers() {
            $this->db->select('*');
            $this->db->from('register');
            $query = $this->db->get();
            return $query->result_array();
        }
    }
?>

<?php

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

class Export_model extends CI_Model {

public function __construct()

{

$this->load->database();

}

public function getUsers() {

$this->db->select('*');

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

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

return $query->result_array();

}

Step 6: Create View File
So finally, we will create the export_excel_file.php file in the “application/views/” directory and make a form with google Recaptcha code in HTML.
application/views/export_excel_file.php

<!DOCTYPE html>
<html lang="en">
<head>
  <title>How To Export Excel and CSV File Using CodeIgniter - XpertPhp</title>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.0/css/bootstrap.min.css">
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.0/js/bootstrap.min.js"></script>
</head>
<body>

<div class="container" style="margin-top:50px;">
  <div class="row">
	<div class="col-lg-10"><h2>CodeIgniter excel Export</h2></div>
	<div class="col-lg-1"><a href="<?php base_url()?>export/create_excel" class="btn btn-success btn-sm">Export</a></div>
	<div class="col-lg-1"></div>
  </div>  
  <table class="table table-striped">
    <thead>
      <tr>
        <th>Id</th>
        <th>Firstname</th>
        <th>Lastname</th>
        <th>Address</th>
        <th>Email</th>
        <th>Mobile</th>
      </tr>
    </thead>
    <tbody>
	<?php
	foreach($user_data as $row) {
	?>
      <tr>
        <td><?php echo $row['id'];?></td>
        <td><?php echo $row['first_name'];?></td>
        <td><?php echo $row['last_name'];?></td>
        <td><?php echo $row['email'];?></td>
        <td><?php echo $row['phone'];?></td>
        <td><?php echo $row['created'];?></td>
      </tr>
	<?php 
	} ?>	
    </tbody>
  </table>
</div>
</body>
</html>

<!DOCTYPE html>

<head>

<title>How To Export Excel and CSV File Using CodeIgniter - XpertPhp</title>

</head>

<body>

<div class="col-lg-10"><h2>CodeIgniter excel Export</h2></div>

<div class="col-lg-1"><a href="<?php base_url()?>export/create_excel" class="btn btn-success btn-sm">Export</a></div>

</div>

<thead>

<tr>

<th>Firstname</th>

<th>Lastname</th>

<th>Address</th>

<th>Email</th>

<th>Mobile</th>

</tr>

</thead>

<tbody>

<?php

foreach($user_data as $row) {

<tr>

</tr>

<?php

} ?>

</tbody>

</table>

</div>

</body>

</html>

Please follow and like us:

Related posts: