{"id":1630,"date":"2017-06-13T18:28:38","date_gmt":"2017-06-13T12:58:38","guid":{"rendered":"https:\/\/blog.pheonixsolutions.com\/?p=1630"},"modified":"2017-06-13T18:28:38","modified_gmt":"2017-06-13T12:58:38","slug":"export-data-database-excel-sheet-xls-using-codeigniter","status":"publish","type":"post","link":"https:\/\/pheonixsolutions.com\/blog\/export-data-database-excel-sheet-xls-using-codeigniter\/","title":{"rendered":"Export data from Database to Excel Sheet(.xls) using Codeigniter"},"content":{"rendered":"

Date Posted:13-06-2017<\/strong>
\nIn this post\u00a0 we will explain export data from database using PHPExcel libary
\nI assume that you are configure or setup the codeigniter<\/p>\n

Step 1:Create the mysql table currency,use following code<\/p>\n

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