In this article I will explain with an example, how to save GridView data to Excel sheet on servers’ disk in ASP.Net using C# and VB.Net.
 
 

HTML Markup

The HTML Markup consists of following control:
GridView – For displaying data.
Button – For saving GridView data to Excel sheet.
The Button has been assigned with an OnClick event handler.
<asp:GridView ID="gvCustomers" runat="server" AutoGenerateColumns="false"> 
    <Columns>
        <asp:BoundField DataField="Customer Id" HeaderText="Customer Id" />
        <asp:BoundField DataField="Name" HeaderText="Name" />
        <asp:BoundField DataField="Country" HeaderText="Country" />
    </Columns>
</asp:GridView>
<br />
<asp:Button ID="btnExportExcel" runat="server" Text="Export" OnClick="ExportToExcel" /> 
 
 

Namespaces

You will need to import the following namespaces.
C#
using System.IO;
using System.Data;
 
VB.Net
Imports System.IO
Imports System.Data
 
 

Binding GridView with Dynamic DataTable using C# and VB.Net

Inside the Page_Load event handler, the GridView is populated with dynamic DataTable.
Note: For more details on how to use dynamic DataTable, please refer my article Dynamically create DataTable and bind to GridView in ASP.Net.
 
C#
protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        DataTable dt =  new DataTable();
        dt.Columns.AddRange(new DataColumn[3] {
                            new DataColumn("Customer Id"),
                            new DataColumn("Name"),
                            new DataColumn("Country") });
        dt.Rows.Add("1", "John Hammond", "United States");
        dt.Rows.Add("2", "Mudassar Khan", "India");
        dt.Rows.Add("3", "Suzanne Mathews", "France");
        dt.Rows.Add("4", "Robert Schidner", "Russia");
        gvCustomers.DataSource = dt;
        gvCustomers.DataBind();
    }
}
 
VB.Net
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not Me.IsPostBack Then
        Dim dt As DataTable = New DataTable()
        dt.Columns.AddRange(New DataColumn(2) {
                            New DataColumn("Customer Id"),
                            New DataColumn("Name"),
                            New DataColumn("Country")})
        dt.Rows.Add("1", "John Hammond", "United States")
        dt.Rows.Add("2", "Mudassar Khan", "India")
        dt.Rows.Add("3", "Suzanne Mathews", "France")
        dt.Rows.Add("4", "Robert Schidner", "Russia")
        gvCustomers.DataSource = dt
        gvCustomers.DataBind()
    End If
End Sub
 
 

Saving GridView data to Excel sheet on Server’s Disk using C# and VB.Net

When Export Button is clicked, first a check is performed whether the Folder (Directory) named Files exists or not if it does not exists then it is created.
The StringWriter and HtmlTextWriter class objects are created.
Then, the StreamWriter class object is created and AppendText method is called inside which the path of the Folder where the Excel file will be saved along the Excel file name are passed as parameter.
Finally, the RenderControl method of GridView is called and StringWriter object is written to the StreamWriter object.
C#
protected void ExportToExcel(object sender, EventArgs e)
{
    string path = Server.MapPath("~/Files/");
    if (!Directory.Exists(path))
    {
        Directory.CreateDirectory(path);
    }
    using (StringWriter sw = new StringWriter())
    {
        using (HtmlTextWriter hw = new HtmlTextWriter(sw))
        {
            StreamWriter writer = File.AppendText(path + "GridView.xls");
            gvCustomers.RenderControl(hw);
            writer.WriteLine(sw.ToString());
            writer.Close();
        }
    }
}
 
VB.Net
Protected Sub ExportToExcel(ByVal sender As Object, ByVal e As EventArgs)
    Dim path As String = Server.MapPath("~/Files/")
    If Not Directory.Exists(path) Then
        Directory.CreateDirectory(path)
    End If
    Using sw As StringWriter = New StringWriter()
        Using hw As HtmlTextWriter = New HtmlTextWriter(sw)
            Dim writer As StreamWriter = File.AppendText(path & "GridView.xls")
            gvCustomers.RenderControl(hw)
            writer.WriteLine(sw.ToString())
            writer.Close()
        End Using
    End Using
End Sub
 
 

Screenshots

The Form

Save GridView to Excel Sheet on server's disk using ASP.Net
 

The Excel File

Save GridView to Excel Sheet on server's disk using ASP.Net
 
 

Downloads

Download Code