Export Grid Data To Excel In Advance Kendo UI Using MVC WEB API And Entity Framework

最新推荐文章于 2018-02-23 11:04:37 发布

努力成为好爸爸

最新推荐文章于 2018-02-23 11:04:37 发布

阅读量364

点赞数

分类专栏： Kendo UI MVC,MVP,MVVM 文件操作

MVC,MVP,MVVM 同时被 3 个专栏收录

45 篇文章 0 订阅

订阅专栏

文件操作

45 篇文章 0 订阅

订阅专栏

Kendo UI

12 篇文章 0 订阅

订阅专栏

ExcelExport.rar

Download 100% FREE Spire Office APIs

Main Objective of this Article

This article shows how to export grid data to excel in advance Kendo UI using MVC WEB API and Entity Framework.

Requirements

VS2010 and above
Fiddler/Postman for testing
SQL Server 2008 and above

Before going through this article ensure that you have a basic understanding of the MVC Architecture, ASP.NET Web API and jQuery.

Description

Let us start with creating a REST service using WEB API.

Just create a WEB API project in Visual Studio as shown in the following Figures 1 and 2:

Figure 1

Figure 2

Creating a Model Class

Right-click on the model folder and create a class. In my case I named it Product.

Write the following code in the Product.cs model class.

public class Product
{
[Key]
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public int ProductID { get; set; }
[Required]
public string ProductName { get; set; }
[Required]
public string UnitPrice { get; set; }
}

Here I am using Entity Framework Code first technique so we need to create the context class.

Right-click on the model folder and create one more class. In my case I named it ProductContext.

Write the following code in ProductContext class.

public class ProductContext:DbContext
{
public ProductContext() : base("name=TestConnection") { }
public DbSet<Product> Products { get; set; }
}

Scaffolding the WEB API Controller Class

Note: Before doing Scaffolding build your application once.

Right-click on Controller folder then select Add, then Controller and create a WEB API class as shown in 3 and 4.

Figure 3

Figure 4

The preceding procedure will scaffold the RESTfull service in the ProductsController.cs.

You will get some pre-defined HTTP GET, POST, PUT and DELETE requests/responses in the products Controller. Modify the code based on your application requirements. For this example I didn't modified the code.

Now the REST services are created, it's time to create a Kendo UI Grid View to consume the services.

Before implementing the service in the Kendo UI once check that in Postman / Fiddler.

Using a Kendo Grid with remote binding

Create an HMTL page in your project, in my case I named it GridtoExcel.html.

Design in GridtoExcel.html

<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<link rel="stylesheet" href="http://cdn.kendostatic.com/2014.3.1316/styles/kendo.common.min.css" />
<link rel="stylesheet" href="http://cdn.kendostatic.com/2014.3.1316/styles/kendo.default.min.css" />
<link rel="stylesheet" href="http://cdn.kendostatic.com/2014.3.1316/styles/kendo.dataviz.min.css" />
<link rel="stylesheet" href="http://cdn.kendostatic.com/2014.3.1316/styles/kendo.dataviz.default.min.css" />
<script src="http://cdn.kendostatic.com/2014.3.1316/js/jquery.min.js"></script>
<script src="http://cdn.kendostatic.com/2014.3.1316/js/kendo.all.min.js"></script>
<script src="http://cdn.kendostatic.com/2014.3.1029/js/jszip.min.js"></script>
<title></title>
</head>
<body>
<div class="container" id="example">
<div class="row">
<div id="test-grid" data-role="grid"
data-scrollable="true"
data-editable="false"
data-selectable="true"
data-toolbar="['excel']"
data-excel='{fileName: "ProductDetail.xlsx"}'
data-columns="[
{ 'field': 'ProductName','width':'100px' },
{ 'field': ' UnitPrice','width':'100px'},
]"
data-pageable='true'
data-bind="source:products"
style="height: 300px"></div>
</div>
</div></body>
</html>

The Rest service End Point is: api/products

JavaScipt with MVVM Model

<script>
var viewModel = kendo.observable({
isVisible: true,
products: new kendo.data.DataSource({
schema: {
model: {
id: "ProductID",
fields: {
ProductName: { type: "string" },
UnitPrice: { 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);
</script>

Result in a browser

Click on Export to Excel button to export the grid data into excel sheet.

Excel Sheet

Export Grid Data to Excel with filtering

Design in GridtoExcel.html

<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<link rel="stylesheet" href="http://cdn.kendostatic.com/2014.3.1316/styles/kendo.common.min.css" />
<link rel="stylesheet" href="http://cdn.kendostatic.com/2014.3.1316/styles/kendo.default.min.css" />
<link rel="stylesheet" href="http://cdn.kendostatic.com/2014.3.1316/styles/kendo.dataviz.min.css" />
<link rel="stylesheet" href="http://cdn.kendostatic.com/2014.3.1316/styles/kendo.dataviz.default.min.css" />
<script src="http://cdn.kendostatic.com/2014.3.1316/js/jquery.min.js"></script>
<script src="http://cdn.kendostatic.com/2014.3.1316/js/kendo.all.min.js"></script>
<script src="http://cdn.kendostatic.com/2014.3.1029/js/jszip.min.js"></script>
<title></title>
</head>
<body>
<div class="container" id="example">
<div class="row">
<div id="test-grid" data-role="grid"
data-scrollable="true"
data-editable="false"
data-selectable="true"
data-toolbar="['excel']"
data-excel='{fileName: "ProductDetail.xlsx",filterable:"true"}'
data-columns="[
{ 'field': 'ProductName','width':'100px' },
{ 'field': ' UnitPrice','width':'100px'},
]"
data-pageable='true'
data-bind="source:products"
style="height: 300px"></div>
</div>
</div>
</body>
</html>

JavaScipt with MVVM Model

var viewModel = kendo.observable({
isVisible: true,
products: new kendo.data.DataSource({
schema: {
model: {
id: "ProductID",
fields: {
ProductName: { type: "string" },
UnitPrice: { 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);

Result in Browser:

Excel Sheet:

Now, I am going to add more entries in the Grid with paging.

Result in browser

Entries in Pages 2

Data in excel sheet

From the above result you can observe one thing that the Mobile entry is missing in Excel sheet because of the paging in Grid.

To overcome this issue we need to use allPages property

Design in GridtoExcel.html

<div class="container" id="example">
<div class="row">
<div id="test-grid" data-role="grid"
data-scrollable="true"
data-editable="false"
data-selectable="true"
data-toolbar="['excel']"
data-excel='{fileName: "ProductDetail.xlsx",filterable:"true",allPages:"true"}'
data-columns="[
{ 'field': 'ProductName','width':'100px' },
{ 'field': ' UnitPrice','width':'100px'},
]"
data-pageable='true'
data-bind="source:products"
style="height: 300px"></div>
</div>
</div>
</div>

JavaScipt with MVVM Model

var viewModel = kendo.observable({
isVisible: true,
products: new kendo.data.DataSource({
schema: {
model: {
id: "ProductID",
fields: {
ProductName: { type: "string" },
UnitPrice: { type: "string" }
}
}
},
batch: true,
pageSize:5,
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);

Data in excel sheet

From the above result you can observe that we have got all the entries from Grid to Excel by using allPages Property.

Conclusion

We have seen how the new export capability in Kendo Grid is more powerful and flexible.

Thank you

努力成为好爸爸

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
Export Grid Data To Excel In Advance Kendo UI Using MVC WEB API And Entity Framework

ExcelExport.rarDownload 100% FREE Spire Office APIsMain Objective of this ArticleThis article shows how to export grid data to excel in advance Kendo UI using MVC WEB API and Ent
复制链接

扫一扫