Post
Ask Question

Remote DataBinding In Kendo SpreadSheet

Introduction

Kendo Spreadsheet control is used to display the data in the spreadsheet view. In this article, we are going to see how to do remote binding in Kendo SpreadSheets.

WEB API Services

I'm going to use the following WEB API Services which are developed using ASP.NET CORE to construct the data source for Kendo SpreadSheet control. If you are new to ASP.NET CORE WEB API please go through my previous article which will give you a basic idea about how to create an API service using ASP.NET CORE WEB API project. 

Create a model class,
 
Product.cs
  1. public class Product  
  2. {  
  3.       public int ProductID { get; set; }  
  4.       public string ProductName { get; set; }  
  5.       public double Price { get; set; }  
  6.       public double Tax { get; set; }  
  7.   
  8.       public Product(int ProductID,string ProductName, double Price, double Tax )  
  9.       {  
  10.           this.ProductID = ProductID;  
  11.           this.ProductName = ProductName;  
  12.           this.Price = Price;  
  13.           this.Tax = Tax;  
  14.       }  
  15. }  
Create a Controller.

ProductsController.cs

  1. namespace MyCoreAPI.Controllers  
  2. {  
  3.     [Produces("application/json")]  
  4.     [Route("api/Products")]  
  5.     public class ProductsController : Controller  
  6.     {  
  7.         [HttpGet]  
  8.         [Route("ProductDetails")]  
  9.         public List<Product> GetProduct()  
  10.         {  
  11.             try  
  12.             {  
  13.                 List<Product> products = new List<Product>();  
  14.                 products.Add(new Product(1, "Tea pack", 100, 10));  
  15.                 products.Add(new Product(2, "Coffee pack", 120, 12));  
  16.                 return products;  
  17.             }  
  18.             catch (Exception ex)  
  19.             {  
  20.                 List<Product> products = null;  
  21.                 return products;  
  22.             }  
  23.         }  
  24.     }  
  25. }  
GetProduct action will return a list of products.

Testing the API in Postman 

/api/Products/ProductDetails
 
 
 
Yes, we got the product list from the above API in JSON format. Now, our API is ready, and we will construct the kendoSpreadSheet using this API.

KendoSpreadSheet.html

  1. <!DOCTYPE html>  
  2. <html>  
  3. <head>  
  4.   
  5.     <style>  
  6.         html {  
  7.             font-size: 14px;  
  8.             font-family: Arial, Helvetica, sans-serif;  
  9.         }  
  10.     </style>  
  11.     <title></title>  
  12.     <link rel="stylesheet" href="https://kendo.cdn.telerik.com/2018.3.1017/styles/kendo.common-material.min.css" />  
  13.     <link rel="stylesheet" href="https://kendo.cdn.telerik.com/2018.3.1017/styles/kendo.material.min.css" />  
  14.     <link rel="stylesheet" href="https://kendo.cdn.telerik.com/2018.3.1017/styles/kendo.material.mobile.min.css" />  
  15.   
  16.     <script src="https://kendo.cdn.telerik.com/2018.3.1017/js/jquery.min.js"></script>  
  17.     <script src="https://kendo.cdn.telerik.com/2018.3.1017/js/jszip.min.js"></script>  
  18.     <script src="https://kendo.cdn.telerik.com/2018.3.1017/js/kendo.all.min.js"></script>  
  19.   
  20.   
  21. </head>  
  22. <body>  
  23.     <div id="example">  
  24.         <div class="box wide">  
  25.             <div class="box-col">  
  26.                 <h4>Save data changes</h4>  
  27.                 <ul class="options">  
  28.                     <li>  
  29.                         <button class="k-button" id="save">Save changes</button>  
  30.                         <button class="k-button" id="cancel">Cancel changes</button>  
  31.                     </li>  
  32.                 </ul>  
  33.             </div>  
  34.         </div>  
  35.   
  36.         <div id="spreadsheet" style="width: 100%"></div>  
  37.         <script>  
  38.             $(function () {  
  39.                 var dataSource = new kendo.data.DataSource({  
  40.                     transport: {  
  41.                         read: onRead,  
  42.                     },  
  43.                     schema: {  
  44.                         model: {  
  45.                             id: "ProductID",  
  46.                             fields: {  
  47.                                 productID: { type: "number" },  
  48.                                 productName: { type: "string" },  
  49.                                 price: { type: "number" },  
  50.                                 tax: { type: "number" }  
  51.                             }  
  52.                         }  
  53.                     }  
  54.                 });  
  55.   
  56.                 $("#spreadsheet").kendoSpreadsheet({  
  57.                     columns: 20,  
  58.                     rows: 100,  
  59.                     toolbar: false,  
  60.                     sheetsbar: false,  
  61.                     sheets: [{  
  62.                         name: "Products",  
  63.                         dataSource: dataSource,  
  64.                         rows: [{  
  65.                             height: 40,  
  66.                             cells: [  
  67.                                 {  
  68.                                     bold: "true",  
  69.                                     background: "#9c27b0",  
  70.                                     textAlign: "center",  
  71.                                     color: "white",  
  72.                                     title: "ID"  
  73.                                 }, {  
  74.                                     bold: "true",  
  75.                                     background: "#9c27b0",  
  76.                                     textAlign: "center",  
  77.                                     color: "white",  
  78.                                     title:"Product Name"  
  79.                                 }, {  
  80.                                     bold: "true",  
  81.                                     background: "#9c27b0",  
  82.                                     textAlign: "center",  
  83.                                     color: "white",  
  84.                                     title:"Price"  
  85.                                 }, {  
  86.                                     bold: "true",  
  87.                                     background: "#9c27b0",  
  88.                                     textAlign: "center",  
  89.                                     color: "white"  
  90.                                 }, ]  
  91.                         }],  
  92.                         columns: [  
  93.                             { width: 100 },  
  94.                             { width: 415 },  
  95.                             { width: 145 },  
  96.                             { width: 145 },  
  97.                             { width: 145 }  
  98.                         ]  
  99.                     }]  
  100.                 });  
  101.   
  102.                 function onRead(options) {  
  103.                     $.ajax({  
  104.                         url: "http://localhost:11207/api/Products/ProductDetails",  
  105.                         dataType: "json",  
  106.                         success: function (result) {  
  107.                             options.success(result);  
  108.                         },  
  109.                         error: function (result) {  
  110.                             options.error(result);  
  111.                         }  
  112.                     });  
  113.                 }  
  114.   
  115.             
  116.         </script>  
  117.     </div>  
  118.   
  119.   
  120. </body>  
  121. </html>  
From the above code, you can notice the kendoSpreadSheet object is used to initialize the spreadsheet widget. The Sheets object is used to set the data source and other sheet information properties like rows, column name and many more.

The onRead method is used to make the AJAX call to get the product details. 

Result in the browser 
 
  
Filtering and sorting in Kendo Spreadsheet

Sorting and filtering of Kendo spreadsheets can be enabled using the filter object, as given below.

  1. filter: {  
  2.                            ref: "A1:D3",  
  3.                            columns: []  
  4.                        },  
In ref property, we need to set the range of the cell.
 
Here is the complete code.
 
KendoSpreadSheet.html 
  1. <!DOCTYPE html>  
  2. <html>  
  3. <head>  
  4.     
  5.     <style>  
  6.         html {  
  7.             font-size: 14px;  
  8.             font-family: Arial, Helvetica, sans-serif;  
  9.         }  
  10.     </style>  
  11.     <title></title>  
  12.     <link rel="stylesheet" href="https://kendo.cdn.telerik.com/2018.3.1017/styles/kendo.common-material.min.css" />  
  13.     <link rel="stylesheet" href="https://kendo.cdn.telerik.com/2018.3.1017/styles/kendo.material.min.css" />  
  14.     <link rel="stylesheet" href="https://kendo.cdn.telerik.com/2018.3.1017/styles/kendo.material.mobile.min.css" />  
  15.   
  16.     <script src="https://kendo.cdn.telerik.com/2018.3.1017/js/jquery.min.js"></script>  
  17.     <script src="https://kendo.cdn.telerik.com/2018.3.1017/js/jszip.min.js"></script>  
  18.     <script src="https://kendo.cdn.telerik.com/2018.3.1017/js/kendo.all.min.js"></script>  
  19.   
  20.   
  21. </head>  
  22. <body>  
  23.     <div id="example">  
  24.          
  25.   
  26.         <div id="spreadsheet" style="width: 100%"></div>  
  27.         <script>  
  28.             $(function () {  
  29.                 
  30.   
  31.                 var dataSource = new kendo.data.DataSource({  
  32.                     transport: {  
  33.                         read: onRead,  
  34.                          
  35.                     },  
  36.                      
  37.                      
  38.                     schema: {  
  39.                         model: {  
  40.                             id: "ProductID",  
  41.                             fields: {  
  42.                                 productID: { type: "number" },  
  43.                                 productName: { type: "string" },  
  44.                                 price: { type: "number" },                                 
  45.                                 tax: { type: "number" }  
  46.                             }  
  47.                         }  
  48.                     }  
  49.                 });  
  50.   
  51.                 $("#spreadsheet").kendoSpreadsheet({  
  52.                     columns: 20,  
  53.                     rows: 100,  
  54.                     toolbar: false,  
  55.                     sheetsbar: false,  
  56.                     sheets: [{  
  57.                         name: "Products",  
  58.                         dataSource: dataSource,  
  59.                         filter: {  
  60.                             ref: "A1:D3",  
  61.                             columns: []  
  62.                         },  
  63.                         rows: [{  
  64.                             height: 40,  
  65.                              
  66.                             cells: [  
  67.                                 {  
  68.                                     bold: "true",  
  69.                                     background: "#9c27b0",  
  70.                                     textAlign: "center",  
  71.                                     color: "white",  
  72.                                     title: "ID"  
  73.                                 }, {  
  74.                                     bold: "true",  
  75.                                     background: "#9c27b0",  
  76.                                     textAlign: "center",  
  77.                                     color: "white",  
  78.                                     title:"Product Name"  
  79.                                 }, {  
  80.                                     bold: "true",  
  81.                                     background: "#9c27b0",  
  82.                                     textAlign: "center",  
  83.                                     color: "white",  
  84.                                     title:"Price"  
  85.                                 }, {  
  86.                                     bold: "true",  
  87.                                     background: "#9c27b0",  
  88.                                     textAlign: "center",  
  89.                                     color: "white"  
  90.                                 }, ]  
  91.                         }],  
  92.                         columns: [  
  93.                             { width: 100 },  
  94.                             { width: 415 },  
  95.                             { width: 145 },  
  96.                             { width: 145 },  
  97.                             { width: 145 }  
  98.                         ]  
  99.                     }]  
  100.                 });  
  101.   
  102.               
  103.   
  104.                 function onRead(options) {  
  105.                     $.ajax({  
  106.                         url: "http://localhost:11207/api/Products/ProductDetails",  
  107.                         dataType: "json",  
  108.                         success: function (result) {  
  109.                             options.success(result);  
  110.                         },  
  111.                         error: function (result) {  
  112.                             options.error(result);  
  113.                         }  
  114.                     });  
  115.                 }  
  116.   
  117.                
  118.             });  
  119.         </script>  
  120.     </div>  
  121.   
  122.   
  123. </body>  
  124. </html>  
Kendo spreadhsheet with filter
 
 
Reference
https://demos.telerik.com/kendo-ui/spreadsheet/index 

Conclusion

We saw how to implement a remote data binding in the kendo spreadsheet, which is basic functionality. Also, we saw the process of enabling the client-side sorting and filtering in the spreadsheet. We will see more advanced functionality in Kendo spreadsheets in my future articles. 

I hope you have enjoyed this article. Your valuable feedback, questions, or comments about this article are always welcomed.


Similar Articles