How to Display SharePoint List in jQuery Datatables

Hello friends, in this article we are going to retrieve data from SharePoint List using REST API and will display that data into jQuery Datatable. So let’s begin…

Show SharePoint List in Datatable format

Step 1:

We have a SharePoint list named EmployeeDetails as shown below with some dummy data. This list has 4 columns (Title, Name, Salary, Address).

EmployeeDetails List
EmployeeDetails List

Step 2 : Create a site page where we are going to show above data in datatable. For creating a page go to Site contents > Site pages > Click on new page icon. In this case I have created a page with name Employee Details.

Step 3 : Insert a Content Editor Webpart into a page.

Employee Details Page
Employee Details Page

Step 4 : Edit the Content Editor Webpart and paste the below code into it.

 

<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/1.10.9/js/jquery.dataTables.min.js"></script>
<link href="https://cdn.datatables.net/1.10.9/css/jquery.dataTables.min.css" rel="Stylesheet" type="text/css" />
<script type="text/javascript" src="../SiteAssets/EmployeeJqueryDatatable.js"></script> 
<div id="DatatablePanel">
<table style="width: 100%;">
<tr>
<td>
<div id="DatatableGrid" style="width: 100%"></div>
</td>
</tr>
</table>
</div>

download above html file here.

Step 5 : If you can obseve the above code, we have referenced a .js file named EmployeeJqueryDatatable.js So we have to first create this js file and upload it into Site assets. Please copy the below code into this js file and named it as EmployeeJqueryDatatable.js

$(document).ready(function() {
 var RestUrl = "../_vti_bin/listdata.svc/EmployeeDetails";
 $.ajax({
 url: RestUrl,
 method: "GET",
 headers: {
 "accept": "application/json;odata=verbose",
 },
 success: function(data) {
 if (data.d.results.length > 0) {
 //construct HTML Table from the JSON Data
 $('#DatatableGrid').append(GenerateTableFromJson(data.d.results));
 //Bind the HTML data with Jquery DataTable
 var oTable = $('#EmployeeTable').dataTable({
 "iDisplayLength": 5,
 "aLengthMenu": [
 [5, 10, 30, 50],
 [5, 10, 30, 50]
 ],
 "sPaginationType": "full_numbers"
 });
 } else {
 $('#DatatableGrid').append("<span>No Employee Details Found.</span>");
 }
 },
 error: function(data) {
 $('#DatatableGrid').append("<span>Error Retreiving Employee Details. Error : " + JSON.stringify(data) + "</span>");
 }
 });
 function GenerateTableFromJson(objArray) {
 var tableContent = '<table id="EmployeeTable" style="width:100%"><thead><tr><td>Title</td>' + '<td>Name</td>' + '<td>Salary</td>' + '<td>Address</td>' + '</tr></thead><tbody>';
 for (var i = 0; i < objArray.length; i++) {
 tableContent += '<tr>';
 tableContent += '<td>' + objArray[i].Title + '</td>';
 tableContent += '<td>' + objArray[i].Name + '</td>';
 tableContent += '<td>' + objArray[i].Salary + '</td>';
 tableContent += '<td>' + objArray[i].Address + '</td>';
 tableContent += '</tr>';
 }
 return tableContent;
 }
 });

download above .js file here

Step 6 : Once you upload the above js file into your site assets, go to our EmployeeDetails page that we ceated in step no. 2. Here we go!!! below is the output for our EmployeeDetails list

Video tutorial:

I have also created a step by step video tutorial for SharePoint Datatables. Please watch it for clear understanding:

https://www.youtube.com/watch?v=gXoeBrXSb3A

I would like to reply to your queries. Please let me know whether you liked this simple step by step article on SharePoint Datatable integration. I am waiting for your comments! Please like our facebook page and subscibe to our newsletter for future updates. Have a nice day!

Leave a Reply

Your email address will not be published. Required fields are marked *