How to Export MySQL data to Excel in PHP

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

May 24, 2020

https://www.webslesson.info/2016/02/export-mysql-data-to-excel-in-php-php-tutorial.html

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;
 }
}
?>

Search This Blog

php