Display SharePoint List Data With jQuery DataTable In SharePoint Framework(SPFx) WebPart

Siddharth Vaghasia

jQuery SPFx SharePoint FrameworkDisplay SharePoint List DatajQuery DataTable In SharePoint FrameworkSPFx WebPart

12 Comments

Join the conversation! Your thoughts help the community grow.

Mamta1y ago
CSS not Working on Pagination
Avinash Yerra4y ago
Hi Siddharth, I am able to compile and deploy the code on SharePoint online. However, no data is shown on the page when I add the web part. There are no errors. I have created the list same as the one you have created
- Siddharth Vaghasia4y ago
  Hey Avinash....please check console window, you will see some errors
David Jones5y ago
Siddharth Vaghasia this is a great reference, thanks. Can you provide any guidance how to incorporate a column search bar for each field? We have one search bar for the full list but I know Datatables.net reference column specific search filters however I can't get them to work in this solution, any ideas? :) Thanks!
Abenet Beyene6y ago
Have you did date column from list and have standard format ? I am having issue converting from utc to standard.
Divesh Gupta6y ago
Hey! I am able to compile and deploy the code on SharePoint online. However, no data is shown on the page when i add the webpart. There are no errors. Please suggest
- Siddharth Vaghasia6y ago
  Hey Divesh.... Does your sharepoint site has same list as mine and are column names exactly same as in this sample? do you see any errors in console ?
Abenet Beyene6y ago
Hi Siddharth, have you used child row? https://datatables.net/examples/api/row_details.html
- Abenet Beyene6y ago
  I have 7 columns that I am showing, I was trying to show 5 columns and add the two columns as child, with collapse and expand.
- Siddharth Vaghasia6y ago
  Abenet Beyene...yes..are you facing any issue?
Siddharth Vaghasia6y ago
Yes....it is possible... use jquery datatable render function with column definition...https://datatables.net/reference/option/columns.render... on this page if you go down you will find "Use as a function to create a link from the data source:"
Abenet Beyene6y ago
Is it possible to have an item to have hyperlinks. For instance all rows under title to be hyperlinks?

One of the typical SharePoint customizations is to display SharePoint List data in jQuery Datable. We used to develop this using the content editor web part or custom page referencing custom JS file. With a new development-recommended approach to create client-side web parts using SharePoint framework, let us see how we can achieve the same using SharePoint Framework web part.

If you are not familiar or aware of SPFx web part and jQuery data table, I would request you to go through below links first.

Sample List

Create a list as per our requirement. For this article, we would be using the below list.

Create an SPFx web part, use the below options.

jQuery Packages

Add the jQuery packages. Run the below 2 commands one by one in NodeJS command prompt at the project root directory.

npm install @types/jquery@2 --save
npm install @types/jqueryui --save

This is to load Jquery package in our SPFx solution and to use jquery syntax in TypeScript file as we do in JS file.

DataTable Packages

As the purpose of the article is to use jQuery Datatable to render list data, we would need to install the data table npm packages in our solution. As we are in luck, we do have npm packages available for datatable.net library which can be used in any node.js based project/solution. Here, we are using jQuery UI front-end framework, we can also use other front-end frameworks like bootstrap, semantic. Read more about this in details at this link.

npm install datatables.net
npm install datatables.net-jqui
npm install --save @types/datatables.net

Note
Last command is to install datatable type which can be used in TypeScript, without this there is an indirect way to use data tables methods in TypeScript (which is a story not for today)

Now, we have our required packages installed, let us move to actual code.

Open SPFx solution in your favourite code editor and go to '\js-jquery-datatable\src\webparts\jqueryDataTableWp\JqueryDataTableWpWebPart.ts' .

Import Packages

Add the below lines on top of your webpart.ts file to import packages which we installed.

import { SPComponentLoader } from '@microsoft/sp-loader';
import * as $ from 'jquery';
import 'DataTables.net';
import { SPHttpClient, SPHttpClientResponse, ISPHttpClientOptions} from '@microsoft/sp-http';

Note
The first and the last import statement do not need any packages to be installed because the packages are already available as a part of SharePoint package while we created SPFx solution using the yo @microsoft/SharePoint command. SPComponentLoader will be used for loading external JS/CSS file. SPHttpClient package has methods which we can use to interact with SharePoint using REST API.

Render Method

Replace render method with below. Please read comments in code carefully to understand the logic behind getting data and initializing DataTable with the JavaScript object (returned from REST API call).

Rest API used to get the list data.

/_api/web/Lists/GetByTitle('CustomList')/items?$select=ID,Title,Email,Phone,Status,yes_x0020_no

public render(): void {
// Just adding a plain table which will be converted to jquery datatable.
this.domElement.innerHTML = `
<table id="example" class="display" width="100%"></table>`;
//Loading Jquery Datatable CSS file to get required look and feel, somehow It thought installing
// datatable package will load css also but it did not worked so I had explicitly call it here.
SPComponentLoader.loadCss("https://cdn.datatables.net/1.10.19/css/jquery.dataTables.min.css");
// getting context of current web
let currentWebUrl = this.context.pageContext.web.absoluteUrl;
// Rest API endpoint to get list data, please note here we are only getting specific columns which are required.
let requestUrl = currentWebUrl.concat("/_api/web/Lists/GetByTitle('CustomList')/items?$select=ID,Title,Email,Phone,Status,yes_x0020_no")
// calling the get method on above REST endpoint
this.context.spHttpClient.get(requestUrl, SPHttpClient.configurations.v1)
.then((response: SPHttpClientResponse) => {
if (response.ok) {
response.json().then((responseJSON) => {
if (responseJSON!=null && responseJSON.value!=null){
// Converting data to jsonArray
var jsonArray = responseJSON.value.map(function (item) {
return [
item.ID,
item.Title,
item.Email,
item.Phone,
item.Status,
item.yes_x0020_no ? "Yes" : "No"
];
});
// Intializing Datatable by passing jsonArray, specifying columns names here, please note that this should be
// in sequence of above jsonArray attributes values, it would be mapped one to one.
$('#example').DataTable( {
data: jsonArray,
columns: [
{ title: "ID" },
{ title: "Title" },
{ title: "Email" },
{ title: "Phone" },
{ title: "Status" },
{ title: "Yes/No" }
]
} );
}
});
}
});
}

Test the webpart

Run the 'gulp serve' command on Node JS command prompt at project/solution root directory. It will open local workbench, you can ignore this -- data would not be loaded here as it needs SharePoint context and we have not used any mock data in our code.

Open your workbench on SharePoint online site collection at the below URL.

https://dcmovies.sharepoint.com/sites/justiceleauge/_layouts/15/workbench.aspx

Output

Nice and clean, you can always extend by using all the available DataTable configuration options available.

Conclusion

In this article, we have learned the below:

Getting Data from SharePoint using REST API in SPFx web part
Mapping SharePoint REST API response data to Javascript array.
Displaying REST API data with Jquery data table in SPFx web part.

Hope this post was helpful. Happy coding!!!