Remove (Hide) columns while exporting SqlDataSource data to Excel using C# and VB.Net in ASP.Net

Hi hsku6482,

Since you are not defining the columns for GridView.

You need to first convert the SqlDataSource to DataTable.

Then Remove the columns from the DataTable.

Then assign the DataTable as DataSource to GridView and export.

Check this example. Now please take its reference and correct your code.

Database

For this example I have used of Northwind database that you can download using the link given below.

Download Northwind Database

HTML

<asp:GridView runat="server" ID="gvCustomers" DataSourceID="SqlDataSource1"></asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:constr %>"
    SelectCommand="SELECT TOP 5 CustomerID,ContactName,City,Country FROM Customers"></asp:SqlDataSource>
<asp:Button Text="Download" runat="server" OnClick="OnDownload" />

Namespaces

C#

using System.Data;
using System.IO;

VB.Net

Imports System.Data
Imports System.IO

Code

C#

protected void OnDownload(object sender, EventArgs e)
{
    GridView GridView1 = new GridView();
    GridView1.AllowPaging = false;
    GridView1.AllowSorting = false;
    GridView1.Style.Clear();
    GridView1.CellPadding = 0;
    GridView1.CellSpacing = 0;
    GridView1.GridLines = GridLines.None;
    GridView1.BorderStyle = BorderStyle.None;
    GridView1.BorderWidth = Unit.Pixel(0);
    GridView1.AlternatingRowStyle.BorderStyle = BorderStyle.None;

    // Convert SqlDataSource to DataTable
    DataTable dt = ((DataView)SqlDataSource1.Select(DataSourceSelectArguments.Empty)).ToTable();
    // Removing the column to hide in excel
    dt.Columns.RemoveAt(0);
    GridView1.DataSource = dt;
    GridView1.DataBind();
    // Clear the response 
    Response.Clear();
    // Set the type and filename 
    Response.AddHeader("content-disposition", "attachment;filename=GridView.xls");
    Response.Charset = "";
    Response.ContentType = "application/vnd.ms-excel";
    // Add the HTML from the GridView to a StringWriter so we can write it out later 
    StringWriter sw = new StringWriter();
    HtmlTextWriter hw = new HtmlTextWriter(sw);
    GridView1.RenderControl(hw);
    // Write out the data 
    Response.Write(sw.ToString());
    Response.End();
}

VB.Net

Protected Sub OnDownload(ByVal sender As Object, ByVal e As EventArgs)
    Dim GridView1 As GridView = New GridView()
    GridView1.AllowPaging = False
    GridView1.AllowSorting = False
    GridView1.Style.Clear()
    GridView1.CellPadding = 0
    GridView1.CellSpacing = 0
    GridView1.GridLines = GridLines.None
    GridView1.BorderStyle = BorderStyle.None
    GridView1.BorderWidth = Unit.Pixel(0)
    GridView1.AlternatingRowStyle.BorderStyle = BorderStyle.None

    ' Convert SqlDataSource to DataTable
    Dim dt As DataTable = CType(SqlDataSource1.Select(DataSourceSelectArguments.Empty), DataView).ToTable()
    ' Removing the column to hide in excel
    dt.Columns.RemoveAt(0)
    GridView1.DataSource = dt
    GridView1.DataBind()
    ' Clear the response
    Response.Clear()
    ' Set the type and filename
    Response.AddHeader("content-disposition", "attachment;filename=GridView.xls")
    Response.Charset = ""
    Response.ContentType = "application/vnd.ms-excel"
    ' Add the HTML from the GridView to a StringWriter so we can write it out later 
    Dim sw As StringWriter = New StringWriter()
    Dim hw As HtmlTextWriter = New HtmlTextWriter(sw)
    GridView1.RenderControl(hw)
    ' Write out the data
    Response.Write(sw.ToString())
    Response.End()
End Sub

Screenshot