How to Export MySQL Database data to Excel in PHP MySQLi

Today, We want to share with you export to excel in php.In this post we will show you create excel file in php source code, hear for export html table data to excel in php we will give you demo and example for implement.In this post, we will learn about php export to excel xlsx with an example.

List of all Google Adsense, VueJS, AngularJS, PHP, Laravel Examples.

Database Data Export to Excel File using PHP

Learn to export to excel in php Database data export is a popular CRUD operation for many of us. here simple you can open MySQL phpMyAdmin is the go to choice for a database client in server side PHP. It supported database administration platforms as well as also allows exporting the product, users, employees or many more data. The exported all the data can be in different formats like as a SQL, CSV, text as selected.

When you need a PHP dynamics script, that can export database data (rows) to an excel file, then this article will help you. It has complete information step by step description with example full source code. It is compact with lightweight source code for php download excel file from server that can be latest module integrated in your PHP MySQL web applications.

We have already seen several example source code for implementing database export using PHP.

How to export MySQL data to excel sheet in PHP

index.php

<?php  
$connect = new mysqli('localhost', 'root', '');  
mysqli_select_db($connect, 'love_shops');  
$sql = "SELECT `productid`,`product_name`,`product_desc` FROM `products`";  
$setRec = mysqli_query($connect, $sql);  
$result = '';  
$result = "Product Id" . "\t" . "Product Name" . "\t" . "Product Desc" . "\t";  
$resultsetData = '';  
  while ($rec = mysqli_fetch_row($setRec)) {  
    $row = '';  
    foreach ($rec as $data) {  
        $data = '"' . $data . '"' . "\t";  
        $row .= $data;  
    }  
    $resultsetData .= trim($row) . "\n";  
}  
  
header("Content-type: application/octet-stream");  
header("Content-Disposition: attachment; filename=Product_Detail.xls");  
header("Pragma: no-cache");  
header("Expires: 0");  

  echo ucwords($result) . "\n" . $resultsetData . "\n";  
 ?> 
 

Export MySQL data to Excel in PHP

In this simple bellow example I have learn step by step regarding how to export data to Excel in PHP. If you have developed any large web based project then that web application you have to need this service like Exporting Data to Excel, CSV or PDF Sheet. So I have developed this best Articles, in which I have create a simple PHP with MySQLi Script for Export Data from mysql database to Excel.
index.php

<?php
$conn = mysqli_connect("localhost", "root", "", "love_shops");
$sql = "SELECT * FROM tbl_products";  
$setRec = mysqli_query($conn, $sql);
?>
<html>  
 <head>  
  <title>Export MySQL data to Excel in PHP - www.pakainfo.com</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">free code - Export MySQL data to Excel in PHP - www.pakainfo.com</h2><br /> 
    <table class="table table-bordered">
     <tr>  
                         <th>ProductName</th>  
                         <th>descrptions</th>  
                         <th>brand</th>  
       <th>p Code</th>
       <th>price</th>
                    </tr>
     <?php
     while($rowData = mysqli_fetch_array($setRec))  
     {  
        echo '  
       <tr>  
         <td>'.$rowData["ProductName"].'</td>  
         <td>'.$rowData["descrptions"].'</td>  
         <td>'.$rowData["brand"].'</td>  
         <td>'.$rowData["pcode"].'</td>  
         <td>'.$rowData["price"].'</td>
       </tr>  
        ';  
     }
     ?>
    </table>
    <br />
    <form method="post" action="export-to-excel.php">
     <input type="submit" name="export" class="btn btn-success" value="Export" />
    </form>
   </div>  
  </div>  
 </body>  
</html>

export-to-excel.php

<?php  
//export-to-excel.php  
$conn = mysqli_connect("localhost", "root", "", "love_shops");
$result = '';
if(isset($_POST["export"]))
{
 $query = "SELECT * FROM tbl_products";
 $setRec = mysqli_query($conn, $query);
 if(mysqli_num_rows($setRec) > 0)
 {
  $result .= '
   <table class="table" bordered="1">  
                    <tr>  
                         <th>Name</th>  
                         <th>descrptions</th>  
                         <th>brand</th>  
       <th>p Code</th>
       <th>price</th>
                    </tr>
  ';
  while($rowData = mysqli_fetch_array($setRec))
  {
   $result .= '
    <tr>  
                         <td>'.$rowData["ProductName"].'</td>  
                         <td>'.$rowData["descrptions"].'</td>  
                         <td>'.$rowData["brand"].'</td>  
       <td>'.$rowData["pcode"].'</td>  
       <td>'.$rowData["price"].'</td>
                    </tr>
   ';
  }
  $result .= '</table>';
  header('Content-Type: application/xls');
  header('Content-Disposition: attachment; filename=download.xls');
  echo $result;
 }
}
?>

Web Programming Tutorials Example with Demo

Read :

Summary

You can also read about AngularJS, ASP.NET, VueJs, PHP.

I hope you get an idea about export to excel in php.
I would like to have feedback on my infinityknow.com blog.
Your valuable feedback, question, or comments about this article are always welcome.
If you enjoyed and liked this post, don’t forget to share.


Related FAQ

Here are some more FAQ related to this Article:

  1.   Create Dynamic Image Slider using PHP with jQuery
  2.   jQuery Capture Webcam Image Using Web Camera in PHP
  3.   PHP Split Export convert Multiple Excel Sheet Files
  4.   datepicker change date format dynamically using jquery
  5.   Get Current login Auth user details using Laravel 5.7
  6.   How to Convert date string to date PHP
  7.   WooCommerce Get Product Prices
  8.   jQuery Parsing PHP JSON Data
  9.   Redirect page after delay using Laravel Example
  10.   laravel soft delete relationship Eloquent ORM