Export to Excel is one of the most common functionality required in ASP.Net pages. Users can download the data from the datagrid into an Excel spreadsheet for offline verification and/or computation. This article includes the source code for such functionality.
How it works
This main functionality to Export a datagrid from an ASP.Net Web Form to an Excel format is actually very simple. There are several solutions for this implementation and in this example we will convert the datagrid to excel format by manipulating the MIME type (media type or Content Type) of the Response. The RenderControl method available in the .Net Framework provides the server control content to an HtmlTextWriter which is subsequently written out to the Response Stream.
private
void Button1_Click(object sender, System.EventArgs e)
{
//export to excel
Response.Clear();
Response.Buffer= true;
Response.ContentType = "application/vnd.ms-excel";
Response.Charset = "";
this.EnableViewState = false;
System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
this.ClearControls(dg);
dg.RenderControl(oHtmlTextWriter);
Response.Write(oStringWriter.ToString());
Response.End();
}
Code Listing : Output the contents of the datagrid to Excel spreadsheet And just one more detail
There's just one thing to take care of. A run-time error occurs if the DataGrid contains any controls other than the LiteralControl. This means that enabling Sorting, Paging or adding Template Columnns or Button columns to the datagrid can cause an error. There are several approaches to workaround this limitation. We will remove all the non-Literal controls in the DataGrid and replace the controls with a text representation , where possible. To do so, we will make use of Reflection. instead of querying each type of control and working out a replacement.
For all controls that have a SelectedItem property, we replace the control with the literal value of the SelectedItem property of the control. This covers most lists. For all controls that have a Text property, we replace the control with the literal value of the Text property of the control. This covers TextBox, Buttons, Button Columns, TemplateColumns. We make an exception only for TableCell controls. This takes care of most of the cases and you can add more checks and balances as required. The only drawback for this generalised formula is the order of the controls within a single cell could get changed.
private void ClearControls(Control control)
{
for (int i=control.Controls.Count -1; i>=0; i--)
{
ClearControls(control.Controls[i]);
}
if (!(control is TableCell))
{
if (control.GetType().GetProperty("SelectedItem") != null)
{
LiteralControl literal = new LiteralControl();
control.Parent.Controls.Add(literal);
try
{literal.Text = (string)control.GetType().GetProperty("SelectedItem").GetValue(control,null);
}
catch
{
}
control.Parent.Controls.Remove(control);
}
else
if (control.GetType().GetProperty("Text") != null)
{
LiteralControl literal = new LiteralControl();
control.Parent.Controls.Add(literal);
literal.Text = (string)control.GetType().GetProperty("Text").GetValue(control,null);
control.Parent.Controls.Remove(control);
}
}
return;
}
Code Listing : Output the contents of the datagrid to Excel spreadsheet
In our sample web form, we connect to the Sample Pubs SQL Server database and display the data from the Employees table. The sample datagrid uses paging and a dummy Edit Column.
Complete Code Listing
<%@ Page language="C#" Debug="true" %>
<%@ Import Namespace="System.Drawing" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<script Language="C#" runat="server">
private void Button1_Click(object sender, System.EventArgs e)
{
//export to excel
Response.Clear();
Response.Buffer= true;
Response.ContentType = "application/vnd.ms-excel";
Response.Charset = "";
this.EnableViewState = false;
System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
this.ClearControls(dg);
dg.RenderControl(oHtmlTextWriter);
Response.Write(oStringWriter.ToString());
Response.End();
}
private void Page_Load(object sender, System.EventArgs e)
{
if (!IsPostBack)
{
SqlConnection conn = new SqlConnection ("data source=(local);initial catalog=Northwind;Pwd=p@ssw0rd;User
ID=sa");
SqlCommand cmd = new SqlCommand ("Select LastName, FirstName, Title, TitleOfCourtesy, BirthDate, HireDate,
Address, City, Region, PostalCode, Country from Employees", conn);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
dg.DataSource = ds.Tables[0];
dg.DataBind();
}
}
private void ClearControls(Control control)
{
for (int i=control.Controls.Count -1; i>=0; i--)
{
ClearControls(control.Controls[i]);
}
if (!(control is TableCell))
{
if (control.GetType().GetProperty("SelectedItem") != null)
{
LiteralControl literal = new LiteralControl();
control.Parent.Controls.Add(literal);
try
{
literal.Text = (string)control.GetType().GetProperty("SelectedItem").GetValuecontrol,null);
}
catch
{
}
control.Parent.Controls.Remove(control);
}
else
if (control.GetType().GetProperty("Text") != null)
{
LiteralControl literal = new LiteralControl();
control.Parent.Controls.Add(literal);
literal.Text = (string)control.GetType().GetProperty("Text").GetValue(control,null);
control.Parent.Controls.Remove(control);
}
}
return;
}
</script>
<html>
<body leftmargin="0" topmargin="0" marginwidth="0" marginheight="0">
<form id="frm" runat="server">
<asp:Button id="Button1" runat="server" Text="Export to Excel"
OnClick="Button1_Click"></asp:Button><BR>
<asp:Datagrid id="dg" runat="server" AutoGenerateColumns="True"
AllowSorting="true" AllowPaging="true"CellPadding="3" PageSize=3>
<columns>
<asp:TemplateColumn>
<ItemTemplate>
<asp:LinkButton runat="server" CommandName="Edit"CausesValidation="false" ID="btnView"Text="Edit"/>
</ItemTemplate>
</asp:TemplateColumn>
</columns>
</asp:datagrid>
<BR>
</form>
</body>
</html>
Note that you will need to have Excel 97 or later installed on the client. You can also add extra code for formatting the excel output. NOTE: This article is purely for educational purpose. This article should not be construed as a best practices white paper. This article is entirely original, unless specified. Any resemblance to other material is an un-intentional coincidence and should not be misconstrued as malicious, slanderous, or any anything else hereof.