Post
Ask Question

Export Kendo Grid Data to MS Excel and CSV using WEB API

Before going through this article ensure that you have basic knowledge of MVC Architecture and ASP.NET Web API.

Open Visual Studio 2012 and create a new project as in the following:
  • Select "File" -> "New" -> "Project...".
  • Select Web in the installed template, then select ASP.NET Web Application.
  • Provide the name for the project and click OK as in Figures 1 and 2.
 
Figure 1
 
 
Figure 2

My database schema is as in Figure 3.


Figure 3

My Microsoft SQL table structure is as in Figure 4.

 
Figure 4

I am using Entity Framework with the Database First approach, so the Entity Framework builds the default model classes and context classes.

Now it’s time to create a Web API controller.

Right-click on the Controller folder then select Add, Controller and create a WEB API class as in Figure 5 and 6.

  
Figure 5
 
  
Figure 6

Check the API services using the POSTMAN/Fiddler as in Figure 7.
Figure 7
 
Exporting the Kendo Grid data to Excel

Now it's time for creating a design to consume the service.

Create an HTML page, here is the design:
  1. <!DOCTYPE html>  
  2. <html xmlns="http://www.w3.org/1999/xhtml">  
  3. <head>  
  4.     <title></title>  
  5.   
  6.     <link rel="stylesheet" href="http://kendo.cdn.telerik.com/2015.2.805/styles/kendo.common.min.css">  
  7.     <link rel="stylesheet" href="http://kendo.cdn.telerik.com/2015.2.805/styles/kendo.rtl.min.css">  
  8.     <link rel="stylesheet" href="http://kendo.cdn.telerik.com/2015.2.805/styles/kendo.default.min.css">  
  9.     <link rel="stylesheet" href="http://kendo.cdn.telerik.com/2015.2.805/styles/kendo.mobile.all.min.css">  
  10.   
  11.     <script src="http://code.jquery.com/jquery-1.9.1.min.js"></script>  
  12.     <script src="http://kendo.cdn.telerik.com/2015.2.805/js/angular.min.js"></script>  
  13.     <script src="http://kendo.cdn.telerik.com/2015.2.805/js/jszip.min.js"></script>  
  14.     <script src="http://kendo.cdn.telerik.com/2015.2.805/js/kendo.all.min.js"></script>  
  15. </head>  
  16. <body>  
  17.     <div id="example">  
  18.         <div class="demo-section k-header wide">  
  19.             <div>  
  20.                 <h4>Export to Excel</h4>  
  21.                 <div id="test-grid" data-role="grid"  
  22.                      date-scrollable="true"  
  23.                      data-editable="true"  
  24.                      data-toolbar="['excel']"  
  25.                      data-columns="[  
  26.                                  { 'field': 'Name', 'width': 270 },  
  27.                                  { 'field': 'Category' },  
  28.                               ]"  
  29.                      data-bind="source: products,  
  30.                             visible: isVisible"  
  31.                      style="height: 300px"></div>  
  32.             </div>  
  33.         </div>  
  34. </div>  
  35.   
  36. </body>  
  37. </html>  
Note: The Excel generation feature needs the JSZip JavaScript library.

JavaScript in MVVM Model
  1. var viewModel = kendo.observable({  
  2.         isVisible: true,  
  3.         products: new kendo.data.DataSource({  
  4.             schema: {  
  5.                 model: {  
  6.                     id: "id",  
  7.                     fields: {  
  8.                         Name: { type: "string" },  
  9.                         Category: { type: "string" }  
  10.                     }  
  11.                 }  
  12.             },  
  13.             batch: true,  
  14.             transport: {  
  15.                 read: {  
  16.                     url: "/api/Products",  
  17.                     dataType: "json"  
  18.                 },  
  19.                 
  20.                 parameterMap: function(options, operation) {  
  21.                     if (operation !== "read" && options.models) {  
  22.                         return {models: kendo.stringify(options.models)};  
  23.                     }  
  24.                 }  
  25.             }  
  26.         }),  
  27.   
  28. });  
  29.     kendo.bind($("#example"), viewModel);  
The following is the result in the browser:
 
 
Figure 8 
 
Click the Export to Excel button to export the Grid data to Microsoft Excel as in Figure 9.
 
 Figure 9
 
The Grid data in the Excel sheet is as in Figure 10.
 
  
Figure 10

Exporting the Kendo Grid data to CSV

To Export a Grid data to the CSV format I will use blog.js and fileSave.js script libraries along with Kendo JavaScript libraries.

HTML Design
  1. <!DOCTYPE html>  
  2. <html xmlns="http://www.w3.org/1999/xhtml">  
  3. <head>  
  4.     <title></title>  
  5.   
  6.     <link rel="stylesheet" href="http://kendo.cdn.telerik.com/2015.2.805/styles/kendo.common.min.css">  
  7.     <link rel="stylesheet" href="http://kendo.cdn.telerik.com/2015.2.805/styles/kendo.rtl.min.css">  
  8.     <link rel="stylesheet" href="http://kendo.cdn.telerik.com/2015.2.805/styles/kendo.default.min.css">  
  9.     <link rel="stylesheet" href="http://kendo.cdn.telerik.com/2015.2.805/styles/kendo.mobile.all.min.css">  
  10.   
  11.     <script src="http://code.jquery.com/jquery-1.9.1.min.js"></script>  
  12.     <script src="http://kendo.cdn.telerik.com/2015.2.805/js/angular.min.js"></script>  
  13.     <script src="http://kendo.cdn.telerik.com/2015.2.805/js/jszip.min.js"></script>  
  14.     <script src="http://kendo.cdn.telerik.com/2015.2.805/js/kendo.all.min.js"></script>  
  15.     <script src="Scripts/Blob.js"></script>  
  16.     <script src="Scripts/FileSaver.js"></script>  
  17. </head>  
  18. <body>  
  19.     <div id="example">  
  20.         <div class="demo-section k-header wide">  
  21.             <div>  
  22.                 <h4>Export to CSV</h4>  
  23.                 <div id="test-grid" data-role="grid"  
  24.                      date-scrollable="true"  
  25.                      data-editable="true"  
  26.                      data-columns="[  
  27.                                  { 'field': 'Name', 'width': 270 },  
  28.                                  { 'field': 'Category' },  
  29.                               ]"  
  30.                      data-bind="source: products,  
  31.                             visible: isVisible"  
  32.                      style="height: 300px"></div>  
  33.             </div>  
  34.         </div>  
  35.         <div >  
  36.             <button data-role="button" data-bind="visible: isVisible, enabled: isEnabled, events: { click: onClick }">Export to CSV</button>  
  37.         </div>  
  38.      </div>  
  39.   
  40. </body>  
  41. </html>  

JavaScript in MVVM Model

  1. var viewModel = kendo.observable({  
  2.         isVisible: true,  
  3.         onClick:function(e)  
  4.         {  
  5.             e.preventDefault();  
  6.             viewModel.exportCsv('test-grid', 'testdata.csv');  
  7.   
  8.         },  
  9.         products: new kendo.data.DataSource({  
  10.             schema: {  
  11.                 model: {  
  12.                     id: "id",  
  13.                     fields: {  
  14.                         Name: { type: "string" },  
  15.                         Category: { type: "string" }  
  16.                     }  
  17.                 }  
  18.             },  
  19.             batch: true,  
  20.             transport: {  
  21.                 read: {  
  22.                     url: "/api/Products",  
  23.                     dataType: "json"  
  24.                 },  
  25.                 
  26.                 parameterMap: function(options, operation) {  
  27.                     if (operation !== "read" && options.models) {  
  28.                         return {models: kendo.stringify(options.models)};  
  29.                     }  
  30.                 }  
  31.             }  
  32.         }),  
  33.   
  34.   
  35.         exportCsv: function (gridId, fileName) {  
  36.             var grid = $("#" + gridId).data("kendoGrid");  
  37.             var originalPageSize = grid.dataSource.pageSize();  
  38.             var csv = '';  
  39.             fileName = fileName || 'download.csv';  
  40.  
  41.             grid.dataSource.pageSize(grid.dataSource.view().length);  
  42.             var data = grid.dataSource.view();  
  43.             for (var i = 0; i < grid.columns.length; i++) {  
  44.                 var field = grid.columns[i].field;  
  45.                 var title = grid.columns[i].title || field;  
  46.                 if (!field) continue;  
  47.   
  48.                 title = title.replace(/"/g, '""');  
  49.                 csv += '"' + title + '"';  
  50.                 if (i < grid.columns.length - 1) {  
  51.                     csv += ',';  
  52.                 }  
  53.             }  
  54.             csv += '\n';  
  55.  
  56.             for (var row in data) {  
  57.                 for (var i = 0; i < grid.columns.length; i++) {  
  58.                     var fieldName = grid.columns[i].field;  
  59.                     var template = grid.columns[i].template;  
  60.                     var exportFormat = grid.columns[i].exportFormat;  
  61.   
  62.  
  63.                     if (!fieldName) continue;  
  64.                     var value = '';  
  65.                     if (fieldName.indexOf('.') >= 0)  
  66.                     {  
  67.                         var properties = fieldName.split('.');  
  68.                         var value = data[row] || '';  
  69.                         for (var j = 0; j < properties.length; j++) {  
  70.                             var prop = properties[j];  
  71.                             value = value[prop] || '';  
  72.                         }  
  73.                     }  
  74.                     else{  
  75.                               
  76.                         value = data[row][fieldName] || '';  
  77.                     }  
  78.                     if (value && template && exportFormat !== false) {  
  79.                         value = _.isFunction(template)  
  80.                             ? template(data[row])  
  81.                             : kendo.template(template)(data[row]);  
  82.                     }  
  83.   
  84.                     value = value.toString().replace(/"/g, '""');  
  85.                     csv += '"' + value + '"';  
  86.                     if (i < grid.columns.length - 1) {  
  87.                         csv += ',';  
  88.                     }  
  89.                 }  
  90.                 csv += '\n';  
  91.             }  
  92.             grid.dataSource.pageSize(originalPageSize);  
  93.   
  94.             //EXPORT TO BROWSER  
  95.             var blob = new Blob([csv], { type: 'text/csv;charset=utf-8' }); //Blob.js  
  96.             saveAs(blob, fileName); //FileSaver.js  
  97.         },  
  98.     });  
  99.     kendo.bind($("#example"), viewModel);  
The result in a browser is as shown in Figure 11.
 
 
Figure 11

Click the Export to CSV button to export the Grid data to CSV as in Figure 12.
 
  
 
Figure 12

The Grid data in the CSV format is as in Figure 13.
 
  
Figure 13

That's it, I hope you have enjoyed this article.
 
Thank you, Happy Coding.


Similar Articles