title: Export to Excel from ASP.NET
body: <pre><code>I have a webpage with one textbox (search) and many buttons. Each buttons is for different queries (using `page.aspx.vb` to the code for each buttons) that are pulling from SQL Server using stored procedures. The textbox is the search field for any of the queries that the user click on. There is also an ‘Export to Excel’ button.
  
  The `page.aspx` code does not have `BoundField` in the markup, just a `GridView`. Because each of the queries headings different and is pulling data from different tables.
  
  Could someone tell me how to get the code for export to Excel without the `BoundField` is need in `page.aspx`?
  
  This is what I have. The only thing I cane't get it to work, is the Export to Excel.
  </code></pre>
  
  <p>This is the html page</p>
  
  <pre><code>&lt;%@ Page Language="VB" AutoEventWireup="false" CodeFile="ExportWithOutBoundField.aspx.vb"   Inherits="zTestExport_ExportWithOutBoundField" %&gt;
  &lt;!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"&gt;
  &lt;html xmlns="http://www.w3.org/1999/xhtml"&gt;
  &lt;head runat="server"&gt;
      &lt;title&gt;&lt;/title&gt;
  &lt;/head&gt;
  &lt;body&gt;
      &lt;form id="form1" runat="server"&gt;
      &lt;div&gt;
    &lt;p&gt;
    Export to Excel
    &lt;/p&gt;
    &lt;p&gt;
        &lt;asp:TextBox ID="txtMaterialNumber" runat="server"&gt;&lt;/asp:TextBox&gt;
        &lt;asp:Button ID="btnGetData" runat="server" Text="GetData"/&gt;
    &lt;/p&gt;  
    &lt;p&gt;
      &lt;asp:Button ID="btnExport" runat="server" Text="Excel"/&gt;
    &lt;/p&gt;
    &lt;p&gt;&lt;asp:Label ID="lblMaterialCount" runat="server" Text="(0: Row Count)"&gt;&lt;/asp:Label&gt;&lt;/p&gt;
    &lt;p&gt;     &lt;asp:GridView ID="ExportToExcel" runat="server"&gt;&lt;/asp:GridView&gt;&lt;/p&gt;
      &lt;/div&gt;
      &lt;/form&gt;
   &lt;/body&gt;
   &lt;/html&gt;
  </code></pre>
  
  <p>This is the code behind that pull data from Stored Procedures in the SQL Server and put the data in GridView</p>
  
  <pre><code>Imports System.Data
  Imports System.Data.DataSet
  Imports System.IO
  Imports System.Data.SqlClient
  Partial Class zTestExport_ExportWithOutBoundField
      Inherits System.Web.UI.Page
      Protected Sub btnGetData_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGetData.Click
          Dim gv As DataSet
          Dim STREWEB2connection As SqlConnection
          Dim ATOMSserver As SqlDataAdapter
          'Create a connection to the SQL Server.
          STREWEB2connection = New SqlConnection("")
          'Create a DataAdapter, and then provide the name of the stored procedure.
          ATOMSserver = New SqlDataAdapter("GetCATO", STREWEB2connection)
          'Set the command type as StoredProcedure.
          ATOMSserver.SelectCommand.CommandType = CommandType.StoredProcedure
          'Create and add a parameter to Parameters collection for the stored procedure.
          ATOMSserver.SelectCommand.Parameters.Add(New SqlParameter("@Material", SqlDbType.VarChar, 40))
          'Assign the search value to the parameter.
          ATOMSserver.SelectCommand.Parameters("@Material").Value = Trim(txtMaterialNumber.Text)
          'Create and add an output parameter to Parameters collection. 
          ATOMSserver.SelectCommand.Parameters.Add(New SqlParameter("@RowCount", SqlDbType.Int, 4))
          'Set the direction for the parameter. This parameter returns the Rows returned.
          ATOMSserver.SelectCommand.Parameters("@RowCount").Direction = ParameterDirection.Output
          gv = New DataSet() 'Create a new DataSet to hold the records.
          ATOMSserver.Fill(gv, "GetCATO") 'Fill the DataSet with the rows returned.
          'Get the number of rows returned, and then assign it to the Label control.
          'lblRowCount.Text = DS.Tables(0).Rows.Count().ToString() &amp; " Rows Found!"
          lblMaterialCount.Text = ATOMSserver.SelectCommand.Parameters(1).Value &amp; " Rows Found! (Material)"
          'Set the data source for the DataGrid as the DataSet that holds the rows.
          ExportToExcel.DataSource = gv.Tables("GetCATO").DefaultView
          'Bind the DataSet to the DataGrid. 
          'NOTE: If you do not call this method, the DataGrid is not displayed!
          ExportToExcel.DataBind()
          ATOMSserver.Dispose() 'Dispose of the DataAdapter.
          STREWEB2connection.Close() 'Close the connection.
      End Sub
  </code></pre>
  
  <p>I can't get this part to work. Is the one that I need to export into Excel</p>
  
  <pre><code>    Protected Sub btnExport_Click(sender As Object, e As System.EventArgs) Handles btnExport.Click
          Dim ad As New DataSet()
          Dim dt As DataTable
          'dt = ad.Read()
          Dim reader As SqlDataReader
          Dim attachment As String = "attachment; filename=3DUSurvey.xls"
          Response.ClearContent()
          Response.AddHeader("content-disposition", attachment)
          Response.ContentType = "application/vnd.ms-excel"
          Dim tab As String = ""
          For Each dc As DataColumn In dt.Columns
              Response.Write(tab + dc.ColumnName)
              tab = vbTab
          Next
          Response.Write(vbLf)
          Dim i As Integer
          For Each dr As DataRow In dt.Rows
              tab = ""
              For i = 0 To dt.Columns.Count - 1
                  Response.Write(tab &amp; dr(i).ToString())
                  tab = vbTab
              Next
              Response.Write(vbLf)
          Next
          Response.[End]()
          'export to excel
      End Sub
  End Class
  </code></pre>