Dear Reader, let us we know that “How to export data in excel format using Codeigniter“ Using PHPExcel Library So in this Tutorial We Learn Step By Step How to export data in excel format using Codeigniter Process.
An Excel worksheet is a collection of cells where you can keep and manipulate data. Each Excel workbook can contain multiple worksheets.If we want to maintain an Excel worksheet of our website data then we have the option to type one by one data in our excel worksheet, for this provision today we know how to export data from database in Excel sheet using Codeigniter.
First, we see Snapshots of our Export data to Excel format
Step 1:- First of all you need to create the database like ci_demo
Step 2:- Now create a table “mobiles” in a database using the below SQL code
1 2 3 4 5 6 7 8 |
CREATE TABLE `mobiles` ( `id` int(11) NOT NULL, `model_no` varchar(30) NOT NULL, `mobile_name` varchar(30) NOT NULL, `company` varchar(40) NOT NULL, `mobile_category` text NOT NULL, `price` double(16,2) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; |
Step 3:- Now dump some data for table “mobiles” using bellow SQL code
1 2 3 4 5 6 7 8 9 |
INSERT INTO `mobiles` (`id`, `model_no`, `mobile_name`, `company`, `mobile_category`, `price`) VALUES (13, 'SM-G615FZKUINS', 'Samsung Galaxy On Max (Black, ', 'Samsung', 'Smartphones', 20800.00), (14, ' SM-G955FZKGINS', 'Samsung Galaxy S8 Plus (Midnig', 'Samsung', 'Smartphones', 18300.00), (15, 'MN0X2HN/A', 'Apple iPhone 6s (Silver, 32 GB', 'Apple', 'Smartphones', 50000.00), (16, 'MQ8E2HN/A', 'Apple iPhone 8 Plus (Silver, 6', 'Apple', 'Smartphones', 60200.00), (17, ' R1 R829', 'OPPO R1 R829 (Black, 16 GB) (', 'OPPO', 'Smartphones', 19000.00), (18, 'F1', 'OPPO F1 (Gold, 16 GB) (3 GB R', 'OPPO', 'Smartphones', 15500.00), (19, 'MZB5602IN', 'Redmi 4A (Gold, 32 GB) (3 GB ', 'Xiomi', 'Smartphones', 5999.00), (20, 'MZB5653IN', 'Mi A1', 'Xiomi', 'Smartphones', 17500.00); |
Step 4:- Now we need to download PHPExcel library here and extract this in application third_party folder as we can see in below snapshot
Step 4:- Now we need to create a library for load PHPExcel library, create Excel.php file in library folder and paste the below code
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
<?php if (!defined('BASEPATH')) exit('No direct script access allowed'); /* * ======================================= * Author : Web Preparations Team * License : Protected * Email : admin@webpreparations.com * * ======================================= */ require_once APPPATH . "/third_party/PHPExcel.php"; class Excel extends PHPExcel { public function __construct() { parent::__construct(); } } ?> |
Step 5:- Now we will see our controller, so create an Exports Controller in your CI application like Exports.php(Controller)
First, create a constructor in__construct()
this we will load our necessary libraries.
1 2 3 4 5 6 |
// construct public function __construct() { parent::__construct(); // load model $this->load->model('Export', 'export'); } |
Step 6:- In Exports.php Controller first we need to create Index method in this function we will retrieve data from our database(models) and will pass this data to the respective view.
1 2 3 4 5 6 7 8 9 10 |
// export xlsx|xls file public function index() { $data['page'] = 'export-excel'; $data['title'] = 'Export Excel data | Web Preparations'; $data['mobiledata'] = $this->export->mobileList(); // load view file for output $this->load->view('header'); $this->load->view('exports/exports', $data); $this->load->view('footer'); } |
Step 7:- Now we need to create methodcreateXLS()
, this function used to create an excel file and show records from MySQL database.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
<?php // create xlsx public function createXLS() { // create file name $fileName = 'mobile-'.time().'.xlsx'; // load excel library $this->load->library('excel'); $mobiledata = $this->export->mobileList(); $objPHPExcel = new PHPExcel(); $objPHPExcel->setActiveSheetIndex(0); // set Header $objPHPExcel->getActiveSheet()->SetCellValue('A1', 'Model No.'); $objPHPExcel->getActiveSheet()->SetCellValue('B1', 'Mobile Name'); $objPHPExcel->getActiveSheet()->SetCellValue('C1', 'Price'); $objPHPExcel->getActiveSheet()->SetCellValue('D1', 'Company'); $objPHPExcel->getActiveSheet()->SetCellValue('E1', 'Category'); // set Row $rowCount = 2; foreach ($mobiledata as $val) { $objPHPExcel->getActiveSheet()->SetCellValue('A' . $rowCount, $val['model_no']); $objPHPExcel->getActiveSheet()->SetCellValue('B' . $rowCount, $val['mobile_name']); $objPHPExcel->getActiveSheet()->SetCellValue('C' . $rowCount, $val['price']); $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $val['company']); $objPHPExcel->getActiveSheet()->SetCellValue('E' . $rowCount, $val['mobile_category']); $rowCount++; } $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel); $objWriter->save($fileName); // download file header("Content-Type: application/vnd.ms-excel"); redirect(site_url().$fileName); } ?> |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 |
<?php /** * Description of Export Controller * * @author TechArise Team * * @email info@techarise.com */ if (!defined('BASEPATH')) exit('No direct script access allowed'); class Exports extends CI_Controller { // construct public function __construct() { parent::__construct(); // load model $this->load->model('Export', 'export'); } // export xlsx|xls file public function index() { $data['page'] = 'export-excel'; $data['title'] = 'Export Excel data | TechArise'; $data['mobiledata'] = $this->export->mobileList(); // load view file for output $this->load->view('header'); $this->load->view('exports/exports', $data); $this->load->view('footer'); } // create xlsx public function createXLS() { // create file name $fileName = 'mobile-'.time().'.xlsx'; // load excel library $this->load->library('excel'); $mobiledata = $this->export->mobileList(); $objPHPExcel = new PHPExcel(); $objPHPExcel->setActiveSheetIndex(0); // set Header $objPHPExcel->getActiveSheet()->SetCellValue('A1', 'Model No.'); $objPHPExcel->getActiveSheet()->SetCellValue('B1', 'Mobile Name'); $objPHPExcel->getActiveSheet()->SetCellValue('C1', 'Price'); $objPHPExcel->getActiveSheet()->SetCellValue('D1', 'Company'); $objPHPExcel->getActiveSheet()->SetCellValue('E1', 'Category'); // set Row $rowCount = 2; foreach ($mobiledata as $val) { $objPHPExcel->getActiveSheet()->SetCellValue('A' . $rowCount, $val['model_no']); $objPHPExcel->getActiveSheet()->SetCellValue('B' . $rowCount, $val['mobile_name']); $objPHPExcel->getActiveSheet()->SetCellValue('C' . $rowCount, $val['price']); $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $val['company']); $objPHPExcel->getActiveSheet()->SetCellValue('E' . $rowCount, $val['mobile_category']); $rowCount++; } $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel); $objWriter->save($fileName); // download file header("Content-Type: application/vnd.ms-excel"); redirect(site_url().$fileName); } } ?> |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
<?php /** * Description of Export Model * * @author Web Preparations Team * * @email webpreparations@gmail.com */ if (!defined('BASEPATH')) exit('No direct script access allowed'); class Export extends CI_Model { // get mobiles list public function mobileList() { $this->db->select(array('m.id', 'm.model_no', 'm.mobile_name', 'm.company', 'm.price', 'm.mobile_category')); $this->db->from('mobiles as m'); $query = $this->db->get(); return $query->result_array(); } } ?> |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 |
<!DOCTYPE html> <html lang="en"> <head> <link rel="stylesheet" href="<?php echo base_url();?>assets/css/style.css"> <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css"> </head> <title>How to export data in Codeigniter using PHPExcel Library</title> <body> <div class="table-responsive"> <table class="table table-hover tablesorter"> <thead> <tr> <th class="header">Model No.</th> <th class="header">Mobile Name</th> <th class="header">Price</th> <th class="header">Company</th> <th class="header">Category</th> </tr> </thead> <a class="pull-right btn btn-warning btn-large" style="margin-right:40px" href="<?php echo site_url()?>exports/createxls"><i class="fa fa-file-excel-o"></i> Export Data</a> <tbody> <?php if (isset($mobiledata) && !empty($mobiledata)) { foreach ($mobiledata as $key => $val) { ?> <tr> <td><?php echo $val['model_no']; ?></td> <td><?php echo $val['mobile_name']; ?></td> <td><?php echo $val['price']; ?></td> <td><?php echo $val['company']; ?></td> <td><?php echo $val['mobile_category']; ?></td> </tr> <?php } } else { ?> <tr> <td colspan="5" class="alert alert-danger">No Records founds</td> </tr> <?php } ?> </tbody> </table> </div> </body> </html> |
like
http://localhost/ci_demo/exports then you can see the result as in below snapshot, you can Export data by click on Export Data button
Congratulations you have successfully created Export to Excel MySQL data into excel sheet in Codeigniter using PHPExcel library, if you like this post and was helpful for you then share this post on social media and if you have any query then please contact us or comment below, Thanks.
17 Comments:
do an essay November 11, 2017
I know this website presents quality based articles or
reviews and extra information, is there any other web site which gives these kinds of
things in quality?
Webpreparations Team November 13, 2017
Thanks Dear Reader,
Yes of course, you can also follow http://webprepration.com/ .This is our another Blogging Website.
gmail login November 30, 2017
At this time it seems like Movable Type is the best blogging platform available right now.
(from what I’ve read) Is that what you are using on your blog?
Webpreparations Team December 12, 2017
Thanks dear Reader 🙂
NobGorgo December 13, 2017
hello!,I like your writing very much! share we communicate more about your post on AOL? I require a specialist on this area to solve my problem. May be that’s you! Looking forward to see you.
Webpreparations Team December 22, 2017
Thanks a lot dear Reader, you can contact us.
Jewelry March 29, 2018
I don’t even know how I ended up here, but I thought this post was great. I do not know who you are but definitely you’re going to a famous blogger if you are not already 😉 Cheers!
Webpreparations Team March 29, 2018
Thanks
Home Improvement March 29, 2018
Thank you for sharing excellent informations. Your web-site is so cool. I am impressed by the details that you have on this site. It reveals how nicely you perceive this subject. Bookmarked this website page, will come back for extra articles. You, my friend, ROCK! I found simply the information I already searched all over the place and simply couldn’t come across. What an ideal web site.
Webpreparations Team March 29, 2018
Thanks
Home Improvement March 31, 2018
Just desire to say your article is as astonishing. The clearness in your post is just excellent and i can assume you are an expert on this subject. Well with your permission let me to grab your RSS feed to keep up to date with forthcoming post. Thanks a million and please carry on the rewarding work.
Webpreparations Team April 02, 2018
Dear Reader, Thanks for appraising. If you wanna be our subscriber then please subscribe yourself from Newsletter widget by providing your email address there. Thanks again 🙂
Hussam October 20, 2018
hello,
i get the following error message in my log file:
PHP Fatal error: Class ‘ZipArchive’ not found in ( knowing that the code works fine in my localhost, but i face this problem in godaddy shared hosting )
how i can solve it ?
Webpreparations Team October 20, 2018
Hello,
This is server’s problem, please contact to Goddady support team, they will resolve your issue.
For more help please comment here.
Thank you for contact us.
fyzan November 05, 2018
Thank you for sharing this, what if i need to download multiple files in on submit. how to loop it so it can download all files or zip them to download.
harshal December 06, 2018
Unable to load the requested class: Excel
Webpreparations Team December 06, 2018
What is the error message?