Export Webgrid Data To Microsoft Excel In ASP.NET MVC 5

In this blog, you will learn how to export Webgrid data to Microsoft Excel in ASP.NET MVC 5.

Hi again everyone. In this tutorial, I am going to explain how to export Webgrid data into Microsoft Excel sheets. If you are new to Webgrid, please refer to  the links of the posts, given below, to understand the basics of Webgrid.

• Webgrid implementation in ASP.NET MVC5 application
• Implementing Custom paging in Webgrid in MVC5

If you have gone through these tutorials, you will get a basic understanding of Webgrid implementation. In this tutorial, I am going to use the code of the previous posts. I have implemented the screenshots given below of the Webgrid in the previous posts.



Exporting Webgrid to Excel in ASP.NET MVC

1. Create Visual Studio Application in (2013 or 2015).
2. Please refe to this tutorial Webgrid implementation in ASP.NET MVC5 Application. I am using the code in this tutorial, without creating a new Application.
   
3. I am adding an extra code to implement exporting the data.
   
4. Add ActionLink, given below, in an Index View, as shown below:
   

   <!--Create a link button to export data-->
    @Html.ActionLink("Export to Excel", "PrintExcel", "Home", new { @class="btn btn-success"})
   <br/>

5. The complete code of the View is shown below:
   
   Index.cshtml
   

   @model List<WebGridinMVC5.Models.UserTable>
   @{
       ViewBag.Title = "www.mitechdev.com";
       //create object for webgrid to use Webgrid helper
       var grid = new WebGrid(source: Model, canPage: true, rowsPerPage: 7);
       //pager
       grid.Pager(WebGridPagerModes.All);
    }
   <h2>Fetching and displaying data from server in WebGrid</h2><br/>
   <!--Create a link button to export data-->
    @Html.ActionLink("Export to Excel", "PrintExcel", "Home", new { @class="btn btn-success"})
   <br/>
   <!--web grid styles starts-->
   <style type="text/css">
       /*styles for altering rows*/
       .webgrid-row-style {
           padding: 3px 7px 2px;
       }
        .webgrid-alternating-row {
           background-color: #EAF2D3;
           padding: 3px 7px 2px;
       }
   </style>
   <!--web grid styles Ends-->
   <!--web grid display element-->
   <div id="webgrid">
       @grid.GetHtml(
       //styles class for table
       //here i used bootstrap table templates
               tableStyle: "table table-responsive backcolor",
               headerStyle:"wedgrid-header",
               footerStyle:"webgrid-footer",
               alternatingRowStyle:"webgrid-alternating-row",
               rowStyle:"webgrid-row-style",
               //binding table column to the grid
               columns:grid.Columns(
               grid.Column(header:"Serial No",format:@<text><div>@(item.WebGrid.Rows.IndexOf(item)+1)</div></text>),
               grid.Column(columnName:"Name",header:"Name"),
               grid.Column(header:"Email", format:@<text><a href="mailto:@item.Email">@item.Email</a></text>),
       grid.Column(columnName:"PhoneNumber",header:"Phone No"),
               grid.Column(header:"Is Active", format:@<text><input type="checkbox" checked="@item.Status" disabled="disabled" /></text>)
               )
       )
   </div>

6. You can get the code, shown above, from the previous post's links also (I specified above).

Create method to Export the Data into Excel

1. Now, add a method to export the data into Microsoft Excel format. When you click 'Export to Excel' button in an Index view page this method (PrintExcel) gets the grid data from the Server and exports it to Excel Sheet.
   
   
2. Replace the HomeController.cs file with the code, given below:
   
   HomeController.cs
   

   using System;
   using System.Collections.Generic;
   using System.Linq;
   using System.Web;
   using System.Web.Mvc;
   using System.Web.Helpers;
   using WebGridinMVC5.Models;
   
   namespace WebGridinMVC5.Controllers
   {
       public class HomeController : Controller
       {
           //Fetch data from database
           public ActionResult Index()
           {
               List lst = new List();
               using (DatabaseEntities db = new DatabaseEntities())
               {
                   lst = db.UserTables.ToList();
               }
               //I am passing data ti view through model binding
               return View(lst);
   
        }
           //Added method to controller.
           //method to export data to Excel
           public void PrintExcel()
           {
               List exceldata = new List();
               using (DatabaseEntities db = new DatabaseEntities())
               {
                   exceldata = db.UserTables.ToList();
               }
               //create object to webgrid
               WebGrid grid = new WebGrid(source: exceldata,canPage:false, canSort: false);
               string gridData = grid.GetHtml(
               columns: grid.Columns(
                               grid.Column("UserID", "UserID"),
                               grid.Column("Name", "Name"),
                               grid.Column("Email", "Email"),
                               grid.Column("PhoneNumber", "PhoneNumber"),
                               grid.Column("Status", "Status")
                               )).ToString();
                   Response.ClearContent();
               //give name to excel sheet.
                   Response.AddHeader("content-disposition", "attachment; filename=UserData.xls");
               //specify content type
                   Response.ContentType = "application/excel";
               //write excel data using this method
                   Response.Write(gridData);
                   Response.End();
           }
    }
   }

3. Now, run the Application and see the output.
   
   
   
   
4. Now, click on "Export to Excel" and you will get the Window, as shown below:
   
   
   

Conclusion: I hope, this blog may be helpful for many readers.