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](http://stackoverflow.com/questions/15832339/gridview-data-export-to-excel-in-asp-net) — huMpty duMpty, Sep 20 '13 at 17:17

score 0 · Accepted Answer · answered Sep 20 '13 at 17:32

0

You are giving the wrong header:

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

answered Sep 20 '13 at 17:32

IvanH

5,039
14
60
81

Yayyy! Thank you so much! This totally makes my day!' For the content type, I need to change it to "application/vnd.ms-excel in order to make it function! – CYC0616 Sep 20 '13 at 17:47
Why do I need that semicolon? How come this example http://www.webcodeexpert.com/2013/06/how-to-bind-and-export-gridview-data-to_21.html#.Ujx4zoakrCc doesn't show it? – CYC0616 Sep 20 '13 at 18:00
Also, how come the file name that I specified did not appear to be the file name of the excel. The file name is admindefault.xls which is the .aspx page of where this user control lives on. Any idea? Thank you very much! – CYC0616 Sep 20 '13 at 18:06
@CYC0616 Final semicolon is probably not necessary. It is a header field separator. I took it from an example. After your question I looked to RFC 2183. By my experience the filename depends on the browser. Some browsers give to file the name of page, some the name and I even saw a default name chosen by a browser (opera 11?). – IvanH Sep 20 '13 at 18:27

Vb.net export gridview to excel

1 Answers1