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
No comments:
Post a Comment