Tuesday, 18 April 2017

How to Generate Excel File in Codeigniter using PHPExcel



This tutorial has cover topic like How to export Data from Database to Excel file in Codeigniter Framework by using PHPExcel library. By using this library we can read write and create Excel file in PHP. In one of the Webslesson old post we have already discussing topic like Import and Export Mysql data to Excel in PHP by using PHPExcel Library. But now in this post we have make discussion on how to Generate excel report in Codeigniter.

We all know Excel sheet data is use by most of the ways, so if you have build an application in which you can exchange data from your system to Excel file is one of the required feature in any web application. Via Excel sheet data you can also import data in system or you can also get data from system also. So for this operation we have use PHPExcel library. By using this library we have get any amount of data from our web application.

But suppose you have make your web application in Codeigniter framework then at that time you have to follow rules of MVC framework, because Codeigniter is one of the best MVC Framework, so in this framework you can directly use this PHPExcel library in Codeigniter. So there is a one question how to generate excel file in Codeigniter. So for this we can use this library by two ways. First ways is you have make an helper in Codeigniter, that means you have to paste this library under Codeingier helper folder and make simple PDF helper and in that helper you have to make one function in which you hav to include PHPExcel Class. So Via helper you can use PHPExcel library in Codeigniter. There is one another ways also you have to make your own library also. So for make library into Codeigniter, you have to copy library file under library folder and create one new library class which extends the class of PHPExcel. So this way you can develop your own PDF library in Codeigniter Framework.

Now here we will make PDF library for use of PHPExcel library in Codeigniter, so when we want to access this library we want to just PDF library where we want to use functionality of this library. Here we have share complete required source code for How to Export HTML table to Excel in Codeigniter.






Source Code


Libraries/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();
 }
}

?>


Libraries/IOFactory.php



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

require_once('PHPExcel/IOFactory.php');

class IOFactory extends PHPExcel_IOFactory
{
 public function __construct()
 {
  parent::__construct();
 }
}

?>


Controllers/Excel_export.php



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

class Excel_export extends CI_Controller {
 
 function index()
 {
  $this->load->model("excel_export_model");
  $data["employee_data"] = $this->excel_export_model->fetch_data();
  $this->load->view("excel_export_view", $data);
 }

 function action()
 {
  $this->load->model("excel_export_model");
  $this->load->library("excel");
  $object = new PHPExcel();

  $object->setActiveSheetIndex(0);

  $table_columns = array("Name", "Address", "Gender", "Designation", "Age");

  $column = 0;

  foreach($table_columns as $field)
  {
   $object->getActiveSheet()->setCellValueByColumnAndRow($column, 1, $field);
   $column++;
  }

  $employee_data = $this->excel_export_model->fetch_data();

  $excel_row = 2;

  foreach($employee_data as $row)
  {
   $object->getActiveSheet()->setCellValueByColumnAndRow(0, $excel_row, $row->name);
   $object->getActiveSheet()->setCellValueByColumnAndRow(1, $excel_row, $row->address);
   $object->getActiveSheet()->setCellValueByColumnAndRow(2, $excel_row, $row->gender);
   $object->getActiveSheet()->setCellValueByColumnAndRow(3, $excel_row, $row->designation);
   $object->getActiveSheet()->setCellValueByColumnAndRow(4, $excel_row, $row->age);
   $excel_row++;
  }

  $object_writer = PHPExcel_IOFactory::createWriter($object, 'Excel5');
  header('Content-Type: application/vnd.ms-excel');
  header('Content-Disposition: attachment;filename="Employee Data.xls"');
  $object_writer->save('php://output');
 }

 
 
}


Models/Excel_export_model.php



<?php
class Excel_export_model extends CI_Model
{
 function fetch_data()
 {
  $this->db->order_by("id", "DESC");
  $query = $this->db->get("employee");
  return $query->result();
 }

 
}



Views/excel_export_view.php



<html>
<head>
    <title>Export Data to Excel in Codeigniter using PHPExcel</title>
    
 <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>
 <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
 <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>
    
</head>
<body>
 <div class="container box">
  <h3 align="center">Export Data to Excel in Codeigniter using PHPExcel</h3>
  <br />
  <div class="table-responsive">
   <table class="table table-bordered">
    <tr>
     <th>Name</th>
     <th>Address</th>
     <th>Gender</th>
     <th>Designation</th>
     <th>Age</th>
    </tr>
    <?php
    foreach($employee_data as $row)
    {
     echo '
     <tr>
      <td>'.$row->name.'</td>
      <td>'.$row->address.'</td>
      <td>'.$row->gender.'</td>
      <td>'.$row->designation.'</td>
      <td>'.$row->age.'</td>
     </tr>
     ';
    }
    ?>
   </table>
   <div align="center">
    <form method="post" action="<?php echo base_url(); ?>excel_export/action">
     <input type="submit" name="export" class="btn btn-success" value="Export" />
    </form>
   </div>
   <br />
   <br />
  </div>
 </div>
</body>
</html>

0 comments:

Post a Comment