Export Data to Excel and CSV file using PhpSpreadsheet library in CodeIgniter and MySQL

Export data functionality makes your web application user-friendly and helps the user to maintain list data.PhpSpreadsheet is a pure PHP library for reading and writing spreadsheet files. This tutorial will help you understand how Export Data to Excel and CSV files using PhpSpreadsheet library in CodeIgniter and MySQL.As PHPExcel is deprecated, so we will use PhpSpreadsheet to create an excel file with collection data.

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 export data to excel and csv file using PhpSpreadsheet library in CodeIgniter and MySQL, look files structure:

codeigniter-export-excel-csv-file-data-into-mysql
- application
  - config
    - autoload.php
    - constants.php
    - database.php
    - routes.php
  - controllers
    - Phpspreadsheet.php
  - models
    - Site.php
  - views
    - spreadsheet
      - export.php
  - - templates
      - footer.php
      - header.php
- vender
  - phpoffice
- system
- index.php
- assets
  - css
  - upload

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;

  INSERT INTO `import` (`id`, `first_name`, `last_name`, `email`, `dob`, `contact_no`) VALUES
  (1, 'Team', 'Tech Arise', 'info@techarise.com', '21-02-2011', '9000000001'),
  (2, 'Admin', '1st', 'admin@techarise.com', '21-02-2011', '9000000002'),
  (3, 'User', '4rth', 'user@techarise.com', '21-02-2011', '9000000003'),
  (4, 'Editor', '3rd', 'editor@techarise.com', '21-02-2011', '9000000004'),
  (5, 'Writer', '2nd', 'writer@techarise.com', '21-02-2011', '9000000005'),
  (6, 'Contact', 'one', 'contact@techarise.com', '21-02-2011', '9000000006'),
  (7, 'Manager', '1st', 'manager@techarise.com', '21-02-2011', '9000000007'),
  (8, 'Team', 'Tech Arise', 'info@techarise.com', '21-02-2011', '9000000001'),
  (9, 'Team', 'Tech Arise', 'info@techarise.com', '21-02-2011', '9000000001'),
  (10, 'Admin', '1st', 'admin@techarise.com', '21-02-2011', '9000000002');

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

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

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

INSERT INTO `import` (`id`, `first_name`, `last_name`, `email`, `dob`, `contact_no`) VALUES

(1, 'Team', 'Tech Arise', 'info@techarise.com', '21-02-2011', '9000000001'),

(2, 'Admin', '1st', 'admin@techarise.com', '21-02-2011', '9000000002'),

(3, 'User', '4rth', 'user@techarise.com', '21-02-2011', '9000000003'),

(4, 'Editor', '3rd', 'editor@techarise.com', '21-02-2011', '9000000004'),

(5, 'Writer', '2nd', 'writer@techarise.com', '21-02-2011', '9000000005'),

(6, 'Contact', 'one', 'contact@techarise.com', '21-02-2011', '9000000006'),

(7, 'Manager', '1st', 'manager@techarise.com', '21-02-2011', '9000000007'),

(8, 'Team', 'Tech Arise', 'info@techarise.com', '21-02-2011', '9000000001'),

(9, 'Team', 'Tech Arise', 'info@techarise.com', '21-02-2011', '9000000001'),

(10, 'Admin', '1st', 'admin@techarise.com', '21-02-2011', '9000000002');

ALTER TABLE `import`

ADD PRIMARY KEY (`id`);

ALTER TABLE `import`

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

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 Export Model
 *
 * @author TechArise Team
 *
 * @email  info@techarise.com
 */
if (!defined('BASEPATH'))
    exit('No direct script access allowed');

class Site extends CI_Model {
    // 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 Export Model

* @author TechArise Team

* @email info@techarise.com

if (!defined('BASEPATH'))

exit('No direct script access allowed');

class Site extends CI_Model {

// 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');
// Spreadsheet
use PhpOffice\PhpSpreadsheet\Spreadsheet;

class Phpspreadsheet extends CI_Controller {

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

  // export file Xlsx, Xls and Csv
  public function export()
  {
    
    $extension = $this->input->post('export_type');
    if(!empty($extension)){
      $extension = $extension;
    } else {
      $extension = 'xlsx';
    }
    $this->load->helper('download');  
    $data = array();
    $data['title'] = 'Export Excel Sheet | Coders Mag';
    // get employee list
    $empInfo = $this->site->employeeList();
    $fileName = 'employee-'.time(); 
    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();
    
    $sheet->setCellValue('A1', 'First_Name');
        $sheet->setCellValue('B1', 'Last_Name');
        $sheet->setCellValue('C1', 'Email');
        $sheet->setCellValue('D1', 'DOB');
        $sheet->setCellValue('E1', 'Contact_No');

        $rowCount = 2;
        foreach ($empInfo as $element) {
            $sheet->setCellValue('A' . $rowCount, $element['first_name']);
            $sheet->setCellValue('B' . $rowCount, $element['last_name']);
            $sheet->setCellValue('C' . $rowCount, $element['email']);
            $sheet->setCellValue('D' . $rowCount, $element['dob']);
            $sheet->setCellValue('E' . $rowCount, $element['contact_no']);
            $rowCount++;
        }

        if($extension == 'csv'){          
      $writer = new \PhpOffice\PhpSpreadsheet\Writer\Csv($spreadsheet);
      $fileName = $fileName.'.csv';
    } elseif($extension == 'xlsx') {
      $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
      $fileName = $fileName.'.xlsx';
    } else {
      $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xls($spreadsheet);
      $fileName = $fileName.'.xls';
    }

    $this->output->set_header('Content-Type: application/vnd.ms-excel');
    $this->output->set_header("Content-type: application/csv");
    $this->output->set_header('Cache-Control: max-age=0');
    $writer->save(ROOT_UPLOAD_PATH.$fileName); 
    //redirect(HTTP_UPLOAD_PATH.$fileName); 
    $filepath = file_get_contents(ROOT_UPLOAD_PATH.$fileName);
    force_download($fileName, $filepath);
  }
}
?>

<?php

/**

* @package Phpspreadsheet : Phpspreadsheet

* @author TechArise Team

* @email info@techarise.com

* Description of Phpspreadsheet Controller

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

// Spreadsheet

use PhpOffice\PhpSpreadsheet\Spreadsheet;

class Phpspreadsheet extends CI_Controller {

public function __construct()

{

parent::__construct();

// load model

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

}

// index

public function index()

{

$data = array();

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

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

$data['empInfo'] = $this->site->employeeList();

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

}

// export file Xlsx, Xls and Csv

public function export()

{

$extension = $this->input->post('export_type');

if(!empty($extension)){

$extension = $extension;

} else {

$extension = 'xlsx';

}

$this->load->helper('download');

$data = array();

$data['title'] = 'Export Excel Sheet | Coders Mag';

// get employee list

$empInfo = $this->site->employeeList();

$fileName = 'employee-'.time();

$spreadsheet = new Spreadsheet();

$sheet = $spreadsheet->getActiveSheet();

$sheet->setCellValue('A1', 'First_Name');

$sheet->setCellValue('B1', 'Last_Name');

$sheet->setCellValue('C1', 'Email');

$sheet->setCellValue('D1', 'DOB');

$sheet->setCellValue('E1', 'Contact_No');

$rowCount = 2;

foreach ($empInfo as $element) {

$sheet->setCellValue('A' . $rowCount, $element['first_name']);

$sheet->setCellValue('B' . $rowCount, $element['last_name']);

$sheet->setCellValue('C' . $rowCount, $element['email']);

$sheet->setCellValue('D' . $rowCount, $element['dob']);

$sheet->setCellValue('E' . $rowCount, $element['contact_no']);

$rowCount++;

}

if($extension == 'csv'){

$writer = new \PhpOffice\PhpSpreadsheet\Writer\Csv($spreadsheet);

$fileName = $fileName.'.csv';

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

$writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);

$fileName = $fileName.'.xlsx';

} else {

$writer = new \PhpOffice\PhpSpreadsheet\Writer\Xls($spreadsheet);

$fileName = $fileName.'.xls';

}

$this->output->set_header('Content-Type: application/vnd.ms-excel');

$this->output->set_header("Content-type: application/csv");

$this->output->set_header('Cache-Control: max-age=0');

$writer->save(ROOT_UPLOAD_PATH.$fileName);

//redirect(HTTP_UPLOAD_PATH.$fileName);

$filepath = file_get_contents(ROOT_UPLOAD_PATH.$fileName);

force_download($fileName, $filepath);

}

Step 7: Create View:

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

 <?php $this->load->view('templates/header');?>
  <section class="showcase">
    <div class="container">
      <div class="pb-2 mt-4 mb-2 border-bottom">
        <h2>Export Data to Excel and CSV file using PhpSpreadsheet library in CodeIgniter and MySQL</h2>
      </div>
      <form action="<?php print site_url();?>phpspreadsheet/export" class="excel-upl" id="excel-upl" enctype="multipart/form-data" method="post" accept-charset="utf-8">
        <div class="row padall">  
        <div class="col-lg-12">
        <div class="float-right">  
          <input type="radio" checked="checked" name="export_type" value="xlsx"> .xlsx
          <input type="radio" name="export_type" value="xls"> .xls
          <input type="radio" name="export_type" value="csv"> .csv
          <button type="submit" name="import" class="btn btn-primary">Export</button>
          </div> 
        </div>
        </div>
      </form>
      <div class="row">
        <div class="col-lg-12">     
        <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($empInfo as $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>
    </div>
  </section>
 <?php $this->load->view('templates/footer');?>

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

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

</div>

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

<input type="radio" checked="checked" name="export_type" value="xlsx"> .xlsx

<input type="radio" name="export_type" value="xls"> .xls

<input type="radio" name="export_type" value="csv"> .csv

<button type="submit" name="import" class="btn btn-primary">Export</button>

</div>

</form>

<thead>

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

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

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

</tr>

</thead>

<tbody>

<?php foreach($empInfo as $element) {?>

<tr>

</tr>

<?php } ?>

</tbody>

</table>

</div>

</section>

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

Also Read : How to use PHPExcel with CodeIgniter

Demo Download