Export data to excel in Asp.net MVC


Exporting data from asp.net page to excel is one of the most common functionality used by asp.net web form developers. This is mostly done by rendering already displayed gridview into HTMLTextWriter and using StringWriter.

However, In Asp.net MVC applications, we don’t use GridView server we still can take advantage of GridView control to export data to excel.

In this short article, I will focus on how easily we can display data from database in grid format (without jQuery grid plugin) and how to provide export to excel functionality in Asp.net MVC

Get data from database using LINQ to SQL

I am not going to explain details about how to add .dbml file to create data context. If you are unsure, please read ScottGu’s blog here

In the default project, under Views -> Home I have added new aspx view file (you may want to use cshtml file instead) and created ActionResult EmployeeInfo in the home controller

public ActionResult EmployeeInfo()
            DBContext dataContext = new DBContext();
            ViewData["Employees"] = dataContext.emps.ToList();
            return View();            

The EmployeeInfo.aspx view file displays data returned by model in tabular format and also provides Actionlink “Export to excel” which will call FileExport action method from home controller

<%@ Page Title="" Language="C#" MasterPageFile="~/Views/Shared/Site.Master" Inherits="System.Web.Mvc.ViewPage<dynamic>" %>
<asp:Content ID="Content1" ContentPlaceHolderID="TitleContent" runat="server">
    Export grid to excel
<asp:Content ID="Content2" ContentPlaceHolderID="MainContent" runat="server">
    <form id="form1" runat="server">
        File Export</h2>
    <hr />
    <div id="ShowList" title="File List">
        <table id="Grid1">
            <thead style="background-color: #507CD1;color:White">
                    Employee number
                    Employee Name
                    Hire date
                  foreach (var item in (List<MVCUpload.emp>)ViewData["Employees"])
                        <%:item.empno %>
                        <%:item.ename %>
                        <%:item.job %>
        <%= Html.ActionLink("Export to excel", "FileExport","home")%>

Run the project and change url as, http://localhost:xxxx/home/EmployeeInfo. The view is displayed in browser as below (The random database used to create this example)


Generally, in Asp.net web form, export gridview to excel functionality excel file is created which contains all the formatting of exported gridview. Although in MVC, we dont use actual GridView control, while exporting data, we can take advantage of “dear” GridView server control.

Before going ahead, add following reference at top of homecontroller class.

using System.IO;
using System.Web.UI;
using System.Web.UI.WebControls;

See action method FileExport below. We have again retrieved data from DB context in order to bind GridView object.

public ActionResult FileExport()
            //LINQ to SQL context class
            DBContext dataContext = new DBContext();
            //Create gridview object - Make sure you have added reference to Syster.Web.UI.ServerControls
            GridView gv = new GridView();
            //Call Method to apply style to gridview - This is optional part and can be avoided 
            StyleGrid(ref gv);
            //assing datasource from context class to gridview object
            gv.DataSource = dataContext.emps.ToList();           
            //Important - bind gridview object
            //We have gridview object ready in memory. Follow normal "export gridview to excel" code
            Response.AddHeader("content-disposition", "attachment; filename=YourFileName.xls");
            Response.ContentType = "application/excel";
            //Ccreate string writer object and pass it to HtmlTextWriter object
            StringWriter sw = new StringWriter();
            HtmlTextWriter htw = new HtmlTextWriter(sw);
            //Call gridiview objects RenderControl method to output gridview content to HtmlTextWriter
            //Pass rendered string to Response object which would be presented to user for download
            return View("EmployeeInfo");

The code is well commented for understanding. In summary, a GridView object is created using Web.ServerControls. Then data is fetched from data context class and assigned to GridView object as datasource and called DataBind()

If you want to replicate style of table displayed on page in exported excel file, then create a function to format gridview object in same way. i.e. this will be separate formatting for export only. In case you change the Css of table displayed on page then its up to you to modify the style of exported grid as well. The definition of StyleGrid method used in above code is

private void StyleGrid(ref GridView gv)
            gv.HeaderStyle.BackColor = System.Drawing.Color.FromName("#507CD1");
            gv.HeaderStyle.Font.Bold = true;
            gv.HeaderStyle.ForeColor = System.Drawing.Color.White;
            gv.RowStyle.BackColor = System.Drawing.Color.FromName("EFF3FB");
            gv.AlternatingRowStyle.BackColor = System.Drawing.Color.Silver;

Now, test export method by clicking “Export to excel” link.

Hope this helps someone looking for answer 🙂