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.
  1. public class Product
  2. {
  3. [Key]
  4. [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
  5. public int ProductID { get; set; }
  6. [Required]
  7. public string ProductName { get; set; }
  8. [Required]
  9. public string UnitPrice { get; set; }
  10. }

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.

  1. public class ProductContext:DbContext
  2. {
  3. public ProductContext() : base("name=TestConnection") { }
  4. public DbSet<Product> Products { get; set; }
  6. }
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

  1. <!DOCTYPE html>
  2. <html xmlns="http://www.w3.org/1999/xhtml">
  3. <head>
  4. <link rel="stylesheet" href="http://cdn.kendostatic.com/2014.3.1316/styles/kendo.common.min.css" />
  5. <link rel="stylesheet" href="http://cdn.kendostatic.com/2014.3.1316/styles/kendo.default.min.css" />
  6. <link rel="stylesheet" href="http://cdn.kendostatic.com/2014.3.1316/styles/kendo.dataviz.min.css" />
  7. <link rel="stylesheet" href="http://cdn.kendostatic.com/2014.3.1316/styles/kendo.dataviz.default.min.css" />
  8. <script src="http://cdn.kendostatic.com/2014.3.1316/js/jquery.min.js"></script>
  9. <script src="http://cdn.kendostatic.com/2014.3.1316/js/kendo.all.min.js"></script>
  10. <script src="http://cdn.kendostatic.com/2014.3.1029/js/jszip.min.js"></script>
  11. <title></title>
  12. </head>
  13. <body>
  14. <div class="container" id="example">
  15. <div class="row">
  17. <div id="test-grid" data-role="grid"
  18. data-scrollable="true"
  19. data-editable="false"
  20. data-selectable="true"
  21. data-toolbar="['excel']"
  22. data-excel='{fileName: "ProductDetail.xlsx"}'
  23. data-columns="[
  25. { 'field': 'ProductName','width':'100px' },
  26. { 'field': ' UnitPrice','width':'100px'},
  27. ]"
  28. data-pageable='true'
  29. data-bind="source:products"
  30. style="height: 300px"></div>
  32. </div>
  33. </div></body>
  34. </html>

The Rest service End Point is: api/products

JavaScipt with MVVM Model

  1. <script>
  2. var viewModel = kendo.observable({
  3. isVisible: true,
  5. products: new kendo.data.DataSource({
  6. schema: {
  7. model: {
  8. id: "ProductID",
  9. fields: {
  10. ProductName: { type: "string" },
  11. UnitPrice: { type: "string" }
  12. }
  13. }
  14. },
  15. batch: true,
  16. transport: {
  17. read: {
  18. url: "api/Products",
  19. dataType: "json"
  20. },
  21. parameterMap: function (options, operation) {
  22. if (operation !== "read" && options.models) {
  23. return { models: kendo.stringify(options.models) };
  24. }
  25. }
  26. }
  27. })
  28. });
  29. kendo.bind($("#example"), viewModel);
  30. </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

  1. <!DOCTYPE html>
  2. <html xmlns="http://www.w3.org/1999/xhtml">
  3. <head>
  4. <link rel="stylesheet" href="http://cdn.kendostatic.com/2014.3.1316/styles/kendo.common.min.css" />
  5. <link rel="stylesheet" href="http://cdn.kendostatic.com/2014.3.1316/styles/kendo.default.min.css" />
  6. <link rel="stylesheet" href="http://cdn.kendostatic.com/2014.3.1316/styles/kendo.dataviz.min.css" />
  7. <link rel="stylesheet" href="http://cdn.kendostatic.com/2014.3.1316/styles/kendo.dataviz.default.min.css" />
  8. <script src="http://cdn.kendostatic.com/2014.3.1316/js/jquery.min.js"></script>
  9. <script src="http://cdn.kendostatic.com/2014.3.1316/js/kendo.all.min.js"></script>
  10. <script src="http://cdn.kendostatic.com/2014.3.1029/js/jszip.min.js"></script>
  11. <title></title>
  12. </head>
  13. <body>
  14. <div class="container" id="example">
  15. <div class="row">
  17. <div id="test-grid" data-role="grid"
  18. data-scrollable="true"
  19. data-editable="false"
  20. data-selectable="true"
  21. data-toolbar="['excel']"
  22. data-excel='{fileName: "ProductDetail.xlsx",filterable:"true"}'
  23. data-columns="[
  25. { 'field': 'ProductName','width':'100px' },
  26. { 'field': ' UnitPrice','width':'100px'},
  27. ]"
  28. data-pageable='true'
  29. data-bind="source:products"
  30. style="height: 300px"></div>
  32. </div>
  33. </div>
  34. </body>
  35. </html>
JavaScipt with MVVM Model
  1. var viewModel = kendo.observable({
  2. isVisible: true,
  4. products: new kendo.data.DataSource({
  5. schema: {
  6. model: {
  7. id: "ProductID",
  8. fields: {
  9. ProductName: { type: "string" },
  10. UnitPrice: { type: "string" }
  11. }
  12. }
  13. },
  14. batch: true,
  15. transport: {
  16. read: {
  17. url: "api/Products",
  18. dataType: "json"
  19. },
  21. parameterMap: function (options, operation) {
  22. if (operation !== "read" && options.models) {
  23. return { models: kendo.stringify(options.models) };
  24. }
  25. }
  26. }
  27. })
  28. });
  29. 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

  1. <div class="container" id="example">
  2. <div class="row">
  4. <div id="test-grid" data-role="grid"
  5. data-scrollable="true"
  6. data-editable="false"
  7. data-selectable="true"
  8. data-toolbar="['excel']"
  9. data-excel='{fileName: "ProductDetail.xlsx",filterable:"true",allPages:"true"}'
  10. data-columns="[
  12. { 'field': 'ProductName','width':'100px' },
  13. { 'field': ' UnitPrice','width':'100px'},
  14. ]"
  15. data-pageable='true'
  16. data-bind="source:products"
  17. style="height: 300px"></div>
  19. </div>
  20. </div>
  21. </div>

JavaScipt with MVVM Model

  1. var viewModel = kendo.observable({
  2. isVisible: true,
  4. products: new kendo.data.DataSource({
  5. schema: {
  6. model: {
  7. id: "ProductID",
  8. fields: {
  9. ProductName: { type: "string" },
  10. UnitPrice: { type: "string" }
  11. }
  12. }
  13. },
  14. batch: true,
  15. pageSize:5,
  16. transport: {
  17. read: {
  18. url: "api/Products",
  19. dataType: "json"
  20. },
  22. parameterMap: function (options, operation) {
  23. if (operation !== "read" && options.models) {
  24. return { models: kendo.stringify(options.models) };
  25. }
  26. }
  27. }
  28. })
  29. });
  30. 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