How to Export DataTables data in PDF, CSV, and Excel format

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.

How to Export DataTables Data in PDF, CSV, And Excel Format makitweb.com


Contents

  1. Table
  2. Configuration
  3. HTML & PHP
  4. Script
  5. Demo
  6. Conclusion

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. 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

View 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.

If you found this tutorial helpful then don't forget to share.

1 thought on “How to Export DataTables data in PDF, CSV, and Excel format”

  1. Excellent job. Thanks
    Could you make a tutorial on same topic but as vuejs component?
    Anyway, your tutorials are always of high interest and serve in many projects. Keep it up.

    Reply

Leave a Comment