How to export MySQL table data into excel using jQuery

by

How to export MySQL table data into excel using jQuery

Hi friends, in this tutorial we will learn how to export MySQL table data into excel using jQuery plugin. On the other hand, it can also be done with the help of PHP script. We will discuss it in the upcoming tutorial. But for now, in order to export table data successfully, we have to follow the below steps one by one.

  1. Download the jQuery Plugin to export the table data.
    You can also download the plugin from here.
  2. Just make a folder and extract the plugin archive and place the archive folder where you have created your PHP file in the root directory.
  3. Fetch the MySQL table data from the database in the HTML page with the help of the PHP script.

DDL information of the table

CREATE TABLE student (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
student_name varchar(225) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
phone_no varchar(225) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
age int(20) DEFAULT NULL,
date_of_birth date DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

<?php
include('connect.php');
//get the student information from the student table in the database
$stmt = $con->prepare("select * from student");
$stmt->execute();
$student_details = $stmt->fetchAll(PDO::FETCH_ASSOC);
?>

4. Include the source file “table2excel.js” in the script tag as shown below.


<script src="jquery-table2excel-master/src/jquery.table2excel.js"></script>

5. Write the function in the script tag of the HTML page to call the plugin for exporting the table data in the excel format.

<script src="jquery-3.2.1.min.js"></script>
<script src="bootstrap.min.js"></script>
<script src="jquery-table2excel-master/src/jquery.table2excel.js"></script>
<script>
//export to excel
$(".export").click(function(){
  $("#mytable").table2excel({
    // exclude CSS class
    exclude: ".noExl",
    name: "Data",
    filename: "report", //do not include extension
    fileext: ".xls" // file extension
  }); 
});
</script>

Complete Code:-

<?php
include('connect.php');
//get the student information from the student table in the database
$stmt = $con->prepare("select * from student");
$stmt->execute();
$student_details = $stmt->fetchAll(PDO::FETCH_ASSOC);
?>
<html>
<head>
<title>ajax example</title>
<link rel="stylesheet" href="bootstrap.css" crossorigin="anonymous">
<!-- Optional theme -->
<link rel="stylesheet" href="bootstrap-theme.css" crossorigin="anonymous">
<style>
.container{
	width:50%;
	height:30%;
	padding:20px;
}
</style>
</head>

<body>
	<div class="container">
	<h3><u>Export MySql Table Data into Excel Using jQuery</u></h3>
	<br>
	<p><b>Student Information:</b></p>
	<br/>     
	  <table class="table table-bordered" id="mytable">
	    <thead>
	      <tr>
	      	<th>Sl No</th>
	        <th>Student Name</th>
	        <th>Phone No</th>
	        <th>Age</th>
	        <th>Date of Birth</th>
	      </tr>
	    </thead>
	    <tbody>
	<?php
	foreach($student_details as $key=>$value)
	{
		echo '<tr>
			<td>'.($key+1).'</td>
		        <td>'.$value['student_name'].'</td>
		        <td>'.$value['phone_no'].'</td>
		        <td>'.$value['age'].'</td>
		        <td>'.$value['date_of_birth'].'</td>
		      </tr>';
	}
	?>

	    </tbody>
	  </table>
	  <button type="button" class="btn btn-success btn-sm export">Export to Excel</button>
    </div>
<script src="jquery-3.2.1.min.js"></script>
<script src="bootstrap.min.js"></script>
<script src="jquery-table2excel-master/src/jquery.table2excel.js"></script>
<script>
//export to excel
$(".export").click(function(){
  $("#mytable").table2excel({
    // exclude CSS class
    exclude: ".noExl",
    name: "Data",
    filename: "report", //do not include extension
    fileext: ".xls" // file extension
  }); 
});
</script>
</body>
</html>

NOTE*

Download the bootstrap CSS and js files from google and include the path of the files in the href attribute of link tag and src attribute of the script tag respectively.

Also Read, Create Multi-step form submission in PHP using jQuery

CONCLUSION:- I hope this tutorial will help you. If you have any doubt on how to export mysql table data into excel using jQuery, then please leave your comment below


Leave a Comment