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

// GET: api/StudentAPI/GetAllStudents
//[HttpPost]
[Route("api/StudentAPI/GetAllStudents")]
public IEnumerable<PersonalDetail> GetAllStudents()
{
List<PersonalDetail> students = new List<PersonalDetail>
{
new PersonalDetail{
RegNo = "2017-0001",
Name = "Nishan",
Address = "Kathmandu",
PhoneNo = "9849845061",
AdmissionDate = DateTime.Now
},
new PersonalDetail{
RegNo = "2017-0002",
Name = "Namrata Rai",
Address = "Bhaktapur",
PhoneNo = "9849845062",
AdmissionDate = DateTime.Now
},
new PersonalDetail{
RegNo = "2017-0003",
Name = "Junge Rai",
Address = "Pokhara",
PhoneNo = "9849845063",
AdmissionDate = DateTime.Now
},
new PersonalDetail{
RegNo = "2017-0004",
Name = "Sunita Ghimire",
Address = "Kathmandu",
PhoneNo = "9849845064",
AdmissionDate = DateTime.Now
},
new PersonalDetail{
RegNo = "2017-0005",
Name = "John ",
Address = "Bhaktapur",
PhoneNo = "9849845065",
AdmissionDate = DateTime.Now
},
new PersonalDetail{
RegNo = "2017-0006",
Name = "Jenny Moktan ",
Address = "Kathmandu",
PhoneNo = "9849845066",
AdmissionDate = DateTime.Now
},
new PersonalDetail{
RegNo = "2017-0007",
Name = "Kalpana Ghimire ",
Address = "Pokhara",
PhoneNo = "9849845067",
AdmissionDate = DateTime.Now
},
new PersonalDetail{
RegNo = "2017-0008",
Name = "Krishna Manadal",
Address = "Kathmandu",
PhoneNo = "9849845067",
AdmissionDate = DateTime.Now
},
new PersonalDetail{
RegNo = "2017-0009",
Name = "Karishman Luitel",
Address = "Bhaktapur",
PhoneNo = "9849845067",
AdmissionDate = DateTime.Now
},
new PersonalDetail{
RegNo = "2017-0010",
Name = "Hari Simkhada",
Address = "Bhaktapur",
PhoneNo = "9849845067",
AdmissionDate = DateTime.Now
},
};
return students;
}

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.

<link rel="stylesheet" href="http://kendo.cdn.telerik.com/2017.1.118/styles/kendo.common.min.css" />
<link rel="stylesheet" href="http://kendo.cdn.telerik.com/2017.1.118/styles/kendo.rtl.min.css" />
<link rel="stylesheet" href="http://kendo.cdn.telerik.com/2017.1.118/styles/kendo.silver.min.css" />
<link rel="stylesheet" href="http://kendo.cdn.telerik.com/2017.1.118/styles/kendo.mobile.all.min.css" />
<script src="http://code.jquery.com/jquery-1.12.4.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jszip/2.4.0/jszip.min.js"></script>
<script src="http://kendo.cdn.telerik.com/2017.1.118/js/kendo.all.min.js"></script>
<div class="panel panel-primary">
<div class="panel-heading">Test Data from API</div>
<div class="panel-body">
<div id="Grid"></div>
</div>
</div>

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.

<script>
$(document).ready(function () {
$("#Grid").kendoGrid({
toolbar: ["excel"],
excel: {
fileName: "Demo Excel From Kendo.xlsx",
filterable: true, //Allow Filtering
allPages: true
},
dataSource: {
type: "json",
transport: {
contentType: "application/json; charset=utf-8",
type: "GET", // type: "GET","POST"
dataType: "json", //dataType: "json","odata"
read: "/api/StudentAPI/GetAllStudents", //API URL
},
pageSize: 5,
schema: {
model: {
fields: {
RegNo: {
type: "string"
},
Name: {
type: "string"
},
Address: {
type: "string"
},
PhoneNo: {
type: "string"
},
admissionDate: {
type: "date"
}
}
}
},
},
filterable: true, //Allow Filtering
sortable: true, //Allow Sorting of Columns
groupable: true, //Allow Grouping
columnMenu: true, //Show/Hide Columns
reorderable: true, //Allow Column Reordering
resizable: true,
pageable: {
refresh: true,
pageSizes: true,
buttonCount: 5
},
columns: [{
field: "regNo",
title: "Regd No",
filterable: { multi: true, search: true } //Allow multi select Filter and Search
}, {
field: "name",
title: "Student Name",
filterable: { multi: true, search: true } //Allow multi select Filter and Search
}, {
field: "address",
title: "Address",
filterable: { multi: true, search: true } //Allow multi select Filter and Search
}, {
field: "phoneNo",
title: "Phone No",
filterable: { multi: true, search: true } //Allow multi select Filter and Search
}, {
field: "admissionDate",
title: "Admission Date",
format: "{0:MM-dd-yyyy}", //Date time Formatting Eg: 03-27-2017
filterable: { multi: true, search: true } //Allow multi select Filter and Search
}]
});
});
</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.

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.

<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.

$(document).ready(function () {
$("#Grid").kendoGrid({
toolbar: ["excel"],
excel: {
fileName: "Demo Excel From Kendo.xsls",
filterable: true, //Allow Filtering
allPages: true
},
............. ///other codes
}); //End of kendoGrid
}); //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.

<script>
$("#btnExportToExcel").kendoButton({
// invoke saveAsExcel() when button is clicked
click: function()
{
$("#Grid").data("kendoGrid").saveAsExcel()
}
});
//convert Grid to Kendo Grid using Kendo
$("#Grid").kendoGrid({
toolbar:[“excel”],
excel:{
filename:”Data Export to Excel.xslx”,
filterable:true, //Allow filtering
allPages:false //Include all data to Excel
},
............
//other code omitted for now.
});
</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.