Export data from Database to Excel Sheet(.xls) using Codeigniter
Date Posted:13-06-2017
In this post we will explain export data from database using PHPExcel libary
I assume that you are configure or setup the codeigniter
Step 1:Create the mysql table currency,use following code
-- phpMyAdmin SQL Dump
-- version 3.5.2.2
-- http://www.phpmyadmin.net
--
-- Host: 127.0.0.1
-- Generation Time: Jun 13, 2017 at 02:38 PM
-- Server version: 5.5.27
-- PHP Version: 5.4.7
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
--
-- Database: `chatigniter`
--
-- --------------------------------------------------------
--
-- Table structure for table `users`
--
CREATE TABLE IF NOT EXISTS `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`firstname` varchar(255) NOT NULL,
`lastname` varchar(255) NOT NULL,
`email` varchar(255) NOT NULL,
`username` varchar(255) NOT NULL,
`password` varchar(255) NOT NULL,
`avatar` varchar(255) NOT NULL,
`online` enum('1','0') NOT NULL DEFAULT '1',
`created_at` datetime NOT NULL,
`updated_at` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
--
-- Dumping data for table `users`
--
INSERT INTO `users` (`id`, `firstname`, `lastname`, `email`, `username`, `password`, `avatar`, `online`, `created_at`, `updated_at`) VALUES
(1, 'test', 'ytest', 'k@gmail.com', 'karthik', '', '', '1', '0000-00-00 00:00:00', '0000-00-00 00:00:00')
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
step2:Download PHPExcel libarry from it’s github repository or you can download it from PHPExcel official website.
Dwonload PHPExcel library
Dwonload PHPExcel library from GIThub repository from https://phpexcel.codeplex.com/
Step3:
After download you find three differnt folder Classes,Documentation,Examples in PHPExcel.navigate to application/thirdparty extract PHPExcel->Classes->PHPExcel.php and PHPExcel directory directory here.
Step4:
Navigate to application/libraries and create new file name as ExcelExport_lib.php,place following code here
<?php
if (!defined('BASEPATH'))
exit('No direct script access allowed');
class Excel {
private $excel;
public function __construct() {
require_once APPPATH . 'third_party/PHPExcel.php';
$this->excel = new PHPExcel();
}
public function load($path) {
$objReader = PHPExcel_IOFactory::createReader('Excel5');
$this->excel = $objReader->load($path);
}
public function save($path) {
$objWriter = PHPExcel_IOFactory::createWriter($this->excel, 'Excel5');
$objWriter->save($path);
}
public function stream($filename, $data = null) {
if ($data != null) {
$col = 'A';
foreach ($data[0] as $key => $val) {
$objRichText = new PHPExcel_RichText();
$objPayable = $objRichText->createTextRun(str_replace("_", " ", $key));
$this->excel->getActiveSheet()->getCell($col . '1')->setValue($objRichText);
$col++;
}
$rowNumber = 2;
foreach ($data as $row) {
$col = 'A';
foreach ($row as $cell) {
$this->excel->getActiveSheet()->setCellValue($col . $rowNumber, $cell);
$col++;
}
$rowNumber++;
}
}
header('Content-type: application/ms-excel');
header("Content-Disposition: attachment; filename=\"" . $filename . "\"");
header("Cache-control: private");
$objWriter = PHPExcel_IOFactory::createWriter($this->excel, 'Excel5');
// $objWriter->save("export/$filename");
$objWriter->save('php://output'); //change by Pavan
header("location: " . base_url() . "export/$filename");
// unlink(base_url() . "export/$filename");
}
public function __call($name, $arguments) {
if (method_exists($this->excel, $name)) {
return call_user_func_array(array($this->excel, $name), $arguments);
}
return null;
}
}
Step5:
Navigate to application/controllers and create new file name as ExportExcel.php,place following code here
<?php
if ( ! defined('BASEPATH')) exit('No direct script access allowed');
/**
* summary
*/
class ExportExcel extends CI_Controller
{
public function index()
{
$this->load->view('excelexport');
}
public function Export()
{
$this->load->library('excelexport_lib');
$assign_res_data =$this->db->get('users')->result();
// print_r($assign_res_data);die;
$title = date('Y-m-d')."_"."AssignmentReport" ;
/*export data in exlsheet start*/
$this->excelexport_lib->setActiveSheetIndex(0);
//name the worksheet
$this->excelexport_lib->getActiveSheet()->setTitle($title);
//make the font become bold
$this->excelexport_lib->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
$this->excelexport_lib->getActiveSheet()->getStyle('A1')->getFont()->setSize(20);
$this->excelexport_lib->getActiveSheet()->getStyle('A1')->getFill()->getStartColor()->setARGB('#5545');
for($col = ord('A'); $col <= ord('H'); $col++)
{
//set column dimension
$this->excelexport_lib->getActiveSheet()->getColumnDimension(chr($col))->setAutoSize(true);
//change the font size
$this->excelexport_lib->getActiveSheet()->getStyle(chr($col))->getFont()->setSize(12);
$this->excelexport_lib->getActiveSheet()->getStyle(chr($col))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
}
$this->excelexport_lib->stream($title.'.xls', $assign_res_data);
}
}
?>
Step6:
Navigate to application/view and create new file name as excelexport.php,place following code here
<?php
defined('BASEPATH') OR exit('No direct script access allowed');
?><!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<title>Welcome to Export Database</title>
<style type="text/css">
::selection { background-color: #E13300; color: white; }
::-moz-selection { background-color: #E13300; color: white; }
body {
background-color: #fff;
margin: 40px;
font: 13px/20px normal Helvetica, Arial, sans-serif;
color: #4F5155;
}
a {
color: #003399;
background-color: transparent;
font-weight: normal;
}
h1 {
color: #444;
background-color: transparent;
border-bottom: 1px solid #D0D0D0;
font-size: 19px;
font-weight: normal;
margin: 0 0 14px 0;
padding: 14px 15px 10px 15px;
}
code {
font-family: Consolas, Monaco, Courier New, Courier, monospace;
font-size: 12px;
background-color: #f9f9f9;
border: 1px solid #D0D0D0;
color: #002166;
display: block;
margin: 14px 0 14px 0;
padding: 12px 10px 12px 10px;
}
#body {
margin: 0 15px 0 15px;
}
p.footer {
text-align: right;
font-size: 11px;
border-top: 1px solid #D0D0D0;
line-height: 32px;
padding: 0 10px 0 10px;
margin: 20px 0 0 0;
}
#container {
margin: 10px;
border: 1px solid #D0D0D0;
box-shadow: 0 0 8px #D0D0D0;
}
</style>
</head>
<body>
Export Excel!
</body>
</html>
