ASP.NET Core

Export Data to Excel from Web API in ASP.NET Core using Kendo Grid

So far, we have learned how to display data in Grid from Web API in ASP.NET Core Web Applications using Kendo UI Grid. If you are new to Web API in ASP.NET and you don't know how to work with Kendo UI Grid in ASP.NET Core. Please check these articles.
Now, we will learn more about Kendo Grid features for exporting data in Excel and PDF format.

Kendo UI Grid supports exporting to Excel or PDF document. In this article, we will learn how to use the Excel Export feature of the Kendo UI Grid.

Let's assume that we have already created a Web API in ASP.NET Core and the URL of the API is /api/StudentAPI/GetAllStudents

Code Snippet of API 
  1. // GET: api/StudentAPI/GetAllStudents  
  2.         //[HttpPost]  
  3.         [Route("api/StudentAPI/GetAllStudents")]  
  4.         public IEnumerable<PersonalDetail> GetAllStudents()  
  5.         {  
  6.             List<PersonalDetail> students = new List<PersonalDetail>  
  7.             {  
  8.             new PersonalDetail{  
  9.                                RegNo = "2017-0001",  
  10.                                Name = "Nishan",  
  11.                                Address = "Kathmandu",  
  12.                                PhoneNo = "9849845061",  
  13.                                AdmissionDate = DateTime.Now  
  14.                                },  
  15.             new PersonalDetail{  
  16.                                RegNo = "2017-0002",  
  17.                                Name = "Namrata Rai",  
  18.                                Address = "Bhaktapur",  
  19.                                PhoneNo = "9849845062",  
  20.                                AdmissionDate = DateTime.Now  
  21.                               },  
  22.              new PersonalDetail{  
  23.                                RegNo = "2017-0003",  
  24.                                Name = "Junge Rai",  
  25.                                Address = "Pokhara",  
  26.                                PhoneNo = "9849845063",  
  27.                                AdmissionDate = DateTime.Now  
  28.                               },  
  29.               new PersonalDetail{  
  30.                                RegNo = "2017-0004",  
  31.                                Name = "Sunita Ghimire",  
  32.                                Address = "Kathmandu",  
  33.                                PhoneNo = "9849845064",  
  34.                                AdmissionDate = DateTime.Now  
  35.                               },  
  36.                new PersonalDetail{  
  37.                                RegNo = "2017-0005",  
  38.                                Name = "John ",  
  39.                                Address = "Bhaktapur",  
  40.                                PhoneNo = "9849845065",  
  41.                                AdmissionDate = DateTime.Now  
  42.                               },  
  43.                new PersonalDetail{  
  44.                                RegNo = "2017-0006",  
  45.                                Name = "Jenny Moktan ",  
  46.                                Address = "Kathmandu",  
  47.                                PhoneNo = "9849845066",  
  48.                                AdmissionDate = DateTime.Now  
  49.                               },  
  50.                new PersonalDetail{  
  51.                                RegNo = "2017-0007",  
  52.                                Name = "Kalpana Ghimire ",  
  53.                                Address = "Pokhara",  
  54.                                PhoneNo = "9849845067",  
  55.                                AdmissionDate = DateTime.Now  
  56.                               },  
  57.                new PersonalDetail{  
  58.                                RegNo = "2017-0008",  
  59.                                Name = "Krishna Manadal",  
  60.                                Address = "Kathmandu",  
  61.                                PhoneNo = "9849845067",  
  62.                                AdmissionDate = DateTime.Now  
  63.                               },  
  64.                new PersonalDetail{  
  65.                                RegNo = "2017-0009",  
  66.                                Name = "Karishman Luitel",  
  67.                                Address = "Bhaktapur",  
  68.                                PhoneNo = "9849845067",  
  69.                                AdmissionDate = DateTime.Now  
  70.                               },  
  71.                new PersonalDetail{  
  72.                                RegNo = "2017-0010",  
  73.                                Name = "Hari Simkhada",  
  74.                                Address = "Bhaktapur",  
  75.                                PhoneNo = "9849845067",  
  76.                                AdmissionDate = DateTime.Now  
  77.                               },  
  78.             };  
  79.             return students;  
  80.         }   
The API when called will return the data shown below.



Now, we will perform the following tasks.
  • Call the above API(/api/StudentAPI/GetAllStudents ) using Kendo Grid
  • Enable Export to Excel using Kendo Grid.
  • Make the columns Filterable.
  • Make Columns Groupable.
  • Make Columns Reorderable.
  • Make columns Sortable.
  • Enable Columns Show/Hide
  • Add additional functions to Column (Search and Multi Check Filter)
Let's add the following lines of code to parse data receied from Web API to Kendo Grid. 
  1. <link rel="stylesheet" href="http://kendo.cdn.telerik.com/2017.1.118/styles/kendo.common.min.css" />  
  2. <link rel="stylesheet" href="http://kendo.cdn.telerik.com/2017.1.118/styles/kendo.rtl.min.css" />  
  3. <link rel="stylesheet" href="http://kendo.cdn.telerik.com/2017.1.118/styles/kendo.silver.min.css" />  
  4. <link rel="stylesheet" href="http://kendo.cdn.telerik.com/2017.1.118/styles/kendo.mobile.all.min.css" />  
  5. <script src="http://code.jquery.com/jquery-1.12.4.min.js"></script>  
  6. <script src="https://cdnjs.cloudflare.com/ajax/libs/jszip/2.4.0/jszip.min.js"></script>  
  7.   
  8. <script src="http://kendo.cdn.telerik.com/2017.1.118/js/kendo.all.min.js"></script>  
  9.   
  10. <div class="panel panel-primary">  
  11.     <div class="panel-heading">Test Data from API</div>  
  12.     <div class="panel-body">  
  13.         <div id="Grid"></div> <!--end of grid-->  
  14.     </div> <!--end of panel-body-->  
  15. </div> <!--end of panel-primary-->   
Now, let's add Script section.
  • to receive data from ASP.NET Core Web API
  • to parse the data into Kendo Grid.
  • Add Export to Excel Buton on Kendo Grid.
  1. <script>  
  2.     $(document).ready(function () {  
  3.         $("#Grid").kendoGrid({  
  4.             toolbar: ["excel"],  
  5.             excel: {  
  6.                 fileName: "Demo Excel From Kendo.xlsx",  
  7.                 filterable: true,  //Allow Filtering  
  8.                 allPages: true   
  9.             },  
  10.             dataSource: {  
  11.                 type: "json",  
  12.                 transport: {  
  13.                     contentType: "application/json; charset=utf-8",  
  14.                     type: "GET", // type: "GET","POST"  
  15.                     dataType: "json", //dataType: "json","odata"  
  16.                     read: "/api/StudentAPI/GetAllStudents",  //API URL  
  17.                 },  
  18.                 pageSize: 5,  
  19.                 schema: {  
  20.                     model: {  
  21.                         fields: {  
  22.                             RegNo: {  
  23.                                 type: "string"  
  24.                             },  
  25.                             Name: {  
  26.                                 type: "string"  
  27.                             },  
  28.                             Address: {  
  29.                                 type: "string"  
  30.                             },  
  31.                             PhoneNo: {  
  32.                                 type: "string"  
  33.                             },  
  34.                             admissionDate: {  
  35.                                 type: "date"  
  36.                             }  
  37.                         }  
  38.                     }  
  39.                 },  
  40.             },  
  41.             filterable: true, //Allow Filtering  
  42.             sortable: true, //Allow Sorting of Columns  
  43.             groupable: true, //Allow Grouping  
  44.             columnMenu: true, //Show/Hide Columns  
  45.             reorderable: true, //Allow Column Reordering  
  46.             resizable: true,  
  47.             pageable: {  
  48.                 refresh: true,  
  49.                 pageSizes: true,  
  50.                 buttonCount: 5  
  51.             },  
  52.             columns: [{  
  53.                 field: "regNo",  
  54.                 title: "Regd No",  
  55.                 filterable: { multi: true, search: true } //Allow multi select Filter and Search   
  56.             }, {  
  57.                 field: "name",  
  58.                 title: "Student Name",  
  59.                 filterable: { multi: true, search: true } //Allow multi select Filter and Search  
  60.             }, {  
  61.                 field: "address",  
  62.                 title: "Address",  
  63.                 filterable: { multi: true, search: true } //Allow multi select Filter and Search  
  64.             }, {  
  65.                 field: "phoneNo",  
  66.                 title: "Phone No",  
  67.                 filterable: { multi: true, search: true } //Allow multi select Filter and Search  
  68.             }, {  
  69.                 field: "admissionDate",  
  70.                 title: "Admission Date",  
  71.                 format: "{0:MM-dd-yyyy}", //Date time Formatting Eg: 03-27-2017  
  72.                 filterable: { multi: true, search: true } //Allow multi select Filter and Search  
  73.             }]  
  74.         });  
  75.     });  
  76. </script>   
Now, run the application and navigate to the corresponding page to see output.

Export to Excel using Kendo UI in ASP.NET Core by Nishan

Toolbar

["excel"] will add an "Export to Excel" button on the grid, as shown in figure above. Now, let's export the above data to Excel. The exported Excel will look like the following.
 
Export to Excel using Kendo UI in ASP.NET Core by Nishan
 
You can also group the data by Columns, Reorder, FIlter by Columns and Show/Hide Columns as per your requirement. When you export the Excel, the data will be exported as per the selection.

Sample Grouped and Ungrouped Data before and after Exporting data is shown in given figure. 
 
Ungrouped data

Export to Excel using Kendo UI in ASP.NET Core by Nishan

Grouped data

Export to Excel using Kendo UI in ASP.NET Core by Nishan

Note that Excel Export relies on a JavaScript library called jszip. Let's reference this jszip JavaScript library.
  1. <script src="https://cdnjs.cloudflare.com/ajax/libs/jszip/2.4.0/jszip.min.js"></script>   
Kendo UI provides awesome and easiest way to configure the export button.
  1. $(document).ready(function () {
  2.     $("#Grid").kendoGrid({
  3.      toolbar: ["excel"],
  4.      excel: {
  5.           fileName: "Demo Excel From Kendo.xsls",
  6.           filterable: true, //Allow Filtering
  7.           allPages: true
  8.          },
  9.          ............. ///other codes
  10.     }); //End of kendoGrid
  11. }); //End of ready function

What if I want to place a Export to Excel button outside the Grid?

Well, in this case, you can complete this task easily. You just need to write a few lines of JavScript to do this.

Kendo UI has saveAsExcel() methd that serves to export data into Excel easily. Let's assume we have a button with labeled with text Export to Excel just outside of the Kendo Grid.

Now, we will say browser to download the Excel when the button is clicked. 
  1. <script>
  2.   $("#btnExportToExcel").kendoButton({
  3.     // invoke saveAsExcel() when button is clicked
  4.     click: function()
  5.     {
  6.        $("#Grid").data("kendoGrid").saveAsExcel()
  7.      }
  8.  });
  11. //convert Grid to Kendo Grid using Kendo
  12. $("#Grid").kendoGrid({
  13.        toolbar:[“excel”],
  14.           excel:{
  15.              filename:”Data Export to Excel.xslx”,
  16.              filterable:true, //Allow filtering
  17.              allPages:false //Include all data to Excel
  18.           },
  19.          ............
  20.          //other code omitted for now.
  21.     });
  23. </script>
We learned how to
  • Convert API Data into Kendo UI Grid
  • Add some functionality on kendo UI Grid like Filtering, pagination, sortable, ColumnMenu, etc
  • Export Data to Excel.
  • Export data to Excel creating custom button outside Kendo Grid.

Up Next
    Ebook Download
    View all
    Learn
    View all
    Membership not found