Hi hsku6482,

First convert the SqlDataSource to DataTable. Then export the DataTable to Excel.

For export DataTable to excel refer below article.

Export DataTable to Excel in ASP.Net using C# and VB.Net

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:TextBox runat="server" ID="txtCountry" />
<asp:Button Text="Search" runat="server" />
<asp:GridView runat="server" ID="gvCustomers" DataSourceID="SqlDataSource1"></asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
    ConnectionString="<%$ ConnectionStrings:constr %>"
    SelectCommand="SELECT DISTINCT CustomerID,ContactName,City,Country FROM Customers WHERE Country = @Country">
    <SelectParameters>
        <asp:ControlParameter ControlID="txtCountry" Name="Country" PropertyName="Text" Type="String" />
    </SelectParameters>
</asp:SqlDataSource>
<asp:Button Text="Export" runat="server" OnClick="OnExport" />

Namespaces

C#

using System.Data;
using System.IO;
using ClosedXML.Excel;

VB.Net

Imports System.Data
Imports System.IO
Imports ClosedXML.Excel

Code

C#

protected void OnExport(object sender, EventArgs e)
{
    DataView dv = (DataView)SqlDataSource1.Select(DataSourceSelectArguments.Empty);
    DataTable dt = dv.ToTable();
    using (XLWorkbook wb = new XLWorkbook())
    {
        wb.Worksheets.Add(dt, "Customers");

        Response.Clear();
        Response.Buffer = true;
        Response.Charset = "";
        Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
        Response.AddHeader("content-disposition", "attachment;filename=SqlDataSourceExport.xlsx");
        using (MemoryStream MyMemoryStream = new MemoryStream())
        {
            wb.SaveAs(MyMemoryStream);
            MyMemoryStream.WriteTo(Response.OutputStream);
            Response.Flush();
            Response.End();
        }
    }
}

VB.Net

Protected Sub OnExport(ByVal sender As Object, ByVal e As EventArgs)
    Dim dv As DataView = CType(SqlDataSource1.Select(DataSourceSelectArguments.Empty), DataView)
    Dim dt As DataTable = dv.ToTable()
    Using wb As XLWorkbook = New XLWorkbook()
        wb.Worksheets.Add(dt, "Customers")
        Response.Clear()
        Response.Buffer = True
        Response.Charset = ""
        Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
        Response.AddHeader("content-disposition", "attachment;filename=SqlDataSourceExport.xlsx")
        Using MyMemoryStream As MemoryStream = New MemoryStream()
            wb.SaveAs(MyMemoryStream)
            MyMemoryStream.WriteTo(Response.OutputStream)
            Response.Flush()
            Response.End()
        End Using
    End Using
End Sub