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"];  
 ?>  

35 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
  4. Tq so much for code its working fine for me

    ReplyDelete
  5. The code is not working the file is downloading in Excel format but there is an eror ..will appear in Excel undefined data.. In Excel. Php in line no 6

    ReplyDelete
  6. Hi man, is not working on any browser, could you help us please

    ReplyDelete
  7. use this one
    header('Content-Type: application/xls');
    header('Content-Disposition: attachment; filename=download.xls');

    ReplyDelete
  8. I am getting below error: [Deprecation] Resource requests whose URLs contained both removed whitespace (`\n`, `\r`, `\t`) characters and less-than characters (`<`) are blocked.

    How to resolve it?

    ReplyDelete
  9. I am receiving below error:

    [Deprecation] Resource requests whose URLs contained both removed whitespace (`\n`, `\r`, `\t`) characters and less-than characters (`<`) are blocked.

    How to resolve it?

    ReplyDelete
  10. why this code is not working in my page

    ReplyDelete
  11. [Deprecation] Resource requests whose URLs contained both removed whitespace (`\n`, `\r`, `\t`) characters and less-than characters (`<`) are blocked. Please remove newlines and encode less-than characters from places like element attribute values in order to load these resources. See https://www.chromestatus.com/feature/5735596811091968 for more details.




    Sir Please Help me


    Chandan kumar
    kumarchandan1703@gmail.com

    ReplyDelete
  12. Will this work in a modal?

    ReplyDelete
  13. Kya hindi me anuwaad ho sakta gai sir

    ReplyDelete
  14. Sir html me banaya gaya program without php run kar sakta hai

    ReplyDelete
  15. Doesnt work for me too. I am sure why

    ReplyDelete
  16. not working! but if i'm to pass value to the url for export it works

    ReplyDelete
  17. how to change location of save file

    ReplyDelete
  18. Great! its work for me...Thank you.

    ReplyDelete
  19. excel file shows only just "excel_data" output only....

    ReplyDelete
  20. var page = "excel.php?data="+excel_data;
    its not working

    when am using below code it shows "excel_data" ouotput...please correct
    var page = "excel.php?data=excel_data";

    ReplyDelete
  21. How can we export the HTML table which contains an iamge. This shows an error -

    The linked image cannot be displayed.
    This file may have been moved, renamed, or deleted.
    Verify that the link points to the correct file and location.

    ReplyDelete
  22. Its given me
    Request-URI Too Long

    The requested URL's length exceeds the capacity limit for this server.

    Additionally, a 414 Request-URI Too Long error was encountered while trying to use an ErrorDocument to handle the request.

    Someone help

    ReplyDelete