Customizing The Export To Excel Functionality In Kendo Grid Using ASP.NET Web API

Gowtham K
8y
13.9k
0
0

Article

Today, I came across a scenario where I need to customize the inbuilt export to excel functionality in kendo grid while exporting the grid data, i.e where I need to get some of the column data which is hidden in grid while downloading it as excel, which can easily done using show/hide column and bind function of kendo grid.

Let we see how to implement it using ASP.NET Web API as follows,

Creating an ASP.NET Web API application.
Creating a Controller.
Test the API
Using a kendo grid with MVVM pattern
Customizing the Export to Excel functionality in kendo grid using ASP.NET Web API.

Creating an Empty ASP.NET Web API Project

Create a simple empty WEB API project as in the following figure:

Creating a Model Class

Right click on the model folder and add a new class, in my case I named it Employee.cs:

Code in Employee.cs

public class Employee
{
public Employee(int EmployeeID, string FirstName, string LastName)
{
this.EmployeeID = EmployeeID;
this.FirstName = FirstName;
this.LastName = LastName;
}
public int EmployeeID { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
}

Creating a Controller

Right click on the Controller folder and add a new controller, in my case I named it EmployeeDetailsController.cs:

Code in EmployeeDetailsController.cs

[RoutePrefix("api/EmployeeList")]
public class EmployeeDetailsController : ApiController
{
[HttpGet]
[Route("List")]
public HttpResponseMessage EmployeeList()
{
try
{
List<Employee> _emp = new List<Employee>();
_emp.Add(new Employee(1, "Bobb", "Ross"));
_emp.Add(new Employee(2, "Pradeep", "Raj"));
_emp.Add(new Employee(3, "Arun", "Kumar"));
_emp.Add(new Employee(4, "Dhivakar", "Krishnaswamy"));
_emp.Add(new Employee(5, "Rajasekar", "Jayaprakash"));
return Request.CreateResponse(HttpStatusCode.OK, _emp, Configuration.Formatters.JsonFormatter);
}
catch (Exception ex)
{
return Request.CreateResponse(HttpStatusCode.OK, ex.Message, Configuration.Formatters.JsonFormatter);
}
}
}

The above controller employee action will return an employee list as a response

Test the API

Test the API using the POSTMAN/Fiddler as in the following figure :

API End Point: /api/EmployeeList/List

Type: GET

Now the API is ready to consume.

Using a Kendo Grid with MVVM pattern

Creating a HTML page

Create a new HTML page in the project.

Design:

<!DOCTYPE html>
<html>
<head>
<title></title>
<meta charset="utf-8" />
<link rel="stylesheet" href="http://kendo.cdn.telerik.com/2016.1.412/styles/kendo.common.min.css">
<link rel="stylesheet" href="http://kendo.cdn.telerik.com/2016.1.412/styles/kendo.rtl.min.css">
<link rel="stylesheet" href="http://kendo.cdn.telerik.com/2016.1.412/styles/kendo.default.min.css">
<link rel="stylesheet" href="http://kendo.cdn.telerik.com/2016.1.412/styles/kendo.mobile.all.min.css">
<script src="http://code.jquery.com/jquery-1.9.1.min.js"></script>
<script src="http://kendo.cdn.telerik.com/2016.1.412/js/angular.min.js"></script>
<script src="http://kendo.cdn.telerik.com/2016.1.412/js/jszip.min.js"></script>
<script src="http://kendo.cdn.telerik.com/2016.1.412/js/kendo.all.min.js"></script>
<script src="Scripts/ViewModel/Core.js"></script>
<script src="Scripts/ViewModel/Data.js"></script>
<script src="Scripts/ViewModel/ViewModel.js"></script>
</head>
<body>
<div class="col-lg-12">
<div id="Main" class="main"></div>
</div>
<script type="text/x-kendo-template" id="Layout-temp">
<div class="col-lg-12">
<div id="content"></div>
</div>
</script>
<script type="text/x-kendo-template" id="Dashboard-temp">
<div class="row margin-t10">
<div class="col-lg-12">
<div data-role="grid" id="EmployeeGrid"
data-sortable="true"
data-scrollable="true"
data-toolbar="['excel']"
data-excel='{allPages:"true"}'
data-resizable="true"
data-pageable="{refresh: true, pageSizes: [25, 50, 100, 200]}"
data-columns="[
{ 'field': 'EmployeeID','width':'100px','hidden':true},
{ 'field': 'FirstName','width':'100px'},
{ 'field': 'LastName','width':'100px' }
]"
data-bind="source:Gridlist,events:{dataBound: onDataBound}" />
</div>
</div>
</script>
</body>
</html>

Creating a JavaScript files

View Model: Create a JavaScript file, in my case I named it ViewModel.Js, The View-Model is a representation of your data (the Model) which will be displayed in the View.

ViewModel.Js

(function(G, $, undefined) {
$.extend(true, G, {
KendoGrid: {
ViewModel: {
DashboardModel: new kendo.observable({
Gridlist: G.KendoGrid.Data.GridList,
onDataBound: function (e) {
$('#EmployeeGrid thead').each(function () {
kendo.bind($(this), G.KendoGrid.ViewModel.DashboardModel);
});
},
}),
}
}
});
})(window.Gni = window.Gni || {}, jQuery);

Data: Create a JavaScript file, in my case I named it Data.Js, This script is responsible to bound the DataSource by requesting the API.

Data.js

(function (G, $, K, undefined) {
$.extend(true, G, {
KendoGrid: {
Data: {
GridList: new K.data.DataSource({
//navigatable: true,
batch: true,
pageSize: 5,
transport: {
read: {
url: '/api/EmployeeList/List',
type: 'GET',
dataType: 'json',
},
parameterMap: function (data, operation) {
if (operation == "read") {
return JSON.stringify(data);
}
return data;
}
},
schema: {
model: {
id: "EmployeeID",
fields: {
"FirstName":{type:"string"},
"LastName":{type:"string"},
}
}
},
}),
}
}
});
})(window.Gni = window.Gni || {}, jQuery, kendo);

Core: Create a JavaScript file, in my case I named it Core.Js.

This core script is used to start the rendering of the templates and calls the view model to bind the data in the UI.

$(function () {
var Layout = new kendo.Layout("Layout-temp");
var DashboardView = new kendo.View("Dashboard-temp", { model: Gni.KendoGrid.ViewModel.DashboardModel });
var router = new kendo.Router({
init: function () {
Layout.render("#Main");
Layout.showIn("#content", DashboardView);
}
});
router.start();
});

Result:

Export to Excel:

As we expect the EmployeeID column is not exported, but our goal is to get the Employee ID in Excel which is hidden from grid,it is possible by modifying the bind method of grid with the help of show and hide column method.

showColumn ( number/string/object )

This method is used to show the specified column, where It can have a parameters like number/string/object.

hideColumn(number/string/object)

This method is used to hide the specified column, where it can have a parameters like number/string/object.

Updated code in core.js

$(function () {
var Layout = new kendo.Layout("Layout-temp");
var DashboardView = new kendo.View("Dashboard-temp", { model: Gni.KendoGrid.ViewModel.DashboardModel });
var router = new kendo.Router({
init: function () {
Layout.render("#Main");
Layout.showIn("#content", DashboardView);
}
});
router.start();
var exportFlag = false;
$("#EmployeeGrid").data("kendoGrid").bind("excelExport", function (e) {
console.log("column")
if (!exportFlag) {
e.sender.showColumn(0);
e.preventDefault();
exportFlag = true;
setTimeout(function () {
e.sender.saveAsExcel();
});
window.location.reload();
} else {
e.sender.hideColumn(0);
exportFlag = false;
}
});
});

The above kendo grid bind methods defines that while exporting the grid data to excel just show up the first column which is EmployeeID in grid export the data to excel and hide the column in grid once the export process is completed.

Export to Excel: