HTML Table with Export, Sort, Filter and Paging
4.30/5 (8 votes)
Now export your table in Excel across all browsers and enable quick filter and paging
Introduction
Web development includes tables and grids vastly. Today, such application requires tables/grid data to be exportable. Now injecting export functionality is quite complex few times due to cross browser and format complexity. Moreover, having client side quick sort and paging is the cherry on the cake. I just love this quick plugin. All thanks to "DataTable.net".
Background
No expertise is required to learn this. It is the simplest table plugin with so much customization. You just need to create a table in HTML by your code and apply the following code to the table and voila!! You are done !!
So let's start...
Using the Code
Firstly, you need to have create table in your page. The table must have all the table tags to apply this plugin. A table has head and body (and footer sometimes). See the sample below:
STEP 1: Create Table
<table id="example" class="display"
cellspacing="0" width="100%">
<thead>
<tr>
<th>Name</th>
<th>Position</th>
<th>Office</th>
<th>Age</th>
<th>Start date</th>
<th>Salary</th>
</tr>
</thead>
<tbody>
<tr>
<td>Timothy Mooney</td>
<td>Office Manager</td>
<td>London</td>
<td>37</td>
<td>2008/12/11</td>
<td>$136,200</td>
</tr>
<tr>
<td>Jackson Bradshaw</td>
<td>Director</td>
<td>New York</td>
<td>65</td>
<td>2008/09/26</td>
<td>$645,750</td>
</tr>
<tr>
<td>Olivia Liang</td>
<td>Support Engineer</td>
<td>Singapore</td>
<td>64</td>
<td>2011/02/03</td>
<td>$234,500</td>
</tr>
<tr>
<td>Bruno Nash</td>
<td>Software Engineer</td>
<td>London</td>
<td>38</td>
<td>2011/05/03</td>
<td>$163,500</td>
</tr>
<tr>
<td>Sakura Yamamoto</td>
<td>Support Engineer</td>
<td>Tokyo</td>
<td>37</td>
<td>2009/08/19</td>
<td>$139,575</td>
</tr>
<tr>
<td>Thor Walton</td>
<td>Developer</td>
<td>New York</td>
<td>61</td>
<td>2013/08/11</td>
<td>$98,540</td>
</tr>
<tr>
<td>Finn Camacho</td>
<td>Support Engineer</td>
<td>San Francisco</td>
<td>47</td>
<td>2009/07/07</td>
<td>$87,500</td>
</tr>
<tr>
<td>Serge Baldwin</td>
<td>Data Coordinator</td>
<td>Singapore</td>
<td>64</td>
<td>2012/04/09</td>
<td>$138,575</td>
</tr>
<tr>
<td>Zenaida Frank</td>
<td>Software Engineer</td>
<td>New York</td>
<td>63</td>
<td>2010/01/04</td>
<td>$125,250</td>
</tr>
<tr>
<td>Zorita Serrano</td>
<td>Software Engineer</td>
<td>San Francisco</td>
<td>56</td>
<td>2012/06/01</td>
<td>$115,000</td>
</tr>
<tr>
<td>Jennifer Acosta</td>
<td>Junior Javascript Developer</td>
<td>Edinburgh</td>
<td>43</td>
<td>2013/02/01</td>
<td>$75,650</td>
</tr>
<tr>
<td>Cara Stevens</td>
<td>Sales Assistant</td>
<td>New York</td>
<td>46</td>
<td>2011/12/06</td>
<td>$145,600</td>
</tr>
<tr>
<td>Hermione Butler</td>
<td>Regional Director</td>
<td>London</td>
<td>47</td>
<td>2011/03/21</td>
<td>$356,250</td>
</tr>
<tr>
<td>Lael Greer</td>
<td>Systems Administrator</td>
<td>London</td>
<td>21</td>
<td>2009/02/27</td>
<td>$103,500</td>
</tr>
<tr>
<td>Jonas Alexander</td>
<td>Developer</td>
<td>San Francisco</td>
<td>30</td>
<td>2010/07/14</td>
<td>$86,500</td>
</tr>
<tr>
<td>Shad Decker</td>
<td>Regional Director</td>
<td>Edinburgh</td>
<td>51</td>
<td>2008/11/13</td>
<td>$183,000</td>
</tr>
<tr>
<td>Michael Bruce</td>
<td>Javascript Developer</td>
<td>Singapore</td>
<td>29</td>
<td>2011/06/27</td>
<td>$183,000</td>
</tr>
<tr>
<td>Donna Snider</td>
<td>Customer Support</td>
<td>New York</td>
<td>27</td>
<td>2011/01/25</td>
<td>$112,000</td>
</tr>
</tbody>
</table>
As you can see, the above code has the following tags that need to be on the table for the plugin:
<thead><tbody>
STEP 2: Include JQuery & Plugin
Include the following file in the head as JS and CSS files.
<link rel="stylesheet"
href="https://cdn.datatables.net/1.10.12/css/jquery.dataTables.min.css" />
<link rel="stylesheet"
href="https://cdn.datatables.net/buttons/1.2.1/css/buttons.dataTables.min.css" />
<Script src="https://code.jquery.com/jquery-1.12.3.js"
type="text/javascript"></Script>
<Script src="https://cdn.datatables.net/1.10.12/js/jquery.dataTables.min.js"
type="text/javascript"></Script>
<Script src="https://cdn.datatables.net/buttons/1.2.1/js/dataTables.buttons.min.js"
type="text/javascript"></Script>
<Script src="https://cdnjs.cloudflare.com/ajax/libs/jszip/2.5.0/jszip.min.js"
type="text/javascript"></Script>
<Script src="https://cdn.datatables.net/buttons/1.2.1/js/buttons.html5.min.js"
type="text/javascript"></Script>
STEP 3: Include the Plugin with JQuery
Attach the plugin to the table with the following code:
<script>
$(document).ready(function () {
$(document).ready(function () {
$('table').DataTable({
dom: 'Blfrtip',
buttons: [{
text: 'Export To Excel',
extend: 'excelHtml5',
exportOptions: {
modifier: {
selected: true
},
columns: [0, 1, 2, 3],
format: {
header: function (data, columnIdx) {
return data;
},
body: function (data, column, row) {
// Strip $ from salary column to make it numeric
debugger;
return column === 4 ? "" : data;
}
}
},
footer: false,
customize: function (xlsx) {
var sheet = xlsx.xl.worksheets['sheet1.xml'];
//$('c[r=A1] t', sheet).text( 'Custom text' );
//$('row c[r^="C"]', sheet).attr('s', '2');
}
}]
});
});
});
</script>
That's it. Your table is ready with all sort, paging and export functionality. Enjoy coding!!
Points of Interest
As you can see, with very little efforts, you have a great functionlity on client side to sort, filter and export data that is also cross browser. Remember this is just a guid to use the plugin "Datatable.net". For more information, you can visit the plugin website at Datatable.net.
History
I have earlier published an article in tips/tricks section for the export to Excel functionlity, but that has a limitation over different browsers. So you can accept it as a modification of that. Previous article link Export.