How To Export Data To Excel Sheet In PHP MySQL

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.

See also

Table of Contents

Create MySql Connection in PHP

For importing and exporting database in MySql will make a separate file ‘config.php’. Add the following code and replace the database credentials with yours. You can find your db credentials in Application Access details:

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>

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 or problems about this tutorial, please comment on the form below.😊