Tag: ASP

.Net, C#, HTML, Office, Programming, VB, Web

Exporting asp:GridView Results To A Microsoft Excel Spreadsheet in VB/C#

Jared Meredith

I have received this requirement on more than one occasion so I thought it would benefit others if I posted these snippets. So here we go, let’s export a gridview as an excel file.

For starters let’s add a couple controls to the front-end aspx page:

 
<asp:Button ID="btnExport" runat="server" Text="Export Results To Excel" /> &amp;nbsp;&amp;nbsp;<br /><br />

<asp:GridView ID="grdSearch" runat="server" CellPadding="3" BackColor="White" BorderColor="#CCCCCC" BorderStyle="None" BorderWidth="1px">
 <FooterStyle BackColor="White" ForeColor="#000066" />
 <HeaderStyle BackColor="#006699" Font-Bold="True" ForeColor="White" />
 <PagerStyle BackColor="White" ForeColor="#000066" HorizontalAlign="Left" />
 <RowStyle ForeColor="#000066" />
 <SelectedRowStyle BackColor="#669999" Font-Bold="True" ForeColor="White" />
 <SortedAscendingCellStyle BackColor="#F1F1F1" />
 <SortedAscendingHeaderStyle BackColor="#007DBB" />
 <SortedDescendingCellStyle BackColor="#CAC9C9" />
 <SortedDescendingHeaderStyle BackColor="#00547E" />
 </asp:GridView>

I’m going to assume you know how to wire in your gridview to return results.

With that assumption in place here is the click event that performs the export (in VB):

You will need: Imports System.IO

Protected Sub btnExport_Click(sender As Object, e As EventArgs) Handles btnExport.Click
        Try
            Response.Clear()
            Response.Buffer = True
            Response.ClearContent()
            Response.ClearHeaders()
            Response.Charset = ""
            Dim FileName As String = "filename" + DateTime.Now + ".xls"
            Dim strwritter As New StringWriter()
            Dim htmltextwrtter As New HtmlTextWriter(strwritter)
            Response.Cache.SetCacheability(HttpCacheability.NoCache)
            Response.ContentType = "application/vnd.ms-excel"
            Response.AddHeader("Content-Disposition", Convert.ToString("attachment;filename=") &amp; FileName)
            grdSearch.GridLines = GridLines.Both
            grdSearch.HeaderStyle.Font.Bold = True
            grdSearch.RenderControl(htmltextwrtter)
            Response.Write(strwritter.ToString())
            Response.[End]()
        Catch ex As Exception
            ' Do something important here if you expect strange results
        End Try
    End Sub

Now in C#:

You will need: using System.IO;

try {
	Response.Clear();
	Response.Buffer = true;
	Response.ClearContent();
	Response.ClearHeaders();
	Response.Charset = "";
	string FileName = "filename" + DateTime.Now + ".xls";
	StringWriter strwritter = new StringWriter();
	HtmlTextWriter htmltextwrtter = new HtmlTextWriter(strwritter);
	Response.Cache.SetCacheability(HttpCacheability.NoCache);
	Response.ContentType = "application/vnd.ms-excel";
	Response.AddHeader("Content-Disposition", Convert.ToString("attachment;filename=") + FileName);
	grdSearch.GridLines = GridLines.Both;
	grdSearch.HeaderStyle.Font.Bold = true;
	grdSearch.RenderControl(htmltextwrtter);
	Response.Write(strwritter.ToString());
	Response.End();
} catch (Exception ex) {
	// Do something important here if you expect strange results
}

I realize you may not need some of the formatting that I used in this example so remove the Gridview related property assignments in the export snippet. Also, depending on how you format your gridview on the aspx page will dictate some of the formatting you have on the spreadsheet. Hope this helps, questions are welcome.