Vb.net export gridview to excel

Question

I am having trouble exporting my gridview to an excel file. Everything works except when I hit the export button, the data that is supposed to be exported to an excel file displays on the web page instead. I am suspecting that this is a problem with Response.ContentType and the file name that I specified. However, I already went through multiple documentations and examples and it doesn't seem like those two are the root problems in my case. Can Anyone help?

<asp:Button ID="btnExport" runat="server" Text="Export To MS Excel File" CssClass="btnMain" OnClick="btnExport_Click" />    




Protected Sub btnExport_Click(ByVal sender As Object, ByVal e As System.EventArgs) 

        Dim filename As String = "PerformanceEval Status Report" & Date.Now.Year.ToString & "-" & _
                                    formatNumberTo2Digits(DateTime.Now.Month) & "-" & formatNumberTo2Digits(DateTime.Now.Day) & ".xls"

        With Page.Response

            .Clear()
            .Buffer = True
            .AddHeader("content_disposition", "attachment;filename" & filename)
            .Charset = ""

            '--to open the Excel file without saving then comment out the line below
            '.Cache.SetCacheability(HttpCacheability.NoCache)

            '.ContentType = "application/vnd.xls"   'data appears on web page 
            '.ContentType = "application/vnd.ms-excel"  'try to download .aspx document NOT .xml
            .ContentType = "application/ms-excel"  'data appears on web page 

        End With


        Dim strWriter As New System.IO.StringWriter
        Dim htmlWriter As System.Web.UI.HtmlTextWriter = New HtmlTextWriter(strWriter)

        Dim gvexport As GridView = New GridView
        gvexport.AutoGenerateColumns = False
        gvexport.CssClass = "gridData"
        gvexport.GridLines = GridLines.Horizontal
        gvexport.HeaderStyle.CssClass = "gridHeader"
        gvexport.RowStyle.CssClass = "gridRow"
        gvexport.AlternatingRowStyle.CssClass = "gridRow_Alt"
        gvexport.FooterStyle.CssClass = "gridFooter"

        Dim dv As DataView = New DataView(Me.ds.Tables(0))  'default is sorted by last name
        'Dim dv As DataView = New DataView(Me.ds.Tables(0))
        dv.RowFilter = GetFilter()

        '-- add columns for report
        addGVcolumn("Employee Name", "EmployeeName", gvexport, -1, HorizontalAlign.Left)
        addGVcolumn("Employee ID", "SID", gvexport, -1, HorizontalAlign.Left)
        addGVcolumn("Email", "WorkEmail", gvexport, -1, HorizontalAlign.Left)
        addGVcolumn("StatusID", "StatusID", gvexport, 50, HorizontalAlign.Center)

        gvexport.DataSource = dv
        gvexport.DataBind()

        gvexport.RenderControl(htmlWriter)

        Response.Output.Write(strWriter.ToString())

        Response.Flush()
        Response.End()

Again, I am able to generate the content but is not able to make my data exports into excel. Thanks a lot!

You have a c# answer here gridview data export to excel in asp.net — huMpty duMpty

IvanH IvanH · Accepted Answer · 2013-09-20T17:32:47

You are giving the wrong header:

.AddHeader "content-disposition", "attachment; filename="& filename &";"

Vb.net export gridview to excel

1 Answers