How to Import Excel Data into Mysql Database using Codeigniter

In this post, we have discuss how can we import Excel Sheet data into Mysql database in Codeigniter framework. For this things we will use third party PHPExcel library in Codeigniter for Import Excel sheet data into Mysql Database by using Ajax Jquery. Importing for Excel sheet data into Mysql database that means first we will read excel sheet data and then after we will store into PHP array and lastly we will perform batch operation that means in a single query execution we will insert multiple data. In Codeigniter framework there is one insert_batch() query builder method by using this method we can insert multiple data in single query execution. This all process will cover step by step in Webslesson youtube channel.

How to use PHPExcel Library in Codeigniter framework, so first we have to download PHPExcel Library and put into libraries folder. In that folder we have to create one more Excel.php class. We will extends all property of PHPExcel class library into this class.

Download Source Code

Excel.php

<?php
if (!defined('BASEPATH')) exit('No direct script access allowed');
require_once('PHPExcel.php');

class Excel extends PHPExcel
{
 public function __construct()
 {
  parent::__construct();
 }
}

?>

Here we have make Excel library and this library we will load this library in controller by simply load library method. After this we have to make Excel_import controller for handle http request for Import an Excel file into Mysql using Controller. Under this controller we have make three method like index(), fetch() and import(). index() method will load view file on browser. fetch method has been use by ajax request for fetching customer table data on web page in html table. And last import() method is used for import excel sheet data and read excel sheet file and convert that data into php array and lastly by using codeigniter insert batch method insert or import multiple data in single query execution.

Controllers - Excel_import_model.php

<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Excel_import extends CI_Controller
{
 public function __construct()
 {
  parent::__construct();
  $this->load->model('excel_import_model');
  $this->load->library('excel');
 }

 function index()
 {
  $this->load->view('excel_import');
 }
 
 function fetch()
 {
  $data = $this->excel_import_model->select();
  $output = '
  <h3 align="center">Total Data - '.$data->num_rows().'</h3>
  <table class="table table-striped table-bordered">
   <tr>
    <th>Customer Name</th>
    <th>Address</th>
    <th>City</th>
    <th>Postal Code</th>
    <th>Country</th>
   </tr>
  ';
  foreach($data->result() as $row)
  {
   $output .= '
   <tr>
    <td>'.$row->CustomerName.'</td>
    <td>'.$row->Address.'</td>
    <td>'.$row->City.'</td>
    <td>'.$row->PostalCode.'</td>
    <td>'.$row->Country.'</td>
   </tr>
   ';
  }
  $output .= '</table>';
  echo $output;
 }

 function import()
 {
  if(isset($_FILES["file"]["name"]))
  {
   $path = $_FILES["file"]["tmp_name"];
   $object = PHPExcel_IOFactory::load($path);
   foreach($object->getWorksheetIterator() as $worksheet)
   {
    $highestRow = $worksheet->getHighestRow();
    $highestColumn = $worksheet->getHighestColumn();
    for($row=2; $row<=$highestRow; $row++)
    {
     $customer_name = $worksheet->getCellByColumnAndRow(0, $row)->getValue();
     $address = $worksheet->getCellByColumnAndRow(1, $row)->getValue();
     $city = $worksheet->getCellByColumnAndRow(2, $row)->getValue();
     $postal_code = $worksheet->getCellByColumnAndRow(3, $row)->getValue();
     $country = $worksheet->getCellByColumnAndRow(4, $row)->getValue();
     $data[] = array(
      'CustomerName'  => $customer_name,
      'Address'   => $address,
      'City'    => $city,
      'PostalCode'  => $postal_code,
      'Country'   => $country
     );
    }
   }
   $this->excel_import_model->insert($data);
   echo 'Data Imported successfully';
  } 
 }
}

?>

Below you we have describe Excel_import_model class in models folder for handle database operation for importing or inserting excel sheet data into mysql database in codeigniter framework. In this we have make two select() and insert() method. select() method is used fro fetch whole customer data and return query execution result while insert() method is for insert multipe data in single query execution.

Models - Excel_import_model.php

<?php
class Excel_import_model extends CI_Model
{
 function select()
 {
  $this->db->order_by('CustomerID', 'DESC');
  $query = $this->db->get('tbl_customer');
  return $query;
 }

 function insert($data)
 {
  $this->db->insert_batch('tbl_customer', $data);
 }
}

Lastly we have discuss excel_import.php view file which an output file on browser. From this file user can select excel file for import data into mysql. This file has been load by index() method of Excel_import controller. Under this file we have first make jquery function in which we have use ajax for fetch data from customer table and display on web page in HTML table formate. So when page has been load on web page then it will display latest customer table data on web page. After this we have make one form for import excel sheet data. Here we have use Ajax for import excel sheet data, so when we have submit form data to server then it will import selected excel sheet data into mysql database. When we have submit form data with selected file then it will send request to import() method of Excel_import model and it will import Excel sheet data into mysql table by using PHPExcel library in codeigniter frameowork. If you want complete source code file then you can get by clicking on below link.

<!DOCTYPE html>
<html>
<head>
 <title>How to Import Excel Data into Mysql in Codeigniter</title>
 <link rel="stylesheet" href="<?php echo base_url(); ?>asset/bootstrap.min.css" />
 <script src="<?php echo base_url(); ?>asset/jquery.min.js"></script>
</head>

<body>
 <div class="container">
  <br />
  <h3 align="center">How to Import Excel Data into Mysql in Codeigniter</h3>
  <form method="post" id="import_form" enctype="multipart/form-data">
   <p><label>Select Excel File</label>
   <input type="file" name="file" id="file" required accept=".xls, .xlsx" /></p>
   <br />
   <input type="submit" name="import" value="Import" class="btn btn-info" />
  </form>
  <br />
  <div class="table-responsive" id="customer_data">

  </div>
 </div>
</body>
</html>

<script>
$(document).ready(function(){

 load_data();

 function load_data()
 {
  $.ajax({
   url:"<?php echo base_url(); ?>excel_import/fetch",
   method:"POST",
   success:function(data){
    $('#customer_data').html(data);
   }
  })
 }

 $('#import_form').on('submit', function(event){
  event.preventDefault();
  $.ajax({
   url:"<?php echo base_url(); ?>excel_import/import",
   method:"POST",
   data:new FormData(this),
   contentType:false,
   cache:false,
   processData:false,
   success:function(data){
    $('#file').val('');
    load_data();
    alert(data);
   }
  })
 });

});
</script>