Export GridView to Excel (not working)

Question

I've spent the last two days trying to get some bloody data to export to Excel. After much research I determined that the best and most common way is using HttpResponse headers as shown in my code below. After stepping through countless times in debug mode, I have confirmed that the data is in fact there and both filtered and sorted the way I want it. However, it does not download as an Excel file, or do anything at all for that matter.

I suspect this may have something to do with my UpdatePanel or perhaps the ImageButton not posting back properly, but I'm not sure. What am I doing wrong? Please help me to debug this issue. I will be eternally grateful. Thank you. :)

Markup

<asp:UpdatePanel ID="statusUpdatePanel" runat="server" UpdateMode="Conditional">
<Triggers>
    <asp:AsyncPostBackTrigger ControlID="btnExportXLS" EventName="Click" />
</Triggers>
<ContentTemplate>
    <asp:GridView ID="GridView1" runat="server" AllowPaging="True" PageSize="10"
        AllowSorting="True" DataSourceID="GridView1SDS" DataKeyNames="ID">
    </asp:GridView>
    <span><asp:ImageButton ID="btnExportXLS" runat="server" /></span>
</ContentTemplate>
</asp:UpdatePanel>

Codebehind

Protected Sub ExportToExcel() Handles btnExportXLS.Click
    Dim dt As New DataTable()
    Dim da As New SqlDataAdapter(SelectCommand, ConnectionString)

    da.Fill(dt)

    Dim gv As New GridView()
    gv.DataSource = dt
    gv.DataBind()

    Dim sw As New IO.StringWriter()
    Dim hw As New System.Web.UI.HtmlTextWriter(sw)
    Response.ContentType = "application/vnd.ms-excel"
    Response.AddHeader("content-disposition", "attachment;filename=Report.xls")
    Response.Charset = String.Empty

    gv.RenderControl(hw)
    Response.Write(sw.ToString()) 'sw is a valid html table, but no Excel file downloads. :(
    Response.End()
End Sub

Answer 1

1. I'm missing a Response.Clear at the beginning.
2. You are calling GridView.RenderControl(htmlTextWriter), hence the page raises an exception that a Server-Control was rendered outside of a Form. Try to execute it in the debugger and i'm fairly sure that you'll see that exception.

You could avoid this exception by overriding VerifyRenderingInServerForm

Public Overrides Sub VerifyRenderingInServerForm(control As Control)
    ' Confirms that an HtmlForm control is rendered for the specified ASP.NET '
    ' server control at run time.  '
End Sub

See here and here.

Edit: I've only just seen that you're using an UpdatePanel. Make sure that you've created a (Full-)PostBackTrigger for that button:

<asp:UpdatePanel ID="UpdGridInfo" runat="server" >
    <ContentTemplate>
        <asp:ImageButton ToolTip="export to Excel" ID="BtnExcelExport" ImageUrl="~/images/excel2007logo.png" runat="server" />
    </ContentTemplate>
    <Triggers>
        <asp:PostBackTrigger ControlID="BtnExcelExport" />
    </Triggers>
</asp:UpdatePanel>

But instead of creating a html-table that can be interpreted by excel, i would use a Excel Library like EPPlus(GPL) which i can warmly recommend.

Then it is as easy as this to create Excel-Files from a DataTable and write it to the Response:

Dim pck = New ExcelPackage()
Dim ws = pck.Workbook.Worksheets.Add("Worksheet-Name")
ws.Cells("A1").LoadFromDataTable(dt, True, OfficeOpenXml.Table.TableStyles.Medium1)
Response.Clear()
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
Response.AddHeader("content-disposition", "attachment;  filename=ExcelFileName.xlsx")
Response.BinaryWrite(pck.GetAsByteArray())
Response.End()

Here is another example: http://epplus.codeplex.com/wikipage?title=WebapplicationExample