ShasScript: codeigniter

Sunday, September 22, 2019

Export to Excel with PHPExcel using Codeigniter

Assalamualaikum/ Hello,

In this post I would like to show how to export data from MySQL to Excel with PHPExcel using Codeigniter.

In Controller

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

 }
}

In Views


<html>
<head>
    <title>Export Data to Excel in Codeigniter using PHPExcel</title>
</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>

In Models


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


}

And Last of all, you need to have to PHPExcel class to make it works..

There the link https://github.com/PHPOffice/PHPExcel

Enjoy, Hope this post will be benefit for all of you. Thank You.

Sya Ahmad
Programmer in Ministry of Health

Upload file using Codeigniter (Excel)

Assalamualaikum/Hello,

In this post I would like to show how to upload a file using codeigniter.

In Controller


public function add() {

  $config = array(
'upload_path' => "./uploads/excel/",
'allowed_types' => "xls|xlsx",
'max_size' => "5000" 
);
$this->load->library('upload', $config);
if($this->upload->do_upload('userfile'))
{
$data = array('upload_data' => $this->upload->data());
}
else
{
$error = array('error' => $this->upload->display_errors());
echo $this->upload->display_errors();
}
}

Important Factors You Need To Consider:

•File/Directory Permission: You must need to make sure that, where you want the uploaded file to be saved, php has proper write permission on those directories. Generally a ‘755’ or ‘777’ permission works fine.

Enjoy, Hope this post will be benefit for all of you. Thank You.

Sya Ahmad
Programmer in Ministry of Health