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.
ConclusionWe have seen how the new export capability in Kendo Grid is more powerful and flexible.
Thank you