We all deal with data and numbers while designing the web applications but what if your client asks you to extract the data from the database and export it to MS Excel file for later review?
In this tutorial, we are going to learn how we can export our data from MySQL database to MS Excel file.
Export data from MySQL to excel using PHP
Let’s begin with creating a simple database ‘Pet_Store’.
Creating the database
Now create a table ‘products’
Table products
Add data to the table created above.
Data View
Next, create two PHP files: petfood.php and petstore_ac.php
petfood.php will display data in form of a table with a link ‘Export to Excel’
<!DOCTYPE html>
<html>
<head>
<link href="https://fonts.googleapis.com/css?family=Open+Sans" rel="stylesheet">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<style>
body{font-family: 'Open Sans', sans-serif; color:#333; font-size:14px;}
#book_form{padding:50px;}
label{display:inline-block; width:140px; }
th, td{width:120px;}
table{width:50%; text-align:left;}
</style>
</head>
<body>
<?php
echo "<table>";
$conn = mysqli_connect("localhost","root","","pet_store");
if(!$conn){
echo "Error: Unable to connect to MySql";
die();
}
echo "<table>
<thead>
<tr>
<th>Product ID</th>
<th>Name</th>
<th>Weight</th>
<th>price</th>
</tr>
</thead>
<tbody>";
$query = "select * from products";
$result = mysqli_query($conn, $query);
while($row = mysqli_fetch_array($result)){
?>
<tr>
<td><?php echo $row['prod_id']; ?></td>
<td><?php echo $row['name']; ?></td>
<td><?php echo $row['weight']; ?></td>
<td><?php echo $row['price']; ?></td>
</tr>
<?php
}
echo "</tbody></table>";
echo "<a href=\"petstore_ac.php\"> Export To Excel </a>";
?>
</body>
</html>
Now, execute petfood.php
Products List
Create the file Petstore_ac.php. This file contains the actual code behind the ‘Export to Excel’ action link.
<?php
header("Content-type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=pet_store.xls");
header("Pragma: no-cache");
header("Expires: 0");
$conn = mysqli_connect("localhost","root","","pet_store");
if(!$conn){
echo "Error: Unable to connect to MySql";
die();
}
$heading = array("Product ID", "Name", "Weight", "Price");
$table_heading = implode("\t", $heading);
$setData = '';
$query = "select * from products";
$result = mysqli_query($conn, $query);
while ($row = mysqli_fetch_row($result)) {
$rowData = '';
foreach ($row as $value) {
$value = '"' . $value . '"' . "\t";
$rowData .= $value;
}
$setData .= trim($rowData) . "\n";
}
echo ucwords($table_heading) . "\n" . $setData . "\n";
?>
Explanation
header(“Content-type: application/vnd.ms-excel”);
The Content-type indicates the media type of the resource – which is Microsoft Excel in this case.
header(“Content-Disposition: attachment; filename=file_name.xls”);
The Content-Disposition is a header indicating if the content is expected to be displayed inline or as a downloadable attachment. In this case, we need data to be downloadable while exporting it to file_name.xls.
Related Articles
Export data from MySQL to JSON using PHP
Comments