Tuesday, 16 February 2016

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


This tutorial will learn you How to export Mysql data from web application to Excel file using PHP programming language. This functionality is mostly required in enterprise level web application. There are lots of data are transfer on daily basis and manage that into separate 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.


In this simple post we have learn something regarding how to export data to Excel in PHP. If you have developed any project then that project you have to required this functionality like Exporting Data to Excel Sheet. So we have developed this tutorial, in which we have make simple PHP Script for Export Data from Web to Excel.



Online Demo


Export MySQL data to Excel in PHP


Name Address City Postal Code Country
Maria Anders Obere Str. 57 Berlin 12209 Germany
Ana Trujillo Avda. de la Construction 2222 Mexico D.F. 5021 Mexico
Antonio Moreno Mataderos 2312 Mexico D.F. 5023 Mexico
Thomas Hardy 120 Hanover Sq. London WA1 1DP UK
Paula Parente Rua do Mercado, 12 Resende 08737-363 Brazil
Wolski Zbyszek ul. Filtrowa 68 Walla 01-012 Poland
Matti Karttunen Keskuskatu 45 Helsinki 21240 Finland
Karl Jablonski 305 - 14th Ave. S. Suite 3B Seattle 98128 USA
Paula Parente Rua do Mercado, 12 Resende 08737-363 Brazil


Source Code



<?php
$connect = mysqli_connect("localhost", "root", "", "testing");
$sql = "SELECT * FROM tbl_customer";  
$result = mysqli_query($connect, $sql);
?>
<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 /> 
    <table class="table table-bordered">
     <tr>  
                         <th>Name</th>  
                         <th>Address</th>  
                         <th>City</th>  
       <th>Postal Code</th>
       <th>Country</th>
                    </tr>
     <?php
     while($row = mysqli_fetch_array($result))  
     {  
        echo '  
       <tr>  
         <td>'.$row["CustomerName"].'</td>  
         <td>'.$row["Address"].'</td>  
         <td>'.$row["City"].'</td>  
         <td>'.$row["PostalCode"].'</td>  
         <td>'.$row["Country"].'</td>
       </tr>  
        ';  
     }
     ?>
    </table>
    <br />
    <form method="post" action="export.php">
     <input type="submit" name="export" class="btn btn-success" value="Export" />
    </form>
   </div>  
  </div>  
 </body>  
</html>


export.php



<?php  
//export.php  
$connect = mysqli_connect("localhost", "root", "", "testing");
$output = '';
if(isset($_POST["export"]))
{
 $query = "SELECT * FROM tbl_customer";
 $result = mysqli_query($connect, $query);
 if(mysqli_num_rows($result) > 0)
 {
  $output .= '
   <table class="table" bordered="1">  
                    <tr>  
                         <th>Name</th>  
                         <th>Address</th>  
                         <th>City</th>  
       <th>Postal Code</th>
       <th>Country</th>
                    </tr>
  ';
  while($row = mysqli_fetch_array($result))
  {
   $output .= '
    <tr>  
                         <td>'.$row["CustomerName"].'</td>  
                         <td>'.$row["Address"].'</td>  
                         <td>'.$row["City"].'</td>  
       <td>'.$row["PostalCode"].'</td>  
       <td>'.$row["Country"].'</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