How to Export data to Excel sheet in PHP MySQL


How to Export data to Excel sheet in PHP MySQL
Hello Guys, In this post well will learn How to export data to excel in PHP MySQL. This function is mainly needed in web applications at the company level. There is a lot of data that is transferred every day and managed in separate Excel files. At that time, this type of functionality was needed in web applications. This function reduces the time needed to retrieve data in an Excel file.
 
 
If you have already developed a project, you need this functionality to export data to an Excel Sheet for this project. That is why we developed this tutorial where we made a simple PHP script to export data from the web to Excel.
 

Here  we are using three file to Export data to excel in PHP Mysql.

1. Create database connectivity file.
2. Create a HTML page and display records
3. Create PHP code for Export data to excel from database Table
 

Create database connectivity file.

First all we need to create a database connection that name is config.php file.
config.php
<?php
// Database configuration
$dbHost = "localhost";
$dbUsername = "root";
$dbPassword = "";
$dbName = "testing";

// Create database connection
$con = new mysqli($dbHost, $dbUsername, $dbPassword, $dbName);

// Check connection
if ($con->connect_error) {
die("Connection failed: " . $con->connect_error);
}
?>
 

Create a HTML page and display records

create a HTML page to display records from the database which you want to export to excel file.
index.php
<!Doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<title>How to Export data to Excel sheet in PHP MySQL</title>
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.0/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.0/js/bootstrap.min.js"></script>
</head>
<body>
<div class="container" style="margin-top:50px">
<h1 style="text-align:center;">How to Export data to Excel sheet in PHP MySQL</h1><br>
<div class="row">
<form action="export_data.php" method="POST" style="margin-left:1010px;margin-bottom: 20px;">
<input type="submit" class="btn btn-success" name="export" value="Export to Excel">
</form>
<table class="table table-striped">
<thead>
<tr>
<th>Id</th>
<th>Name</th>
<th>Email</th>
<th>Username</th>
<th>Age</th>
<th>City</th>
</tr>
</thead>
<?php
// include database connectivity

include_once('config.php');

// fetch data from users table

$query = "SELECT * FROM users ORDER BY id DESC";
$result = mysqli_query($con, $query);
if (mysqli_num_rows($result) > 0) {
while ($data = mysqli_fetch_assoc($result)) {
?>
<tr>
<td><?php echo $data['id']?></td>
<td><?php echo $data['name']?></td>
<td><?php echo $data['email']?></td>
<td><?php echo $data['username']?></td>
<td><?php echo $data['age']?></td>
<td><?php echo $data['city_name']?></td>
</tr>
<?php } } ?>
</table>
</div>
</body>
</html>
 

Create PHP code for Export data to excel from database Table

create PHP code file for Export data to excel from the database table when click the export to excel button.
export_data.php
<?php
// include database connectivity

include_once('config.php');


if (isset($_POST['export'])) {
$query = "SELECT * FROM users ORDER BY id DESC";
$result = mysqli_query($con, $query);
if (mysqli_num_rows($result) > 0) {
$output = "";
$output .= "<table class='table table-striped'>
                <thead>
<tr>
<th>Id</th>
<th>Name</th>
<th>Email</th>
<th>Username</th>
<th>Age</th>
<th>City</th>
</tr>
</thead>"
;
while ($data = mysqli_fetch_assoc($result)) {
$output .= "<tr>
         <td>"
.$data['id']."</td>
<td>"
.$data['name']."</td>
<td>"
.$data['email']."</td>
<td>"
.$data['username']."</td>
<td>"
.$data['age']."</td>
<td>"
.$data['city_name']."</td>
</tr>"
;
}

$output .="</table>";

header('Content-Type: application/xls');
    header('Content-Disposition: attachment; filename=download.xls');

echo $output;
}else{
echo "No record found";
}

}
?>
 
Output
How to export data to excel sheet in PHP MySQL
 
You can always support by sharing on social media or recommending my blog to your friends and colleagues.
If you have any suggestions / problems about this tutorial, please comment on the  form below.😊

Leave a Reply