Tuesday, 16 February 2016

How to Export MySQL data to Excel in PHP - PHP Tutorial



This tutorial will learn you how to export our mysql data from web application to excel file using php programming language. This functionality is mostly reqired in enterprise level web application. There are lots of data are transfer on daily basis and manage that into seperate excel file. So, at that time this type of functionality is required in web application. This functionality reduce lots of time to take data into excel file.


Source Code


index.php


 <html>  
      <head>  
           <title>Export MySQL data to Excel in PHP</title>  
           <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>  
           <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>  
      </head>  
      <body>  
           <div class="container">  
                <br />  
                <br />  
                <br />  
                <div class="table-responsive">  
                     <h2 align="center">Export MySQL data to Excel in PHP</h2><br />  
                     <div id="live_data"></div>       
                     <form method="post" action="excel.php" >  
                          <input type="submit" name="export_excel" class="btn btn-success" value="Export to Excel" />  
                     </form>  
                </div>  
           </div>  
      </body>  
 </html>  

excel.php


 <?php  
 $connect = mysqli_connect("localhost", "root", "", "test_db");  
 $output = '';  
 if(isset($_POST["export_excel"]))  
 {  
      $sql = "SELECT * FROM tbl_sample ORDER BY id DESC";  
      $result = mysqli_query($connect, $sql);  
      if(mysqli_num_rows($result) > 0)  
      {  
           $output .= '  
                <table class="table" bordered="1">  
                     <tr>  
                          <th>Id</th>  
                          <th>First Name</th>  
                          <th>Last Name</th>  
                     </tr>  
           ';  
           while($row = mysqli_fetch_array($result))  
           {  
                $output .= '  
                     <tr>  
                          <td>'.$row["id"].'</td>  
                          <td>'.$row["first_name"].'</td>  
                          <td>'.$row["last_name"].'</td>  
                     </tr>  
                ';  
           }  
           $output .= '</table>';  
           header("Content-Type: application/xls");   
           header("Content-Disposition: attachment; filename=download.xls");  
           echo $output;  
      }  
 }  
 ?>  

13 comments:

  1. Hi!

    i'd like to THANK YOU, i've been looking for an answer like this a long time ago!!

    i have a problem, hope you can help me...
    i use the whole code, everything you posted, but when i am going to open it says that the format doesnt match the filetype... hope you can help me.
    regards!

    ReplyDelete
  2. Nice one. You can check more tutorials here
    http://skillinfinity.com/blog/topics/php/library

    ReplyDelete
  3. Thanks - just what i needed!

    ReplyDelete
  4. Hi Thanks for the tutorial can you please tell me how we can format the table like changing color of the table

    ReplyDelete
  5. how can we change the extension of the file.

    ReplyDelete
  6. Hey Thanks for sharing. When I export to excel.php I just get a blank page. It doesn't download, copied code just as you haveit on the page.

    ReplyDelete
  7. i have used your code but the data is not getting displayed.but in download file data is there

    ReplyDelete
  8. I found it easy to understand and useful, thanks

    ReplyDelete
  9. This comment has been removed by the author.

    ReplyDelete
  10. thanks great. i want to know how to export in pdf

    ReplyDelete