How to export to csv, pdf and more using DataTables


Ever wanted to easily export tabular data as csv, pdf, Excel in addition to copying and printing them with just a click in a ColdBox or any web applications and sites? Well, you can by using jQuery and DataTables, and it is as easy as 1-2-3:

  1. Link to the libraries
  2. Initialise the table and buttons
  3. Test the buttons

Link to the Libraries

In the html page head add the following styles for the DataTables table and buttons:


<head>
   <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.12/css/jquery.dataTables.min.css">
   <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/buttons/1.2.2/css/buttons.dataTables.min.css">
</head>

And in the body, link to the libraries:


<body>
   <script src="https://code.jquery.com/jquery-1.12.3.js"></script>
   <script src="https://cdn.datatables.net/1.10.12/js/jquery.dataTables.min.js"></script>
   <script src="https://cdn.datatables.net/buttons/1.2.2/js/dataTables.buttons.min.js"></script>
   <script src="https://cdn.datatables.net/buttons/1.2.2/js/buttons.flash.min.js"></script>
   <script src="https://cdnjs.cloudflare.com/ajax/libs/jszip/2.5.0/jszip.min.js"></script>
   <script src="https://cdn.rawgit.com/bpampuch/pdfmake/0.1.18/build/pdfmake.min.js"></script>
   <script src="https://cdn.rawgit.com/bpampuch/pdfmake/0.1.18/build/vfs_fonts.js"></script>
   <script src="https://cdn.datatables.net/buttons/1.2.2/js/buttons.html5.min.js"></script>
   <script src="https://cdn.datatables.net/buttons/1.2.2/js/buttons.print.min.js"></script>
</body>

Initialise the table and buttons

To initialise the table with an id of summary, and add the buttons (copy,csv,excel,pdf,print), add this JavaScript snippet towards the button of the page:


<script>
$(document).ready(function() {
   $('#summary').dataTable({
      dom: 'Bfrtip',
         lengthMenu: [
            [ 10, 25, 50, 100, -1 ],
            [ '10 rows', '25 rows', '50 rows', '100 rows', 'Show all' ]
         ],
         buttons: [
            'pageLength',
            'copy',
            {
               extend: 'csv',
               title: 'Summary'
            },
            {
               extend: 'excel',
               title: 'Summary'
            },
            {
               extend: 'pdf',
               title: 'Summary'
            },
            'print'
         ]
   });
});
</script>

Notes

  • lengthMenu in combination with pageLength – rows per page to display options – i.e. 10,20,50,100 per page etc
  • extend and title allow us to specify the title to use for display and file name for the csv, Excel and pdf buttons
  • dom parameters (turn on/off – present means it’s on):
    • B – buttons
    • f – filtering input
    • r – processing display element
    • t – The table!
    • i – Table information summary
    • p – pagination control

Test the buttons

Test each of the following buttons:

  • Copy
  • CSV
  • Excel
  • PDF
  • Print

Here’s what you should see if you click the Copy button:

csv-pdf-datatables-2a

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s