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>

41 comments:

  1. Nice tutorial. Absolutely working.

    ReplyDelete
  2. Muy Claro y bueno el código 100%

    ReplyDelete
  3. how to save the file in specific path in the server

    ReplyDelete
  4. Perfect tutorial

    ReplyDelete
  5. i have error in libraries/excel.php
    Message: require_once(PHPExcel.php): failed to open stream: No such file or directory

    pls ,help

    ReplyDelete
    Replies
    1. I assume, you didn't uploaded entire Excel library folder. Check video, there is step by step tutorial, where everything is widely explained.

      Delete
    2. Where is the Excel library found?

      Delete
    3. wheres the video?
      we only add the library name "Excel.php" right?

      Delete
  6. very nice tutorial. It's very helpful. Thank you..

    ReplyDelete
  7. very nice tutorial. It's very helpful. Thank you..

    ReplyDelete
  8. Hi, Is there a way you can implement this in Jqgrid? I have 3 pages in codeigniter with Jqgrid on them, and I'm trying to export the data. Can you help?

    ReplyDelete
  9. The file is correpted and can't opend

    ReplyDelete
  10. when i use thi line $object_writer = PHPExcel_IOFactory::createWriter($object, 'Excel5'); the url is not working.it's getting ERR_INVALID_RESPONSE error

    ReplyDelete
  11. Great Work with Codeigniter version 3 and PHP 7.1
    Thank you very much
    Regards

    ReplyDelete
  12. nice share.. thanks
    but i have problem when data type from excel is date or time
    ini phpmyadmin read as 0000-00-00 ..
    how to solve it ?

    ReplyDelete
  13. Hii I used your described method and it was working fine. After months its showing error in opening excel file and the size of excel is showing as 0.

    ReplyDelete
  14. i have create excel but there is format issue

    ReplyDelete
  15. how i can export excel include image ? thank you

    ReplyDelete
  16. ho i can export excel include image ? thank you

    ReplyDelete
  17. i have an error please help me
    Message: Undefined variable: Employee_data

    Filename: topic/print.php

    Line Number: 16

    ReplyDelete
  18. Message: Undefined variable: Employee_data

    Filename: topic/print.php

    Line Number: 16

    ReplyDelete
  19. i have an error
    Cannot declare class IOFactory, because the name is already in use

    and i don't know what is using it. pls help.

    ReplyDelete
  20. hi, thanks for ur great tutorial but i have an issue.
    1. Undefined method '__construct'. detected in IOFactory file.
    2. and when i clicked the button to export excel i have another message "Unable to locate the specified class: Session.php"

    ReplyDelete
  21. Severity: Compile Error

    Message: Cannot declare class Excel, because the name is already in use

    Filename: libraries/Excel.php

    ReplyDelete
  22. Fatal error: Cannot declare class Excel, because the name is already in use in C:\xampp\htdocs\school\application\libraries\Excel.php on line 5

    ReplyDelete
  23. unexpected variable "$excel_row" error

    ReplyDelete
  24. i tried this it gives broken excel file

    ReplyDelete
  25. plz help me out to solve this issue

    ReplyDelete
  26. its shows your filformat is different your files is corrupted

    ReplyDelete
  27. Working good. I want to add style on excel text field. Please help

    ReplyDelete
  28. Message: require_once(PHPExcel.php): failed to open stream: No such file or directory

    ReplyDelete
  29. div style="border:1px solid #990000;padding-left:20px;margin:0 0 10px 0;"

    h4 A PHP Error was encountered h4

    Severity: Warning
    Message: "continue" targeting switch is equivalent to "break". Did you mean to use "continue 2"?
    Filename: Shared/OLE.php
    Line Number: 288

    ReplyDelete
  30. I am getting blank csv file while importing 5000 rows, is there any waythorugh to solve this issue.

    ReplyDelete
  31. I am new to this development environment
    I copied all the modules as described
    i was able to run the conroller and view
    I can see the result of employee
    but what should be the actual usl of this solution
    Please any one help me

    ReplyDelete
  32. Class 'PHPExcel_Shared_OLE_PPS_File' not found /var/www/html/royalejack/online-bo/app/third_party/PHPExcel/Writer/Excel5.php 181

    ReplyDelete