Here we will see how to generate excel report from MySQL database using Codeigniter. Codeigniter is a of the popular PHP framework and it’s easy to work with Codeigniter for now you can export excel file without using any library.

Follow below steps.

• 1. Create a database table in MySQL database.
• 2. And insert the data in MySQL database .
• 3. Then Create a Controller.
• 4. And next create a query fecha data in MySQL database
• 5. And Create a view file.
• 6. And Finaly generate Excel Report

Requirements

To complete this tutorial you will need a fully generate Excel Repor:

• MySQL database
• Codeigniter

1. Create a database table in MySQL database.

    
    CREATE TABLE `students` (
        `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
        `name` varchar(100) NOT NULL,
        `father_name` varchar(100) NOT NULL,
        `roll_number` double NOT NULL,
        `date` varchar(10) NOT NULL,
        PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
    

2. Insert the data in students table .

    
    insert into students(id,name,father_name,roll_number,date) values(1, 'Alamgir','Belel hossen','35000','02-04-2019');
    insert into students(id,name,father_name,roll_number,date) values(2, 'Abdullah','Belel hossen','37030','03-04-2019');
    insert into students(id,name,father_name,roll_number,date) values(3, 'Yanni','Belel hossen','48700','04-04-2019');
    insert into students(id,name,father_name,roll_number,date) values(4, 'Zaman','Belel hossen','17505','05-04-2019');
    insert into students(id,name,father_name,roll_number,date) values(5, 'Alpona','Belel hossen','22080','05-04-2019');
    insert into students(id,name,father_name,roll_number,date) values(6, 'Labonno','Belel hossen','34040','05-04-2019');
    insert into students(id,name,father_name,roll_number,date) values(7, 'Shams','Belel hossen','2500','06-04-2019');
    insert into students(id,name,father_name,roll_number,date) values(8, 'Hasan','Belel hossen','55894','07-04-2019');
    
    

3. Create a controller .

So create a new file inside application/controller/Excel.php

Folder directory application/controller/Excel.php

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

    class Excel extends CI_Controller
    {
        public function __construct()
        {
            parent::__construct();
           $this->load->model('Common_model');
        }

        /*************excel_create this function use**************/
        public function index(){
            $this->data['results'] = $this->Common_model->get_info('students');
            $this->load->view('excel/index', $this->data);
        }

        public function excel_create()
        {
            $this->data['results'] = $this->Common_model->get_info('students');
            $this->load->view('excel/excel_create', $this->data, true);
        }
         
       
    }
        
    

4. Create a model

Then we fetch the students information from MySQL database and write row information to the Excel array.

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

        class Common_model extends CI_Model {

            public function __construct() {
                parent::__construct();
            }

            public function get_info($table) {
                $query = $this->db->get($table);
                $ret = $query->result_array();
                return $ret;
                
            }
        }

        
    

5.Create a view file

Now we will add just one hyperlink on which user will click to generate exce and action excel_create() function.

        
        
       <style>
            table {
                font-family: arial, sans-serif;
                border-collapse: collapse;
                width:
                100%;
            }
            td, th {
                border: 1px solid #E6E0E0;
                text-align: left;
                padding: 8px;
            }
            tr:nth-child(even) {
                background-color: #E6E0E0;
            }

            .button{
                color: #fff;
                background-color: #007bff;
                border-color: #007bff;
                padding: 10px;
            }
        </style>
        <h2 align="center">Excel Report Export</h2>

        //Now we will add just one hyperlink on which user will click to generate exce
   
        <div align="center" >
            <a class="button"  href="Excel/excel_create" class="btn btn-blueviolet btn-xs btn-mini"> Excel Download</a>
        </div>
         <br>
         
        <table align="center" style="width: 70%">
            <thead>
                <tr>
                    <th>Sl</th>
                    <th>Name</th>
                    <th>Father Name</th>
                    <th>Roll Number</th>
                    <th>Date</th>
                </tr>
            </thead>
       
            <tbody>
            	
		<?php foreach ($results as $key => $row): ?>
                <tr>
                    <td><?=$row["id"]</td>
                    <td><?=$row["name"] ?></td>
                    <td><?=$row["father_name"] ?></td>
                    <td><?=$row["roll_number"] ?></td>
                    <td><?=$row["date"] ?></td>
                </tr>
            <?php endforeach; ?>
            </tbody>
        </table>
         
        
    

Finally we write the data to excel file.

        

     
           <html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40">
            <head>

            </head>

            <body>
                <?php 
                    $filename = "Excelreport.xls";
                    header("Content-Type: application/vnd.ms-excel"); //mime type
                    header('Content-Disposition: attachment;filename="'.$filename.'"'); //tell browser what's the file name
                    header("Cache-Control: max-age=0"); //no cache
                
                 ?>                 
                <table >
                <thead>
                    <tr>
                        <th>Sl</th>
                        <th>Name</th>
                        <th>Father Name</th>
                        <th>Roll Number</th>
                        <th>Date</th>
                    </tr>
                </thead>

                <tbody>
                    <?php foreach ($results as $key => $row): ?>                        
                    <tr>
	                    <td><?=$row["id"]</td>
	                    <td><?=$row["name"] ?></td>
	                    <td><?=$row["father_name"] ?></td>
	                    <td><?=$row["roll_number"] ?></td>
	                    <td><?=$row["date"] ?></td>
                    </tr> 
                     <?php  endforeach; ?>
                </tbody>
            </table>
            </body>
            </html>    
	

        
    

7 .Finally Output