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 frm As HtmlForm = New HtmlForm()
frm.Controls.Add(gv)
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())
Response.End()
End Sub
