It is a simple task to export data from an ASP.NET DataGrid to an Excel spreadsheet. In this code snippet, we will transfer the DataGrid output to an Excel spreadsheet format by manipulating the MIME type of the HTTP response. The content for the Excel spreadsheet is obtained by using the RenderControl method of a web server control. This method writes its output to an HtmlTextWriter object, which is then written out to the HTTP response stream. We can manipulate this process so that we can apply a few formatting expressions such as inserting a line, formatting text to bold, and extending a column's span.

Scenario:

On the web page we present a DropDownList control from which the user can select an employee's name. When the Submit button is clicked, the selected employee's name is displayed with a Label control and the employee's details displayed with a DataGrid control..

When the Export button is clicked, the same DataGrid control is used to prepare an Excel spreadsheet, where formatting is done through code. In order to return the Excel spreadsheet with an appropriate filename and type, we make use of the AddHeader method of the HttpResponse object.

Listing 1: Web Form Code-behind

Sub Bind()
    Dim strName As String = DropDownList1.SelectedItem.Text
    LblEmpName.Text = strName
    OleDbCommand1.CommandText = _
        "SELECT * FROM tbEmployee WHERE EmployeeName Like '%" & strName & "%'"
    Dim adp As New OleDb.OleDbDataAdapter(OleDbCommand1)
    Dim ds As New DataSet
    adp.Fill(ds)
    DataGrid1.DataSource = ds
    DataGrid1.DataBind()
End Sub

Private Sub Submit_Click(sender As Object, e As EventArgs) Handles Submit.Click
    Bind()
End Sub

Private Sub Export_Click(sender As Object, e As EventArgs) Handles Export.Click

    ' Fill the DataGrid
    Bind()
    
    'Provide a filename
    Response.AddHeader("content-disposition", "attachment;filename=Employee Report.xls")
    
    ' Set MIME type to Excel.
    Response.ContentType = "application/vnd.ms-excel"
    
    ' Remove the charset from the Content-Type header.
    Response.Charset = ""
    
    ' Prepare to export the DataGrid
    Dim strw As New System.IO.StringWriter
    Dim htmlw As New System.Web.UI.HtmlTextWriter(strw)
    
    ' Use the Label control to add the employee's name.
    strw.GetStringBuilder.Append("<B>")
    strw.WriteLine("Employee Report:  ")
    strw.GetStringBuilder.Append("</B>")
    strw.GetStringBuilder.Append("<br>")
    strw.GetStringBuilder.Append("<br>")
    strw.GetStringBuilder.Append("<table>")
    strw.GetStringBuilder.Append("<tr>")
    strw.GetStringBuilder.Append("<td colspan=3 bgcolor=Gainsboro align=center>")
    strw.GetStringBuilder.Append("<B>")
    LblEmpName.RenderControl(htmlw)
    strw.GetStringBuilder.Append("</B>")
    strw.GetStringBuilder.Append("</td>")
    strw.GetStringBuilder.Append("</tr>")
    
    ' Use the DataGrid control to add the employee's details
    DataGrid1.RenderControl(htmlw)
    
    ' Finish the Excel spreadsheet and send the response
    strw.GetStringBuilder.Append("</table>")
    Response.Write(strw.ToString())
    Response.End()
    
End Sub

I hope you will find this code snippet to be useful.