Friday, 19 August 2016

Export HTML table to Excel File using Jquery with PHP



If you are willing to learn how can we generate excel file by using jquery, so this is the right place in which I will learn you how to use jquery with php programming for export of html table data to Excel file. This is my short PHP web development tutorial in which We will talk about how we can use jquery code for export html table data to excel file format by using PHP. In number of web pages in your web application, you want to give one option for generate excel file from your html data table. With the help of this clean php code execute with the help jquery. You can efficiently build the functionality like export any type of html table data to excel file format by using php programming with Jquery. In this PHP web development tutorial first I will fetch data from mysql table and display that table data on the web page in html table and below html table I have put one button for export html table data to excel file. When user click on that button html data will be exported to excel file. I will jquery code on button click event. This code is working on most of the modern browser like a Old and New Internet Explorer, Google Chrome Fire Fox etc. I wish you have something learn from this post.


Export HTML table to Excel File using Jquery with PHP

Source Code

Database


 --  
 -- Table structure for table `tbl_employee`  
 --  
 CREATE TABLE IF NOT EXISTS `tbl_employee` (  
  `id` int(11) NOT NULL AUTO_INCREMENT,  
  `name` varchar(50) NOT NULL,  
  `gender` varchar(10) NOT NULL,  
  `designation` varchar(100) NOT NULL,  
  PRIMARY KEY (`id`)  
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=22 ;  
 --  
 -- Dumping data for table `tbl_employee`  
 --  
 INSERT INTO `tbl_employee` (`id`, `name`, `gender`, `designation`) VALUES  
 (1, 'Bruce Tom', 'Male', 'Driver'),  
 (5, 'Clara Gilliam', 'Female', 'Programmer'),  
 (6, 'Barbra K. Hurley', 'Female', 'Service technician'),  
 (7, 'Antonio J. Forbes', 'Male', 'Faller'),  
 (8, 'Charles D. Horst', 'Male', 'Financial investigator'),  
 (9, 'Beau L. Clayton', 'Male', 'Extractive metallurgical engin'),  
 (10, 'Ramona W. Burns', 'Female', 'Electronic typesetting machine operator'),  
 (11, 'Jennifer A. Morrison', 'Female', 'Rigging chaser'),  
 (12, 'Susan M. Juarez', 'Female', 'Control and valve installer'),  
 (13, 'Ellan D. Downie', 'Female', 'Education and training manager'),  
 (14, 'Larry T. Williamson', 'Male', 'Teaching assistant'),  
 (15, 'Lauren M. Reynolds', 'Female', 'Internet developer'),  
 (16, 'Joseph L. Judge', 'Male', 'Refrigeration mechanic'),  
 (17, 'Eric C. Lavelle', 'Male', 'Model'),  
 (18, 'Cheryl T. Smithers', 'Female', 'Personal banker'),  
 (19, 'Tonia J. Diaz', 'Female', 'Facilitator'),  
 (20, 'Stephanie P. Lederman', 'Female', 'Mental health aide'),  
 (21, 'Edward F. Sanchez', 'Male', 'Marine oiler');  

index.php


 <?php   
 $connect = mysqli_connect("localhost", "root", "", "testing");  
 $query = "SELECT * FROM tbl_employee";  
 $result = mysqli_query($connect, $query);  
 ?>  
 <!DOCTYPE html>  
 <html>  
      <head>  
           <title>Webslesson Tutorial | Export HTML table to Excel File using Jquery with PHP</title>  
           <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>  
           <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />  
           <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>  
      </head>  
      <body>  
           <br />  
           <div class="container" style="width:700px;">  
                <h3 class="text-center">Export HTML table to Excel File using Jquery with PHP</h3><br />  
                <div class="table-responsive" id="employee_table">  
                     <table class="table table-bordered">  
                          <tr>  
                               <th width="10%">Id</th>  
                               <th width="30%">Name</th>  
                               <th width="10%">Gender</th>  
                               <th width="50%">Designation</th>  
                          </tr>  
                          <?php   
                          while($row = mysqli_fetch_array($result))  
                          {  
                          ?>  
                          <tr>  
                               <td><?php echo $row['id']; ?></td>  
                               <td><?php echo $row['name']; ?></td>  
                               <td><?php echo $row['gender']; ?></td>  
                               <td><?php echo $row['designation']; ?></td>  
                          </tr>  
                          <?php                           
                          }  
                          ?>  
                     </table>  
                </div>  
                <div align="center">  
                     <button name="create_excel" id="create_excel" class="btn btn-success">Create Excel File</button>  
                </div>  
           </div>  
           <br />  
      </body>  
 </html>  
 <script>  
 $(document).ready(function(){  
      $('#create_excel').click(function(){  
           var excel_data = $('#employee_table').html();  
           var page = "excel.php?data=" + excel_data;  
           window.location = page;  
      });  
 });  
 </script>  

excel.php


 <?php  
 //excel.php  
 header('Content-Type: application/vnd.ms-excel');  
 header('Content-disposition: attachment; filename='.rand().'.xls');  
 echo $_GET["data"];  
 ?>  

4 comments:

  1. can you share another way beside using method get? because I always get error 414 Submitted URI too large!

    ReplyDelete
    Replies
    1. Check out how it is done here: https://demos.shieldui.com/web/grid-general/export-to-excel

      Delete
  2. header('Content-Type: application/vnd.ms-excel');
    header('Content-disposition: attachment; filename='.rand().'.xls');
    this code don't open up excel . Why
    www.samiko.co.il

    ReplyDelete
  3. It doesn't work. I'am using mysql only and not mysqli in my table to fetch the data from database... why is it that it the button doesn't work also. thank you

    ReplyDelete