Post
Ask Question

Display SharePoint List Data In Jquery Data Table

Introduction

Everybody knows that technology is changing rapidly. There are many things that we can integrate together. In this article, we are going to see, how to integrate the data table with SharePoint.

So, what is a data table?

As per Data Table’s official site definition:
“Data Table is a plug-in for the jQuery JavaScript library. It is a highly flexible tool, based upon the foundations of the progressive enhancement and will add the advanced interaction controls to any HTML table.”

Scenario

We are starting our scenario.

Before starting with this post, we must have some basic knowledge of the following:

  • HTML Tables
  • Rest API in SharePoint
  • JavaScript Objects

First of all, create a SharePoint list, where we are going to retrieve our data and display it in our data table. For this, go to your SPO site and create a custom list. In my case, my list name is Employee, given below:

Employee

Now, add some columns to display in our list.

column

Some of the default columns are always there, when we add our custom columns to the list. Now, add some dummy data to our list, as shown below:

list

Note: Change the list view as per your requirement.

Now, we move to the functionality part, that suggests how we can get the data from SharePoint list. For this, we need two files; one is HTML file to render the data and another one is JS file to get the data from SharePoint list.

Go to Data Table CDN to get the required JS and CSS files.

We need to follow CDN scripts, that are required to be used in our functionality,

Now, create the HTML file in a text editor (In our case, we are using Sublime Text 3).

  1. <!DOCTYPE html>  
  2. <html>  
  3.   
  4. <head>  
  5.     <title>WiFi Home</title>  
  6.     <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.0.0/jquery.min.js"></script> //External js file to get data from SharePoint List  
  7.     <script type="text/javascript" src="/SiteAssets/GetData_Wifi.js"></script>  
  8.     <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.12/css/jquery.dataTables.min.css">  
  9.     <link rel="stylesheet" type="text/css" href="https://cdnjs.cloudflare.com/ajax/libs/datatables/1.10.12/css/dataTables.jqueryui.min.css">  
  10.     <script type="text/javascript" src="https://cdn.datatables.net/1.10.12/js/jquery.dataTables.min.js"></script>  
  11. </head>  
  12.   
  13. <body>  
  14.     <table id="table_id" class="display" cellspacing="0" width="100%">  
  15.         <thead>  
  16.             <tr>  
  17.                 <th>Name</th>  
  18.                 <th>Position</th>  
  19.                 <th>Office</th>  
  20.                 <th>Age</th>  
  21.                 <th width="18%">Start date</th>  
  22.                 <th>Salary</th>  
  23.             </tr>  
  24.         </thead>  
  25.         <tfoot> </tfoot>  
  26.     </table>  
  27.     </div>  
  28. </body>  
  29.   
  30. </html>  
Now, we have to create JavaScript file (GetData_Wifi.js) to get the data from SharePoint list

For this, write a function to get the data, using REST API.
  1. function loadMyItems() {  
  2.     var siteUrl = _spPageContextInfo.siteAbsoluteUrl;  
  3.     var oDataUrl = siteUrl + "/_api/web/lists/getbytitle('Employee')/items?$select=Name,Position,Office,Age,StartDate,Salary";  
  4.     $.ajax({  
  5.         url: oDataUrl,  
  6.         type: "GET",  
  7.         dataType: "json",  
  8.         headers: {  
  9.             "accept": "application/json;odata=verbose"  
  10.         },  
  11.         success: mySuccHandler,  
  12.         error: myErrHandler  
  13.     });  
  14. }  
Now, the next thing is how to bind this data to our view (HTML) via data table?

For this, let's put our success code in mySuccessHandler function (this is a part of asynchronous programming).
  1. function mySuccHandler(data) {  
  2.     try {  
  3.         var dataTableExample = $('#table_id').DataTable();  
  4.         if (dataTableExample != 'undefined') {  
  5.             dataTableExample.destroy();  
  6.         }  
  7.         dataTableExample = $('#table_id').DataTable({  
  8.             scrollY: 300,  
  9.             "aaData": data.d.results,  
  10.             "aoColumns": [{  
  11.                 "mData": "Name"  
  12.             }, {  
  13.                 "mData": "Position"  
  14.             }, {  
  15.                 "mData": "Office"  
  16.             }, {  
  17.                 "mData": "Age"  
  18.             }, {  
  19.                 "mData": "StartDate",  
  20.                 "render": function(mData) {  
  21.                     var date = new Date(mData);  
  22.                     var month = date.getMonth() + 1;  
  23.                     return (month.length > 1 ? month : "0" + month) + "/" + date.getDate() + "/" + date.getFullYear();  
  24.                 }  
  25.             }, {  
  26.                 "mData": "Salary",  
  27.                 "render": function(mData) {  
  28.                     var sal = new Object(mData);  
  29.                     var commaSep = mData.toString().split(".");  
  30.                     commaSep[0] = commaSep[0].replace(/\B(?=(\d{3})+(?!\d))/g, ",");  
  31.                     sal.rup = "<span>₹</span>";  
  32.                     return ((sal.rup) + " " + commaSep.join("."));  
  33.                 }  
  34.             }]  
  35.         });  
  36.     } catch (e) {  
  37.         alert(e.message);  
  38.     }  
  39. }  
  40.   
  41. function myErrHandler(data, errMessage) {  
  42.     alert("Error: " + errMessage);  
  43. } 
Our final JS file will look, as follows:
  1. $(document).ready(function() {  
  2.     loadMyItems();  
  3. });  
  4.   
  5. function loadMyItems() {  
  6.     var siteUrl = _spPageContextInfo.siteAbsoluteUrl;  
  7.     var oDataUrl = siteUrl + "/_api/web/lists/getbytitle('Employee')/items?$select=Name,Position,Office,Age,StartDate,Salary";  
  8.     $.ajax({  
  9.         url: oDataUrl,  
  10.         type: "GET",  
  11.         dataType: "json",  
  12.         headers: {  
  13.             "accept": "application/json;odata=verbose"  
  14.         },  
  15.         success: mySuccHandler,  
  16.         error: myErrHandler  
  17.     });  
  18. }  
  19.   
  20. function mySuccHandler(data) {  
  21.     try {  
  22.         var dataTableExample = $('#table_id').DataTable();  
  23.         if (dataTableExample != 'undefined') {  
  24.             dataTableExample.destroy();  
  25.         }  
  26.         dataTableExample = $('#table_id').DataTable({  
  27.             scrollY: 300,  
  28.             "aaData": data.d.results,  
  29.             "aoColumns": [{  
  30.                 "mData": "Name"  
  31.             }, {  
  32.                 "mData": "Position"  
  33.             }, {  
  34.                 "mData": "Office"  
  35.             }, {  
  36.                 "mData": "Age"  
  37.             }, {  
  38.                 "mData": "StartDate",  
  39.                 "render": function(mData) {  
  40.                     var date = new Date(mData);  
  41.                     var month = date.getMonth() + 1;  
  42.                     return (month.length > 1 ? month : "0" + month) + "/" + date.getDate() + "/" + date.getFullYear();  
  43.                 }  
  44.             }, {  
  45.                 "mData": "Salary",  
  46.                 "render": function(mData) {  
  47.                     var sal = new Object(mData);  
  48.                     var commaSep = mData.toString().split(".");  
  49.                     commaSep[0] = commaSep[0].replace(/\B(?=(\d{3})+(?!\d))/g, ",");  
  50.                     sal.rup = "<span>₹</span>";  
  51.                     return ((sal.rup) + " " + commaSep.join("."));  
  52.                 }  
  53.             }]  
  54.         });  
  55.     } catch (e) {  
  56.         alert(e.message);  
  57.     }  
  58. }  
  59.   
  60. function myErrHandler(data, errCode, errMessage) {  
  61.     alert("Error: " + errMessage);  
  62. }  
Now, move to SharePoint part. First, upload the above JavaScript and HTML file to Site Assets library of SharePoint or whatever library you want.

Create a Webpart Page (Named DataTable) and store it in the pages or the site pages library.

Add a content editor to DataTable Page. Now, give HTML file reference to the content editor into Content Editor.

Content Editor

Click OK and save the page.

Now, our final output is shown below:

output

It’s Cool!

result
Now, play with the UI, provided by the data table plugin.

Conclusion: Thus, in this way, we see how we can work with DataTables and SharePoint together.

Did I miss anything that you may think is required? Maybe this is useful for someone. I hope you like it. Please share your valuable suggestions and feedback.


Similar Articles