Simple export from ASP.NET GridView into Excel VB.NET

Question

I have a GridView that I need to export into Excel (by button event) and I'm using Visual Studio and vb.net.

I never tried this before and I'm kinda clueless, is there a simple way to do this? I don't think I need any complications at the moment, just a simple export of the GridView information.

Also I already got a connection between the GridView and my database. I tried adding a working Excel export from other project but I still miss something .

   Public Overrides Sub VerifyRenderingInServerForm(ByVal control As Control)
        ' Verifies that the control is rendered 

    End Sub

    Protected Sub exportExelBtn_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles exportExelBtn.Click
        Dim errorCheck As Integer = 0
        Dim popupscript As String = ""

        If approvalGrid.Rows.Count > 0 Then
            Try

                Response.ClearContent()
                Response.Buffer = True
                Response.AddHeader("content-disposition", String.Format("attachment; filename={0}", "TestPage.xls"))
                Response.ContentEncoding = Encoding.UTF8
                Response.ContentType = "application/ms-excel"
                ' Dim sw As New stringwriter()
                Dim tw As New IO.StringWriter()
                Dim htw As New HtmlTextWriter(tw)
                approvalGrid.RenderControl(htw)
                Response.Write(tw.ToString())
                Response.[End]()

            Catch ex As Exception
                errorCheck = 1
            End Try
        Else
            errorCheck = 1
        End If
        If errorCheck = 1 Then
            'a pop up error messege feature
            popupscript = "<script language='javascript'>" + "alert(" + Chr(34) + "There was an error in exporting to exel, please make sure there is a grid to export!" + Chr(34) + ");</script>"
        End If
        Page.ClientScript.RegisterStartupScript(Me.GetType(), "PopUpWindow", popupscript, False)
   End Sub

The problem is that the file that it creates upon click says it's not Excel format and when I agree to open it I do see the GridView information as I wanted but I also see a lot of extra info in the form of buttons calanders and other stuff from my page, how can I prevent the export of those other stuff?

You can NOT export the GridView, what you CAN export is the same data (that gridview renders) in a specific format (csv) with comma, or tab delimited, and export as text and line by line, and then the excel reads that and load it on a sheet. — Aristos
– Aristos, Commented May 29, 2014 at 9:52

Tasos K. · Accepted Answer · 2014-05-29 10:34:53Z

2

Please Try Below code

Public Overrides Sub VerifyRenderingInServerForm(control As Control)
        ' Verifies that the control is rendered 

End Sub

Protected Sub btnExport_Click(sender As Object, e As EventArgs)
    If gridview.Rows.Count > 0 Then
        Try
            gridview.Columns(0).Visible = False
            Response.ClearContent()
            Response.Buffer = True
            Response.AddHeader("content-disposition", String.Format("attachment; filename={0}", "TestPage.xls"))
            Response.ContentEncoding = Encoding.UTF8
            Response.ContentType = "application/ms-excel"
            Dim sw As New StringWriter()
            Dim htw As New HtmlTextWriter(sw)
            gridview.RenderControl(htw)
            Response.Write(sw.ToString())
            Response.[End]()

        Catch ex As Exception
        Finally
            gridview.Columns(0).Visible = True
        End Try
    End If
End Sub

edited May 29, 2014 at 10:34

Tasos K.

8,1057 gold badges44 silver badges68 bronze badges

answered May 29, 2014 at 9:59

Deepak Joshi

1,0668 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

kresa Over a year ago

Hey thanks for the response i tried this code but it aint recognizing StringWriter() so i used Dim tw As New IO.StringWriter() Now it works but it takes many other things beside the gridview information , like buttons calander textboxes and more ^^

Deepak Joshi Over a year ago

ok so in your export you are getting all the column which are not required in excel right so. you have make those column visible false and after catch you have to add finally block you have to make column again visible true.

Mohammad Mirzaie · Accepted Answer · 2020-05-15 07:29:05Z

I have written above code to export gridview to excel file, it exports successfully but there are some content in gridview in Persian language which is shown unreadable in exported excel file. the code I have written is as below:

 Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    If GridView1.Rows.Count > 0 Then
        Response.ClearContent()
        Response.Buffer = True
        Response.AddHeader("content-disposition", String.Format("attachment; filename={0}", "IncentiveReport.xls"))
        Response.ContentEncoding = Encoding.UTF8
        Response.ContentType = "application/ms-excel"
        Dim sw As New IO.StringWriter()
        Dim htw As New HtmlTextWriter(sw)
        GridView1.RenderControl(htw)
        Response.Write(sw.ToString())
        Response.End()
    End If
End Sub
Public Overrides Sub VerifyRenderingInServerForm(control As Control)
    ' Verifies that the control is rendered 
End Sub

Collectives™ on Stack Overflow

Simple export from ASP.NET GridView into Excel VB.NET

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related