Sep
10
2010
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 :)