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:

<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title></title>
<link rel="stylesheet" href="http://kendo.cdn.telerik.com/2015.2.805/styles/kendo.common.min.css">
<link rel="stylesheet" href="http://kendo.cdn.telerik.com/2015.2.805/styles/kendo.rtl.min.css">
<link rel="stylesheet" href="http://kendo.cdn.telerik.com/2015.2.805/styles/kendo.default.min.css">
<link rel="stylesheet" href="http://kendo.cdn.telerik.com/2015.2.805/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/2015.2.805/js/angular.min.js"></script>
<script src="http://kendo.cdn.telerik.com/2015.2.805/js/jszip.min.js"></script>
<script src="http://kendo.cdn.telerik.com/2015.2.805/js/kendo.all.min.js"></script>
</head>
<body>
<div id="example">
<div class="demo-section k-header wide">
<div>
<h4>Export to Excel</h4>
<div id="test-grid" data-role="grid"
date-scrollable="true"
data-editable="true"
data-toolbar="['excel']"
data-columns="[
{ 'field': 'Name', 'width': 270 },
{ 'field': 'Category' },
]"
data-bind="source: products,
visible: isVisible"
style="height: 300px"></div>
</div>
</div>
</div>
</body>
</html>

Note: The Excel generation feature needs the JSZip JavaScript library.

JavaScript in MVVM Model

var viewModel = kendo.observable({
isVisible: true,
products: new kendo.data.DataSource({
schema: {
model: {
id: "id",
fields: {
Name: { type: "string" },
Category: { type: "string" }
}
}
},
batch: true,
transport: {
read: {
url: "/api/Products",
dataType: "json"
},
parameterMap: function(options, operation) {
if (operation !== "read" && options.models) {
return {models: kendo.stringify(options.models)};
}
}
}
}),
});
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

<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title></title>
<link rel="stylesheet" href="http://kendo.cdn.telerik.com/2015.2.805/styles/kendo.common.min.css">
<link rel="stylesheet" href="http://kendo.cdn.telerik.com/2015.2.805/styles/kendo.rtl.min.css">
<link rel="stylesheet" href="http://kendo.cdn.telerik.com/2015.2.805/styles/kendo.default.min.css">
<link rel="stylesheet" href="http://kendo.cdn.telerik.com/2015.2.805/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/2015.2.805/js/angular.min.js"></script>
<script src="http://kendo.cdn.telerik.com/2015.2.805/js/jszip.min.js"></script>
<script src="http://kendo.cdn.telerik.com/2015.2.805/js/kendo.all.min.js"></script>
<script src="Scripts/Blob.js"></script>
<script src="Scripts/FileSaver.js"></script>
</head>
<body>
<div id="example">
<div class="demo-section k-header wide">
<div>
<h4>Export to CSV</h4>
<div id="test-grid" data-role="grid"
date-scrollable="true"
data-editable="true"
data-columns="[
{ 'field': 'Name', 'width': 270 },
{ 'field': 'Category' },
]"
data-bind="source: products,
visible: isVisible"
style="height: 300px"></div>
</div>
</div>
<div >
<button data-role="button" data-bind="visible: isVisible, enabled: isEnabled, events: { click: onClick }">Export to CSV</button>
</div>
</div>
</body>
</html>

JavaScript in MVVM Model

var viewModel = kendo.observable({
isVisible: true,
onClick:function(e)
{
e.preventDefault();
viewModel.exportCsv('test-grid', 'testdata.csv');
},
products: new kendo.data.DataSource({
schema: {
model: {
id: "id",
fields: {
Name: { type: "string" },
Category: { type: "string" }
}
}
},
batch: true,
transport: {
read: {
url: "/api/Products",
dataType: "json"
},
parameterMap: function(options, operation) {
if (operation !== "read" && options.models) {
return {models: kendo.stringify(options.models)};
}
}
}
}),
exportCsv: function (gridId, fileName) {
var grid = $("#" + gridId).data("kendoGrid");
var originalPageSize = grid.dataSource.pageSize();
var csv = '';
fileName = fileName || 'download.csv';
grid.dataSource.pageSize(grid.dataSource.view().length);
var data = grid.dataSource.view();
for (var i = 0; i < grid.columns.length; i++) {
var field = grid.columns[i].field;
var title = grid.columns[i].title || field;
if (!field) continue;
title = title.replace(/"/g, '""');
csv += '"' + title + '"';
if (i < grid.columns.length - 1) {
csv += ',';
}
}
csv += '\n';
for (var row in data) {
for (var i = 0; i < grid.columns.length; i++) {
var fieldName = grid.columns[i].field;
var template = grid.columns[i].template;
var exportFormat = grid.columns[i].exportFormat;
if (!fieldName) continue;
var value = '';
if (fieldName.indexOf('.') >= 0)
{
var properties = fieldName.split('.');
var value = data[row] || '';
for (var j = 0; j < properties.length; j++) {
var prop = properties[j];
value = value[prop] || '';
}
}
else{
value = data[row][fieldName] || '';
}
if (value && template && exportFormat !== false) {
value = _.isFunction(template)
? template(data[row])
: kendo.template(template)(data[row]);
}
value = value.toString().replace(/"/g, '""');
csv += '"' + value + '"';
if (i < grid.columns.length - 1) {
csv += ',';
}
}
csv += '\n';
}
grid.dataSource.pageSize(originalPageSize);
//EXPORT TO BROWSER
var blob = new Blob([csv], { type: 'text/csv;charset=utf-8' }); //Blob.js
saveAs(blob, fileName); //FileSaver.js
},
});
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.

17 Comments

Join the conversation! Your thoughts help the community grow.

Arul R11y ago
Nice share!!!
Arul R11y ago
Nice share!!!
Van Lai11y ago
Nice one, thx for sharing.
Shridhar Sharma11y ago
good show.
- Gowtham K11y ago
  Thank you
Gopi Chand11y ago
Nice work
- Gowtham K11y ago
  Thank you
Rakesh11y ago
Good one
- Gowtham K11y ago
  Thank you
Ankit Bansal11y ago
good..
- Gowtham K11y ago
  Thank You
Mohammed Ibrahim11y ago
nice
- Gowtham K11y ago
  Thank You
Karthikeyan K11y ago
Good one sir..Thanks for sharing
- Gowtham K11y ago
  Thank You
Vaikesh K P11y ago
Nice Share :)
- Gowtham K11y ago
  Thank You Vaikesh