Import Data to Excel and CSV file using PhpSpreadsheet library in CodeIgniter

Using Excel and CSV file you can store all the data and import the Excel and CSV file data into the database at once using PhpSpreadsheet library in CodeIgniter and MySQL.PhpSpreadsheet is a PHP library for reading and writing spreadsheet files. Import Excel and CSV into MySQL help to save the user time and avoid repetitive work.

In this tutorial how to explain Import Data to Excel and CSV file using PhpSpreadsheet library in CodeIgniter and MySQL.As PHPExcel is deprecated, so we will use PhpSpreadsheet to create an excel file with collection data. We will cover this tutorial with a live demo to Import Data to Excel and CSV file using PhpSpreadsheet library in CodeIgniter and MySQL.

Also Read : How to use PHPExcel with CodeIgniter

Step 1: Download and install CodeIgniter.

Step 2: composer or direct download

Run below composer command to download phpspreadsheet library from your project folder. It will create a new folder called “vendor” and it will download phpoffice/phpspreadsheet library.

$ composer require phpoffice/phpspreadsheet Here is my the directory structure after installing phpoffice/phpspreadsheet

3.Setup Composer Autoload

You need to set vendor directory path inside application/config/config.php

<?php
    $config['composer_autoload'] = 'vendor/autoload.php';
?>

<?php

$config['composer_autoload'] = 'vendor/autoload.php';

Before started to implement the import data to excel and csv file using PhpSpreadsheet library in CodeIgniter and MySQL, look files structure:

codeigniter-import-excel-csv-file-data-into-mysql
- application
  - config
    - autoload.php
    - constants.php
    - database.php
    - routes.php
  - controllers
    - Phpspreadsheet.php
  - models
    - Site.php
  - views
    - spreadsheet
      - index.php
      - display.php
    - templates
      - header.php
      - footer.php
- vender
  - phpoffice
- system
- index.php
- assets
  - css
  - uploads
    - sample-xlsx.xlsx
    - sample-xls.xls
    - sample-csv.csv

Step 4: Create MySQL Database and Table

The following SQL creates a customer table in the MySQL database.

<?php
    CREATE TABLE `import` (
      `id` int(11) NOT NULL,
      `first_name` varchar(100) NOT NULL,
      `last_name` varchar(100) NOT NULL,
      `email` varchar(255) NOT NULL,
      `dob` varchar(20) NOT NULL,
      `contact_no` varchar(16) NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

    ALTER TABLE `import`
      ADD PRIMARY KEY (`id`);

    ALTER TABLE `import`
      MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;
?>

<?php

CREATE TABLE `import` (

`id` int(11) NOT NULL,

`first_name` varchar(100) NOT NULL,

`last_name` varchar(100) NOT NULL,

`email` varchar(255) NOT NULL,

`dob` varchar(20) NOT NULL,

`contact_no` varchar(16) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `import`

ADD PRIMARY KEY (`id`);

ALTER TABLE `import`

MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;

Step 5: The Site model handles the database.

Create a file named Site.php inside “application/models folder.

<?php
/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 * Description of Import Model
 *
 * @author Coders Mag Team
 *
 * @email  info@techarise.com
 */
if (!defined('BASEPATH'))
    exit('No direct script access allowed');

class Site extends CI_Model {
    private $_batchImport;
 
    public function setBatchImport($batchImport) {
        $this->_batchImport = $batchImport;
    }
 
    // save data
    public function importData() {
        $data = $this->_batchImport;
        $this->db->insert_batch('import', $data);
    }
    // get employee list
    public function employeeList() {
        $this->db->select(array('e.id', 'e.first_name', 'e.last_name', 'e.email', 'e.dob', 'e.contact_no'));
        $this->db->from('import as e');
        $query = $this->db->get();
        return $query->result_array();
    }
}

?>

<?php

* To change this license header, choose License Headers in Project Properties.

* To change this template file, choose Tools | Templates

* and open the template in the editor.

/**

* Description of Import Model

* @author Coders Mag Team

* @email info@techarise.com

if (!defined('BASEPATH'))

exit('No direct script access allowed');

class Site extends CI_Model {

private $_batchImport;

public function setBatchImport($batchImport) {

$this->_batchImport = $batchImport;

}

// save data

public function importData() {

$data = $this->_batchImport;

$this->db->insert_batch('import', $data);

}

// get employee list

public function employeeList() {

$this->db->select(array('e.id', 'e.first_name', 'e.last_name', 'e.email', 'e.dob', 'e.contact_no'));

$this->db->from('import as e');

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

return $query->result_array();

}

Step 6: Create Controller and load class

Create a controller named Phpspreadsheet.php and use phpspreadsheet library inside controller.Please find below the code for controller.

<?php
/**
 * @package Phpspreadsheet :  Phpspreadsheet
 * @author TechArise Team
 *
 * @email  info@techarise.com
 *   
 * Description of Phpspreadsheet Controller
 */

defined('BASEPATH') OR exit('No direct script access allowed');
//PhpSpreadsheet
use PhpOffice\PhpSpreadsheet\Spreadsheet;

class Phpspreadsheet extends CI_Controller {

    public function __construct()
    {
        parent::__construct();
        // load model
        $this->load->model('Site', 'site');
    }
    // index
    public function import()
    {
        $data = array();
        $data['title'] = 'Import Excel Sheet | TechArise';
        $data['breadcrumbs'] = array('Home' => '#');
        $this->load->view('spreadsheet/index', $data);
    }

    // file upload functionality
    public function upload() {
        $data = array();
        $data['title'] = 'Import Excel Sheet | TechArise';
        $data['breadcrumbs'] = array('Home' => '#');
         // Load form validation library
         $this->load->library('form_validation');
         $this->form_validation->set_rules('fileURL', 'Upload File', 'callback_checkFileValidation');
         if($this->form_validation->run() == false) {
            
            $this->load->view('spreadsheet/index', $data);
         } else {
            // If file uploaded
            if(!empty($_FILES['fileURL']['name'])) { 
                // get file extension
                $extension = pathinfo($_FILES['fileURL']['name'], PATHINFO_EXTENSION);

                if($extension == 'csv'){
                    $reader = new \PhpOffice\PhpSpreadsheet\Reader\Csv();
                } elseif($extension == 'xlsx') {
                    $reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
                } else {
                    $reader = new \PhpOffice\PhpSpreadsheet\Reader\Xls();
                }
                // file path
                $spreadsheet = $reader->load($_FILES['fileURL']['tmp_name']);
                $allDataInSheet = $spreadsheet->getActiveSheet()->toArray(null, true, true, true);
            
                // array Count
                $arrayCount = count($allDataInSheet);
                $flag = 0;
                $createArray = array('First_Name', 'Last_Name', 'Email', 'DOB', 'Contact_No');
                $makeArray = array('First_Name' => 'First_Name', 'Last_Name' => 'Last_Name', 'Email' => 'Email', 'DOB' => 'DOB', 'Contact_No' => 'Contact_No');
                $SheetDataKey = array();
                foreach ($allDataInSheet as $dataInSheet) {
                    foreach ($dataInSheet as $key => $value) {
                        if (in_array(trim($value), $createArray)) {
                            $value = preg_replace('/\s+/', '', $value);
                            $SheetDataKey[trim($value)] = $key;
                        } 
                    }
                }
                $dataDiff = array_diff_key($makeArray, $SheetDataKey);
                if (empty($dataDiff)) {
                    $flag = 1;
                }
                // match excel sheet column
                if ($flag == 1) {
                    for ($i = 2; $i <= $arrayCount; $i++) {
                        $addresses = array();
                        $firstName = $SheetDataKey['First_Name'];
                        $lastName = $SheetDataKey['Last_Name'];
                        $email = $SheetDataKey['Email'];
                        $dob = $SheetDataKey['DOB'];
                        $contactNo = $SheetDataKey['Contact_No'];

                        $firstName = filter_var(trim($allDataInSheet[$i][$firstName]), FILTER_SANITIZE_STRING);
                        $lastName = filter_var(trim($allDataInSheet[$i][$lastName]), FILTER_SANITIZE_STRING);
                        $email = filter_var(trim($allDataInSheet[$i][$email]), FILTER_SANITIZE_EMAIL);
                        $dob = filter_var(trim($allDataInSheet[$i][$dob]), FILTER_SANITIZE_STRING);
                        $contactNo = filter_var(trim($allDataInSheet[$i][$contactNo]), FILTER_SANITIZE_STRING);
                        $fetchData[] = array('first_name' => $firstName, 'last_name' => $lastName, 'email' => $email, 'dob' => $dob, 'contact_no' => $contactNo);
                    }   
                    $data['dataInfo'] = $fetchData;
                    $this->site->setBatchImport($fetchData);
                    $this->site->importData();
                } else {
                    echo "Please import correct file, did not match excel sheet column";
                }
                $this->load->view('spreadsheet/display', $data);
            }              
        }
    }

    // checkFileValidation
    public function checkFileValidation($string) {
      $file_mimes = array('text/x-comma-separated-values', 
        'text/comma-separated-values', 
        'application/octet-stream', 
        'application/vnd.ms-excel', 
        'application/x-csv', 
        'text/x-csv', 
        'text/csv', 
        'application/csv', 
        'application/excel', 
        'application/vnd.msexcel', 
        'text/plain', 
        'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
      );
      if(isset($_FILES['fileURL']['name'])) {
            $arr_file = explode('.', $_FILES['fileURL']['name']);
            $extension = end($arr_file);
            if(($extension == 'xlsx' || $extension == 'xls' || $extension == 'csv') && in_array($_FILES['fileURL']['type'], $file_mimes)){
                return true;
            }else{
                $this->form_validation->set_message('checkFileValidation', 'Please choose correct file.');
                return false;
            }
        }else{
            $this->form_validation->set_message('checkFileValidation', 'Please choose a file.');
            return false;
        }
    }

}

?>

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

<?php

/**

* @package Phpspreadsheet : Phpspreadsheet

* @author TechArise Team

* @email info@techarise.com

* Description of Phpspreadsheet Controller

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

//PhpSpreadsheet

use PhpOffice\PhpSpreadsheet\Spreadsheet;

class Phpspreadsheet extends CI_Controller {

public function __construct()

{

parent::__construct();

// load model

$this->load->model('Site', 'site');

}

// index

public function import()

{

$data = array();

$data['title'] = 'Import Excel Sheet | TechArise';

$data['breadcrumbs'] = array('Home' => '#');

$this->load->view('spreadsheet/index', $data);

}

// file upload functionality

public function upload() {

$data = array();

$data['title'] = 'Import Excel Sheet | TechArise';

$data['breadcrumbs'] = array('Home' => '#');

// Load form validation library

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

$this->form_validation->set_rules('fileURL', 'Upload File', 'callback_checkFileValidation');

if($this->form_validation->run() == false) {

$this->load->view('spreadsheet/index', $data);

} else {

// If file uploaded

if(!empty($_FILES['fileURL']['name'])) {

// get file extension

$extension = pathinfo($_FILES['fileURL']['name'], PATHINFO_EXTENSION);

if($extension == 'csv'){

$reader = new \PhpOffice\PhpSpreadsheet\Reader\Csv();

} elseif($extension == 'xlsx') {

$reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();

} else {

$reader = new \PhpOffice\PhpSpreadsheet\Reader\Xls();

}

// file path

$spreadsheet = $reader->load($_FILES['fileURL']['tmp_name']);

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

// array Count

$arrayCount = count($allDataInSheet);

$flag = 0;

$createArray = array('First_Name', 'Last_Name', 'Email', 'DOB', 'Contact_No');

$makeArray = array('First_Name' => 'First_Name', 'Last_Name' => 'Last_Name', 'Email' => 'Email', 'DOB' => 'DOB', 'Contact_No' => 'Contact_No');

$SheetDataKey = array();

foreach ($allDataInSheet as $dataInSheet) {

foreach ($dataInSheet as $key => $value) {

if (in_array(trim($value), $createArray)) {

$value = preg_replace('/\s+/', '', $value);

$SheetDataKey[trim($value)] = $key;

}

$dataDiff = array_diff_key($makeArray, $SheetDataKey);

if (empty($dataDiff)) {

$flag = 1;

}

// match excel sheet column

if ($flag == 1) {

for ($i = 2; $i <= $arrayCount; $i++) {

$addresses = array();

$firstName = $SheetDataKey['First_Name'];

$lastName = $SheetDataKey['Last_Name'];

$email = $SheetDataKey['Email'];

$dob = $SheetDataKey['DOB'];

$contactNo = $SheetDataKey['Contact_No'];

$firstName = filter_var(trim($allDataInSheet[$i][$firstName]), FILTER_SANITIZE_STRING);

$lastName = filter_var(trim($allDataInSheet[$i][$lastName]), FILTER_SANITIZE_STRING);

$email = filter_var(trim($allDataInSheet[$i][$email]), FILTER_SANITIZE_EMAIL);

$dob = filter_var(trim($allDataInSheet[$i][$dob]), FILTER_SANITIZE_STRING);

$contactNo = filter_var(trim($allDataInSheet[$i][$contactNo]), FILTER_SANITIZE_STRING);

$fetchData[] = array('first_name' => $firstName, 'last_name' => $lastName, 'email' => $email, 'dob' => $dob, 'contact_no' => $contactNo);

}

$data['dataInfo'] = $fetchData;

$this->site->setBatchImport($fetchData);

$this->site->importData();

} else {

echo "Please import correct file, did not match excel sheet column";

}

$this->load->view('spreadsheet/display', $data);

}

// checkFileValidation

public function checkFileValidation($string) {

$file_mimes = array('text/x-comma-separated-values',

'text/comma-separated-values',

'application/octet-stream',

'application/vnd.ms-excel',

'application/x-csv',

'text/x-csv',

'text/csv',

'application/csv',

'application/excel',

'application/vnd.msexcel',

'text/plain',

'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'

);

if(isset($_FILES['fileURL']['name'])) {

$arr_file = explode('.', $_FILES['fileURL']['name']);

$extension = end($arr_file);

if(($extension == 'xlsx' || $extension == 'xls' || $extension == 'csv') && in_array($_FILES['fileURL']['type'], $file_mimes)){

return true;

}else{

$this->form_validation->set_message('checkFileValidation', 'Please choose correct file.');

return false;

}

}else{

$this->form_validation->set_message('checkFileValidation', 'Please choose a file.');

return false;

}

Step 7: Create View:

Create a view named index.php inside application/views directory. Please find the code for view file.

  <?php $this->load->view('templates/header');?>
 <!-- container --> 
  <section class="showcase">
    <div class="container">
      <div class="pb-2 mt-4 mb-2 border-bottom">
        <h2>Import Data to Excel and CSV file using PhpSpreadsheet library in CodeIgniter and MySQL</h2>
      </div>

      <?php if(form_error('fileURL')) {?>    
        <div class="alert alert-danger alert-dismissible">
            <button type="button" class="close" data-dismiss="alert">&times;</button>
            <?php print form_error('fileURL'); ?>
        </div>       
    <?php } ?>
    <div class="row padall border-bottom">  
      <div class="col-lg-12">
      <div class="float-right">  
          <a href="<?php print site_url();?>assets/uploads/sample-xlsx.xlsx" class="btn btn-info btn-sm"><i class="fa fa-file-excel"></i> Sample .XLSX</a>
          <a href="<?php print site_url();?>assets/uploads/sample-xls.xls" class="btn btn-info btn-sm"><i class="fa fa-file-excel"></i> Sample .XLS</a>
          <a href="<?php print site_url();?>assets/uploads/sample-csv.csv" class="btn btn-info btn-sm" target="_blank" ><i class="fa fa-file-csv"></i> Sample .CSV</a>
        </div> 
      </div>
      </div>

    <form action="<?php print site_url();?>phpspreadsheet/upload" class="excel-upl" id="excel-upl" enctype="multipart/form-data" method="post" accept-charset="utf-8">
      <div class="row padall">
        <div class="col-lg-6 order-lg-1">
          
          <input type="file" class="custom-file-input" id="validatedCustomFile" name="fileURL">
          <label class="custom-file-label" for="validatedCustomFile">Choose file...</label>
        </div>
        <div class="col-lg-6 order-lg-2">
          <button type="submit" name="import" class="float-right btn btn-primary">Import</button>
        </div>
      </div>
    </form>
    </div>
  </section>
 <?php $this->load->view('templates/footer');?>

<?php $this->load->view('templates/header');?>

<h2>Import Data to Excel and CSV file using PhpSpreadsheet library in CodeIgniter and MySQL</h2>

</div>

<?php if(form_error('fileURL')) {?>

<?php print form_error('fileURL'); ?>

</div>

<?php } ?>

<a href="<?php print site_url();?>assets/uploads/sample-xlsx.xlsx" class="btn btn-info btn-sm"><i class="fa fa-file-excel"></i> Sample .XLSX</a>

<a href="<?php print site_url();?>assets/uploads/sample-xls.xls" class="btn btn-info btn-sm"><i class="fa fa-file-excel"></i> Sample .XLS</a>

<a href="<?php print site_url();?>assets/uploads/sample-csv.csv" class="btn btn-info btn-sm" target="_blank" ><i class="fa fa-file-csv"></i> Sample .CSV</a>

</div>

<form action="<?php print site_url();?>phpspreadsheet/upload" class="excel-upl" id="excel-upl" enctype="multipart/form-data" method="post" accept-charset="utf-8">

<label class="custom-file-label" for="validatedCustomFile">Choose file...</label>

</div>

<button type="submit" name="import" class="float-right btn btn-primary">Import</button>

</div>

</form>

</div>

</section>

<?php $this->load->view('templates/footer');?>

Step 8: Create View:

Create a view named dispaly.php inside application/views directory. Please find the code for view file.

 <?php $this->load->view('templates/header');?>
 <!-- container --> 
  <section class="showcase">
    <div class="container">
      <div class="pb-2 mt-4 mb-2 border-bottom">
        <h2>Import/Export Data to Excel and CSV file using PhpSpreadsheet library in CodeIgniter and MySQL</h2>
      </div>
      <div class="row padall border-bottom">  
      <div class="col-lg-12">
      <div class="float-right">  
          <a href="<?php print site_url();?>phpspreadsheet" class="btn btn-info btn-sm"><i class="fa fa-file-upload"></i> Back to Upload</a>
        </div> 
      </div>
      </div>
      <div class="row padall">
        
        <table class="table table-striped">
          <thead>
            <tr class="table-primary">
              <th scope="col">First Name</th>
              <th scope="col">Last Name</th>
              <th scope="col">Email</th>
              <th scope="col">DOB</th>
              <th scope="col">Contact No</th>
            </tr>
          </thead>
          <tbody>
            <?php foreach($dataInfo as $key=>$element) { ?>
            <tr>
              <td><?php print $element['first_name'];?></td>
              <td><?php print $element['last_name'];?></td>
              <td><?php print $element['email'];?></td>
              <td><?php print $element['dob'];?></td>
              <td><?php print $element['contact_no'];?></td>
            </tr>
          <?php } ?>
          </tbody>
        </table>
        
      </div>
    </div>
  </section>
 <?php $this->load->view('templates/footer');?>

<?php $this->load->view('templates/header');?>

<h2>Import/Export Data to Excel and CSV file using PhpSpreadsheet library in CodeIgniter and MySQL</h2>

</div>

<a href="<?php print site_url();?>phpspreadsheet" class="btn btn-info btn-sm"><i class="fa fa-file-upload"></i> Back to Upload</a>

</div>

<thead>

<th scope="col">First Name</th>

<th scope="col">Email</th>

<th scope="col">Contact No</th>

</tr>

</thead>

<tbody>

<?php foreach($dataInfo as $key=>$element) { ?>

<tr>

</tr>

<?php } ?>

</tbody>

</table>

</div>

</section>

<?php $this->load->view('templates/footer');?>

Also Read : How to use PHPExcel with CodeIgniter

Demo Download