TomBecker.net

Exporting a Girdview to Excel

I needed a quick way to export either a Gridview to an Excel file. I wanted something quick that I could call on a button click. So I put the following function in a class and you can call it from anywhere. You can also specify the file name of the export if you wish. It defaults to export.xls if you don't. You can easily use this for file types of than excel by changing the MIME type, a full list can be found here.

The PrepareControlForExport sub will just remove the control and replace it with a literal so it will show up nice in your export.

Public Shared Sub Generate(ByVal Grid As GridView, Optional ByVal filename As String = "export")
    HttpContext.Current.Response.Clear()
    HttpContext.Current.Response.Buffer = True
    HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=" & filename & ".xls")
    HttpContext.Current.Response.ContentType = "application/vnd.ms-excel"
    HttpContext.Current.Response.Charset = ""

    Dim oStringWriter As System.IO.StringWriter = New System.IO.StringWriter()
    Dim oHtmlTextWriter As System.Web.UI.HtmlTextWriter = New System.Web.UI.HtmlTextWriter(oStringWriter)

    '  Create a form to contain the grid
    Dim table As Table = New Table
    table.GridLines = Grid.GridLines

    '  add the header row to the table
    If Grid.ShowHeader Then
        PrepareControlForExport(Grid.HeaderRow.Controls(0))
        table.Rows.Add(Grid.HeaderRow)
    End If

    '  add each of the data rows to the table
    For Each row As GridViewRow In Grid.Rows
        PrepareControlForExport(row)
        table.Rows.Add(row)
    Next
  
    '  render the table into the htmlwriter
    table.RenderControl(oHtmlTextWriter)
  
    HttpContext.Current.Response.Write(oStringWriter.ToString())
    HttpContext.Current.Response.End()
End Sub

 ' Replace any of the contained controls with literals
Private Shared Sub PrepareControlForExport(ByVal control As Web.UI.Control)
    Dim i As Integer = 0
    Do While (i < control.Controls.Count)
        Dim current As Web.UI.Control = control.Controls(i)
        If (TypeOf current Is LinkButton) Then
            control.Controls.Remove(current)
            control.Controls.AddAt(i, New Web.UI.LiteralControl(CType(current, LinkButton).Text))
        ElseIf (TypeOf current Is ImageButton) Then
            control.Controls.Remove(current)
            control.Controls.AddAt(i, New Web.UI.LiteralControl(CType(current, ImageButton).AlternateText))
        ElseIf (TypeOf current Is HyperLink) Then
            control.Controls.Remove(current)
            control.Controls.AddAt(i, New Web.UI.LiteralControl(CType(current, HyperLink).Text))
        ElseIf (TypeOf current Is DropDownList) Then
            control.Controls.Remove(current)
            control.Controls.AddAt(i, New Web.UI.LiteralControl(CType(current, DropDownList).SelectedItem.Text))
        ElseIf (TypeOf current Is CheckBox) Then
            control.Controls.Remove(current)
            control.Controls.AddAt(i, New Web.UI.LiteralControl(CType(current, CheckBox).Checked))
        End If

        If current.HasControls Then
            PrepareControlForExport(current)
        End If
        i = (i + 1)
    Loop
End Sub

Just pass in your gridview and filename and you are good to go. It should prompt you whether or not you want to save the file or open it.

Labels: .NET

posted by Tom Becker at 11/25/2008