Ignite UI Data Grid In SharePoint Online Using AJAX - Part One

How to use UI Data Grid in SharePoint Online using AJAX.

In this article, I have explained how to use Infragistics UI Data Grid (igGrid) in SharePoint online.

Introduction

In most cases, we use jQuery data table for data binding because it’s free of cost with awesome features. Nowadays, several data grids are available in the market for free or at a minimal cost with most enhanced features to play against the data, like Sorting, Paging, Filtering, and exporting to files like PDF, Excel. However, the most important thing is the performance and I am choosing this Infragistics UI data grid because I love its performance of binding the data in a fraction of second when comparing to the jQuery data table.

What Is igGrid?

igGrid is a jQuery grid that binds the data and has the following features.

  • Sorting
  • Filtering
  • Paging
  • Grouping
  • Column Hiding
  • Resizing
  • Row and Cell selection
  • Summaries
  • Tooltips
  • Data Editing

Some of the dependencies are required for using the igGrid.

  • jquery-1.9.1.js
  • ui.core.js
  • ui.widget.js
  • datasource.js
  • ui.widget.js
  • ui.shared.js
  • templating.js
  • util.js

Now, open the SharePoint online data to create a data source for pulling the data from the SharePoint custom list.

For example, we are going to create a Product list to hold the product information, like ProductId, ProductName, Category, Stock etc. I have pushed some dummy data up to 5000 items in SharePoint list.

Ignite UI Data grid

Open SharePoint Designer -> Site Assets and create Igid.html file into it. Open the HTML file in a text editor to add the following references in the <head></head> Section.

  1. <!-- Ignite UI Required Combined CSS Files -->   
  2. <link href="https://cdn-na.infragistics.com/igniteui/2017.2/latest/css/themes/infragistics/infragistics.theme.css" rel="stylesheet" />   
  3. <link href="https://cdn-na.infragistics.com/igniteui/2017.2/latest/css/structure/infragistics.css" rel="stylesheet" />   
  4. <script src="https://ajax.aspnetcdn.com/ajax/modernizr/modernizr-2.8.3.js"></script>   
  5. <script src="https://code.jquery.com/jquery-1.11.3.min.js"></script>   
  6. <script src="https://code.jquery.com/ui/1.11.1/jquery-ui.min.js"></script>   
  7. <!-- Ignite UI Required Combined JavaScript Files -->  
  8.  <script src="https://cdn-na.infragistics.com/igniteui/2017.2/latest/js/infragistics.core.js"></script>  
  9.  <script src="https://cdn-na.infragistics.com/igniteui/2017.2/latest/js/infragistics.lob.js"></script>  

Create an AJAX request to fetch the data from SharePoint list. Add the HTML into the <body></body> tag.

  1. <table id="grid"></table>  

Declare the necessary variables.

  1. <script>  
  2.     $(function() {  
  3.         var datas;  
  4.         var results = [];  
  5.         $.ajax({  
  6.             url: _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getbytitle('accessoriesdata')/items?$top=5000",  
  7.             method: "GET",  
  8.             headers: {  
  9.                 "Accept""application/json; odata=verbose"  
  10.             },  
  11.             success: function(data) {  
  12.                 datas = data.d.results;  
  13.                 console.log(datas);  
  14.             },  
  15.   
  16.   
  17.             error: function(error) {  
  18.                 console.log(JSON.stringify(error));  
  19.             }  
  20.         });  
  21.     });  
  22. </script>  

In the browser console, you are now able to see how many records we retrieved in that AJAX Call.

Ignite UI Data grid

I have just formatted a data source like this.

  1. var result = [  
  2.                 { "ProductID": 1, "ProductName""Adjustable Race""Category""AR-5381",  “Stock”:”” }  
  3.           
  4.                 ]  

Code

  1. results.push({ "ProductID": datas[i].ID,   
  2.                              "ProductName": datas[i].ProductName,  
  3.                              "Category":datas[i].Category,  
  4.                              "Stock":datas[i].Stock  
  5.                       
  6.                           });  
  7.               console.log(results);  

Result

Ignite UI Data grid

Now, in the next step, go and initialize the igGrid function of the table.

  1. function initigGrid(value){  
  2.     //Initialize the grid  
  3.      $("#grid").igGrid({  
  4.                 autoGenerateColumns: false,  
  5. //Define the columns and it’s datatypes  
  6.                 columns: [  
  7.                     { headerText: "Product ID", key: "ProductID", dataType: "number" },  
  8.                     { headerText: "Product Name", key: "ProductName", dataType: "string" },  
  9.                     { headerText: "Category", key: "Category", dataType: "string" },  
  10.                     { headerText: "Stock", key: "Stock", dataType: "number" }  
  11.                 ],  
  12. //Pick the datasource to display  
  13.             dataSource: value      
  14.                   
  15.                 });  
  16.       
  17.     }  

The overall script looks like below.

  1. <script>  
  2.     $(function() {  
  3.         var datas;  
  4.         var results = [];  
  5.   
  6.         $.ajax({  
  7.             url: _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getbytitle('accessoriesdata')/items?$top=5000",  
  8.             method: "GET",  
  9.             headers: {  
  10.                 "Accept""application/json; odata=verbose"  
  11.             },  
  12.             success: function(data) {  
  13.   
  14.                 datas = data.d.results;  
  15.                 console.log(datas);  
  16.            // Iterate through the data  
  17.                   for(i=0; i < datas.length; i++)  
  18.               {  
  19.               results.push({ "ProductID": datas[i].ProductID,   
  20.                              "ProductName": datas[i].Title,  
  21.                              "Category":datas[i].Category,  
  22.                              "Stock":datas[i].Stock  
  23.                       
  24.                           });  
  25.                 
  26.                     }    
  27.                     console.log(results);  
  28.                     initigGrid(results); //Call the function after successful results  
  29.             },  
  30.   
  31.   
  32.             error: function(error) {  
  33.                 console.log(JSON.stringify(error));  
  34.             }  
  35.         });  
  36.     });  
  37.     function initigGrid(value){  
  38.       
  39.      $("#grid").igGrid({  
  40.                 autoGenerateColumns: false,  
  41.                 columns: [  
  42.                     { headerText: "Product ID", key: "ProductID", dataType: "number" },  
  43.                     { headerText: "Product Name", key: "ProductName", dataType: "string" },  
  44.                     { headerText: "Category", key: "Category", dataType: "string" },  
  45.                     { headerText: "Stock", key: "Stock", dataType: "number" }  
  46.                 ],  
  47.             dataSource: value      
  48.                   
  49.                 });  
  50.       
  51.     }  
  52.       
  53. </script>  

Result

Ignite UI Data grid

Ignite UI Data grid

It’s showing 5000 items from the SharePoint list without paging. Now, I am going to show how to hide the column “ProductID”.

Just set the hidden attribute to true inside the column object. You can also format the width and height of the column.

  1. columns: [  
  2.                     { headerText: "Product ID", key: "ProductID", dataType: "number" , hidden: true }  
  3. ]  

Ignite UI Data grid

Paging

Paging allows you to show the number of items per page. You can navigate to next and previous to next items.

Code

Features

  1. {  
  2.     name: "Paging",     // It defines the paging feature  
  3.     pageSize: 10        // Mention the page size it view 10 per page now  
  4. }  

Ignite UI Data grid
Sorting

This feature allows you to sort the data in the columns. It also supports single and multi-column sorting.

  1. features: [  
  2.              
  3. {  
  4.                         name: "Sorting",     // Define sorting feature  
  5.                          type: "local"       // Type of sorting example local, remote   
  6.                                                    
  7.              }   
  8. ]  

Ignite UI Data grid
Filter

It helps to filter the data based on the conditions; for example - Equals, Not equals, contains etc.

Code 

  1. {  
  2.                         name: "Filtering",                              // Define filter feature  
  3.                         type: "local",                                     // Type of filter  
  4.                         columnSettings: [  
  5.                             {   
  6. // If you do not want to filter some column, just set allowFiltering: false
  7. // By default, other columns are set to true.  
  8.   
  9.                                 columnKey: "ProductName",                         
  10.                                 allowFiltering: false  
  11.                             },  
  12.                             {  
  13.                                 columnKey: "Stock",  
  14.                                 condition: "greaterThan"                     // Also set the default condition  
  15.                             }  
  16.                         ]  
  17.                     }  

Ignite UI Data grid

Ignite UI Data grid

GroupBy

This feature helps in grouping the products. You can easily understand the value of the particular set of products.

Code

  1. name: "GroupBy",  
  2. /By default set the groupby value of particular column  
  3. columnSettings: [  
  4.     {  
  5.         columnKey: "ProductName",  
  6.         isGroupBy: true            
  7.     }  
  8. ]  

Ignite UI Data grid

Just drag and drop the columns to the Groupby section for grouping the data.

Ignite UI Data grid

Finally, the code looks like below

Code

  1. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">  
  2. <html xmlns="http://www.w3.org/1999/xhtml">  
  3.   
  4. <head>  
  5. <script src="https://ajax.aspnetcdn.com/ajax/modernizr/modernizr-2.8.3.js"></script>  
  6. <script src="https://code.jquery.com/jquery-1.11.3.min.js"></script>  
  7. <script src="https://code.jquery.com/ui/1.11.1/jquery-ui.min.js"></script>  
  8. <!-- Ignite UI Required Combined JavaScript Files -->  
  9. <script src="https://cdn-na.infragistics.com/igniteui/2017.2/latest/js/infragistics.core.js"></script>  
  10. <script src="https://cdn-na.infragistics.com/igniteui/2017.2/latest/js/infragistics.lob.js"></script>  
  11.   
  12. <!-- Ignite UI Required Combined CSS Files -->  
  13. <link href="https://cdn-na.infragistics.com/igniteui/2017.2/latest/css/themes/infragistics/infragistics.theme.css" rel="stylesheet" />  
  14. <link href="https://cdn-na.infragistics.com/igniteui/2017.2/latest/css/structure/infragistics.css" rel="stylesheet" />  
  15.   
  16.   
  17. </head>  
  18.   
  19. <body>  
  20. <table id="grid"></table>  
  21.   
  22. <script>  
  23.         $(function() {  
  24.             var datas;  
  25.             var results = [];  
  26.   
  27.             $.ajax({  
  28.                 url: _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getbytitle('accessoriesdata')/items?$top=5000",  
  29.                 method: "GET",  
  30.                 headers: {  
  31.                     "Accept""application/json; odata=verbose"  
  32.                 },  
  33.                 success: function(data) {  
  34.   
  35.                     datas = data.d.results;  
  36.                     console.log(datas);  
  37.                     for (i = 0; i < datas.length; i++) {  
  38.                         results.push({  
  39.                             "ProductID": datas[i].ProductID,  
  40.                             "ProductName": datas[i].Title,  
  41.                             "Category": datas[i].Category,  
  42.                             "Stock": datas[i].Stock  
  43.   
  44.                         });  
  45.   
  46.                     }  
  47.                     console.log(results);  
  48.                     initigGrid(results)  
  49.                 },  
  50.   
  51.   
  52.                 error: function(error) {  
  53.                     console.log(JSON.stringify(error));  
  54.                 }  
  55.             });  
  56.         });  
  57.   
  58.         function initigGrid(value) {  
  59.   
  60.             $("#grid").igGrid({  
  61.                 autoGenerateColumns: false,  
  62.                 primaryKey: "ProductID",  
  63.                 caption : "<span> <img src='https://sharepointtechie.sharepoint.com/sites/auto/SiteAssets/sp.png' alt='sharepoint' height='50px'></span>",  
  64.                 width: '100%',  
  65.   
  66.                 columns: [{  
  67.                         headerText: "Product ID",  
  68.                         key: "ProductID",  
  69.                         dataType: "number",  
  70.                         hidden: true  
  71.                     },  
  72.                     {  
  73.                         headerText: "Product Name",  
  74.                         key: "ProductName",  
  75.                         dataType: "string"  
  76.                     },  
  77.                     {  
  78.                         headerText: "Category",  
  79.                         key: "Category",  
  80.                         dataType: "string"  
  81.                     },  
  82.                     {  
  83.                         headerText: "Stock",  
  84.                         key: "Stock",  
  85.                         dataType: "number"  
  86.                     }  
  87.                 ],  
  88.                 dataSource: value,  
  89.                 features: [{  
  90.                         name: "Paging",  
  91.                         pageSize: 10  
  92.                     },  
  93.                     {  
  94.                         name: "Sorting",  
  95.                         type: "local"  
  96.   
  97.                     },  
  98.   
  99.                     {  
  100.                         name: "Filtering",  
  101.                         type: "local",  
  102.                         columnSettings: [{  
  103.                                 columnKey: "ProductName",  
  104.                                 allowFiltering: false  
  105.                             },  
  106.                             {  
  107.                                 columnKey: "Stock",  
  108.                                 condition: "greaterThan"  
  109.                             }  
  110.                         ]  
  111.                     },  
  112.                     {  
  113.                         name: "Resizing"  
  114.                     },  
  115.   
  116.                     {  
  117.                         name: "GroupBy",  
  118.                         columnSettings: [{  
  119.                             columnKey: "ProductName",  
  120.                             isGroupBy: true  
  121.                         }]  
  122.                     },  
  123.   
  124.   
  125.                 ]  
  126.   
  127.             });  
  128.   
  129.         }  
  130.     </script>  
  131.     </body>  
  132.   
  133. </html>  

Ignite UI Data grid

Get connected to learn more features of the Infragistics data grid in my upcoming articles. 

Happy SharePointing!!!