DataTables is a popular pagination jQuery library. It has many features like – search filter, sorting, change default page length, etc. One of them is the export feature.
It has the following export options – copy, pdf, csv, and excel.
In this tutorial, I show how you can add export buttons in DataTable.
Contents
1. Table
I am using employees table in the example.
CREATE TABLE `employees` ( `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT, `emp_name` varchar(80) NOT NULL, `salary` varchar(20) NOT NULL, `gender` varchar(10) NOT NULL, `city` varchar(80) NOT NULL, `email` varchar(80) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2. Database Configuration
Create a config.php for the database connection.
Completed Code
<?php
$host = "localhost"; /* Host name */
$user = "root"; /* User */
$password = ""; /* Password */
$dbname = "tutorial"; /* Database name */
$con = mysqli_connect($host, $user, $password,$dbname);
// Check connection
if (!$con) {
die("Connection failed: " . mysqli_connect_error());
}
3. HTML & PHP
Include CSS & JS
Include DataTables and jQuery library. To enable export in DataTables require including other CSS and JS files –
<!-- Datatable CSS -->
<link href='https://cdn.datatables.net/1.12.1/css/jquery.dataTables.min.css' rel='stylesheet' type='text/css'>
<link href='https://cdn.datatables.net/buttons/2.2.3/css/buttons.dataTables.min.css' rel='stylesheet' type='text/css'>
<style type="text/css">
.dt-buttons{
width: 100%;
}
</style>
<!-- jQuery Library -->
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script>
<!-- Datatable JS -->
<script src="https://cdn.datatables.net/1.12.1/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/buttons/2.2.3/js/dataTables.buttons.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jszip/3.1.3/jszip.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.53/pdfmake.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.53/vfs_fonts.js"></script>
<script src="https://cdn.datatables.net/buttons/2.2.3/js/buttons.html5.min.js"></script>
Load Data
Create <table id='empTable' > and fetch all records from employees table.
Loop on fetched records and add <table > rows.
Completed Code
<?php
include "config.php";
?>
<!doctype html>
<html>
<head>
<title>How to Export DataTables data in PDF, CSV, and Excel format</title>
<!-- Datatable CSS -->
<link href='https://cdn.datatables.net/1.12.1/css/jquery.dataTables.min.css' rel='stylesheet' type='text/css'>
<link href='https://cdn.datatables.net/buttons/2.2.3/css/buttons.dataTables.min.css' rel='stylesheet' type='text/css'>
<style type="text/css">
.dt-buttons{
width: 100%;
}
</style>
<!-- jQuery Library -->
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script>
<!-- Datatable JS -->
<script src="https://cdn.datatables.net/1.12.1/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/buttons/2.2.3/js/dataTables.buttons.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jszip/3.1.3/jszip.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.53/pdfmake.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.53/vfs_fonts.js"></script>
<script src="https://cdn.datatables.net/buttons/2.2.3/js/buttons.html5.min.js"></script>
</head>
<body >
<div >
<!-- Table -->
<table id='empTable' class='display dataTable'>
<thead>
<tr>
<th>Employee name</th>
<th>Email</th>
<th>Gender</th>
<th>Salary</th>
<th>City</th>
</tr>
</thead>
<tbody>
<?php
## Fetch records
$empQuery = "select * from employees";
$empRecords = mysqli_query($con, $empQuery);
while ($row = mysqli_fetch_assoc($empRecords)) {
?>
<tr>
<td><?= $row['emp_name'] ?></td>
<td><?= $row['email'] ?></td>
<td><?= $row['gender'] ?></td>
<td><?= $row['salary'] ?></td>
<td><?= $row['city'] ?></td>
</tr>
<?php
}
?>
</tbody>
</table>
</div>
</body>
</html>
4. Script
Initialize DataTables on <table id='empTable' >.
Add dom: 'Blfrtip' and buttons option where specific export button details.
In the example, I am adding copy, pdf, csv, and excel export buttons.
By default, DataTables export all visible columns but you can control it using exportOptions.
Specify the column index position which needs to export in columns. In the example, I specified 0,1 columns for pdf export.
Here, 0 is Employee name, and 1 is Email column.
Use it similarly for other export buttons.
Completed Code
$(document).ready(function(){
var empDataTable = $('#empTable').DataTable({
dom: 'Blfrtip',
buttons: [
{
extend: 'copy'
},
{
extend: 'pdf',
exportOptions: {
columns: [0,1] // Column index which needs to export
}
},
{
extend: 'csv',
},
{
extend: 'excel',
}
]
});
});
5. Demo
6. Conclusion
Using the above script you can easily add export buttons in the DataTables.
But there is one issue officially export feature doesn’t work properly when serverSide: true.
If you have set serverSide: true then I will suggest you create custom buttons to export data instead of using the default one.
