Export Data to Excel/CSV in Codeigniter Using PHPExcel

torque-programming 0 Comments 7 min read

Hello there! In this tutorial, we will show you how to export data into Excel or CSV using CodeIgniter. Exporting data into Excel is a great way to store and organize your data. CodeIgniter excel library makes it easy to export your data into Excel or CSV files. So, if you want to learn how to export your data into Excel or CSV, you’re in the right place.

Codeigniter Export Excel,CSV

Contents

Download Codeigniter Latest
Basic Configurations
Download phpExcel Library
Create Library
Create Database With Table
Setup Database Credentials
Make New Controller
Create model
Create Views
Start Development server
Conclusion

Download Codeigniter Latest

In this next step, we’re going to download the newest version of Codeigniter. To do this, go to the website Download Codeigniter and get the most up-to-date setup. After you’ve downloaded it, unzip the setup in your local system’s xampp/htdocs/ folder. You can also rename your project folder to whatever you’d like.

Basic Configuration

Now it’s time to do some basic configuration settings on the config.php file. To do this, we’ll navigate to the application/config/config.php file and open it up in a text editor.

set base url like this:-

$config['base_url'] = 'http://localhost/projects/demo/';

Download PHPExcel Library

Download this excel library here : click here

Next, we need to download phpExcel library from this link, and extract into application/third_party folder. After extract this library move to PHPExcel folder like application/third_party/PHPExcel and also move PHPExcel.php file to application/third_party/PHPExcel.php.

Create Library

Now we need to create Excel.php file into application/library, So go to application/library and create one file name Excel.php and put the below code here.

<?php 
if (!defined('BASEPATH')) exit('No direct script access allowed');  
  
require_once APPPATH."/third_party/PHPExcel.php";
  
class Excel extends PHPExcel {
    public function __construct() {
        parent::__construct();
    }
}

Create Database With Table

For this next step, we’ll need to create a database with the name “demo” To do this, we’ll open up PHPMyAdmin and create the database with that name. Once it’s successfully created, we can use the following SQL query to create a table in the database.

Setup Database Credentials

In the following step, we’ll connect our CRUD app project to the database. To do this, we’ll go to the

application/config/database.php

file and set up our database credentials. In this file, we’ll add our database name, username, and password.

CREATE TABLE import (
    id int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary Key',
    first_name varchar(100) NOT NULL COMMENT 'First Name',
    last_name varchar(100) NOT NULL COMMENT 'Last Name',
    email varchar(255) NOT NULL COMMENT 'Email Address',
    dob varchar(20) NOT NULL COMMENT 'Date of Birth',
    contact_no varchar(50) NOT NULL COMMENT 'Contact No',
    PRIMARY KEY (id)
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='datatable demo table' AUTO_INCREMENT=1;
  INSERT INTO import (id, first_name, last_name, email, dob, contact_no) VALUES
  (1, 'Team', 'test', 'info@test.com', '21-02-2023', '9000000001'),
  (2, 'Admin', 'second', 'admin@test.com', '21-02-2023', '9000000002'),
  (3, 'User', 'third', 'user@test.com', '21-02-2023', '9000000003'),
  (4, 'Editor', 'fouth', 'editor@test.com', '21-02-2023', '9000000004'),
  (5, 'Writer', 'fifth', 'writer@test.com', '21-02-2023', '9000000005'),
  (6, 'Contact', 'sixth', 'contact@test.com', '21-02-2023', '9000000006'),
  (7, 'Manager', 'seven', 'manager@test.com', '21-02-2023', '9000000007');

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

Create Controller

Now we need to create a controller name Export.php. In this controller we will create some method/function. We will build some of the methods like :

Index() – This is used to showing users list
generateXls() – This function is used to export excel or csv sheet

<?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', 'export');
    }    
 
    public function index() {
        $data['export_list'] = $this->export->exportList();
        $this->load->view('export', $data);
    }
    // create xlsx
    public function generateXls() {
        // create file name
        $fileName = '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', 'First Name');
        $objPHPExcel->getActiveSheet()->SetCellValue('B1', 'Last Name');
        $objPHPExcel->getActiveSheet()->SetCellValue('C1', 'Email');
        $objPHPExcel->getActiveSheet()->SetCellValue('D1', 'DOB');
        $objPHPExcel->getActiveSheet()->SetCellValue('E1', 'Contact_No');       
        // 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->email);
            $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $list->dob);
            $objPHPExcel->getActiveSheet()->SetCellValue('E' . $rowCount, $list->contact_no);
            $rowCount++;
        }
        $filename = "tutsmake". 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'); 
 
    }
     
}

Create Model

Now go to application/models folder and create a one model name Export_model.php . After create this model put the below query in to model.

<?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(array('id', 'first_name', 'last_name', 'email', 'dob', 'contact_no'));
            $this->db->from('import');
            $query = $this->db->get();
            return $query->result();
        }
    }

Create Views

Now we need to create export.php, go to application/views/ folder and create export.php file. Here put the below html code for showing list of product.

<!DOCTYPE html>
<html>
<head>
  <title>Codeigniter Export Example - Torque Programming</title>
 <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.1.3/css/bootstrap.min.css" />
<script src="https://code.jquery.com/jquery-3.4.1.min.js"></script>
</head>
<body>
     
    <div class="table-responsive">
    <table class="table table-hover tablesorter">
        <thead>
            <tr>
                <th class="header">First Name</th>
                <th class="header">Last Name</th>                           
                <th class="header">Email</th>                      
                <th class="header">DOB</th>
                <th class="header">Contact Name</th>
            </tr>
        </thead>
        <tbody>
            <?php
            if (isset($export_list) && !empty($export_list)) {
                foreach ($export_list as $key => $list) {
                    ?>
                    <tr>
                        <td><?php echo $list->first_name; ?></td>   
                        <td><?php echo $list->last_name; ?></td> 
                        <td><?php echo $list->email; ?></td>                       
                        <td><?php echo $list->dob; ?></td>
                        <td><?php echo $list->contact_no; ?></td>
                    </tr>
                    <?php
                }
            } else {
                ?>
                <tr>
                    <td colspan="5">There is no employee.</td>    
                </tr>
            <?php } ?>
 
        </tbody>
    </table>
    <a class="pull-right btn btn-primary btn-xs" href="export/generateXls"><i class="fa fa-file-excel-o"></i> Export Data</a>
    </div> 
 
</body>
</html>

Start Development Server

For start development server, Go to the browser and hit below the url.

http://localhost/projects/demo/excel

Conclusion

In this codeigniter excel csv tutorial, we have successfully create a list of users and successfully export data as csv format, If you want to change format like excel,xls, xlse, etc. So you can change easily and download.

You May Also Like

Integrate Stripe Payment Gateway in Codeigniter

Using Chart Js Implement Pie Chart In Codeigniter

Google Bar & Line Charts Days Wise MySQL PHP Codeigniter