Sep
10
2010

Export data to excel in Asp.net MVC

Introduction

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>
<asp:Content ID="Content2" ContentPlaceHolderID="MainContent" runat="server">
    <form id="form1" runat="server">
    <h2>
        File Export</h2>
    <hr />
    <div id="ShowList" title="File List">
        <table id="Grid1">
            <thead style="background-color: #507CD1;color:White">
                <td>
                    Employee number
                </td>
                <td>
                    Employee Name
                </td>
                <td>
                    Job
                </td>
                <td>
                    Hire date
                </td>
            </thead>
            <tbody>
                <%
                  foreach (var item in (List<MVCUpload.emp>)ViewData["Employees"])
                    {
                %><tr>
                    <%
                    %><td>
                        <%:item.empno %>
                    </td>
                    <td>
                        <%:item.ename %>
                    </td>
                    <td>
                        <%:item.job %>
                    </td>
                    <td>
                        <%:item.hiredate%>
                    </td>
                </tr>
                <%
  }
                %>
            </tbody>
        </table>
        <%= Html.ActionLink("Export to excel", "FileExport","home")%>
    </div>
    </form>
</asp:Content>

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
            gv.DataBind();
            //We have gridview object ready in memory. Follow normal "export gridview to excel" code
            Response.ClearContent();
            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
            gv.RenderControl(htw);
            //Pass rendered string to Response object which would be presented to user for download
            Response.Write(sw.ToString());
            Response.End();
            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 :)

Comments (8) -

VenkatapathiRaju

very nice article and it is very useful in my project ..!!!
And i very thankful to Mr.KedarRKulkarni...!!!

serkan

nice tip, thanks.

vinay

This is really cool article . i have got one have a look
dotnetpools.com/.../

Andrei

you are doing a great job..thank you!

cheap shoes online

This site was... how do I say it? Relevant!! Finally I have found something that helped me. Thanks!

My web page ...  cheap shoes online - bookit.zendesk.com/.../45844100-Losing-Fat-Step-By-Step

Ahmad

Touche. Outstanding arguments. Keep up the good effort.

Feel free to surf to my page best shoes [ Ahmad - 5linxuniversity.zendesk.com/.../76170623-Get-Off-To-A-Running-Start-With-Microsoft-Word-Xp ]

summer shoes

Hey great website! Does running a blog similar to this take a large amount of work? I have very little knowledge of computer programming however I had been hoping to start my own blog soon. Anyways, if you have any suggestions or tips for new blog owners please share. I know this is off topic however I simply wanted to ask. Cheers!

Feel free to surf to my site  summer shoes - reschedge.zendesk.com/.../76578193-Hints-For-Discovering-The-Best-Black-Shoes

hiking gear

It's going to be ending of mine day, but before ending I am reading this great paragraph to improve my experience.

Feel free to surf to my weblog ...  hiking gear - Foammattress247.org/.../

Pingbacks and trackbacks (1)+

About Me

You are visiting personal website of Kedar (KK)

Please go here to know more about me

Disclaimer

The opinions expressed here represent my own and not those of my past or present employers.

The concept/code provided on this site may not work as described. If you are using any code provided on this site. Then, please test it thoroughly. I shall not be responsible for any issues arising in the code. 

Month List