Export table data From Dataset to Excel in ASP.NET

This article will explain how to export database table into a Excel file by using Dataset in ASP.NET.

Here I am using the SqlDataAdapter to get data from Sql server table and StringWriter , HtmlTextWriter to write data to Excel File.

The below code shows how to Write data table data to Excel file

    Dim strSqlQuery As String, DA As SqlDataAdapter, Ds As New DataSet
        Dim conobj As SqlConnection
        Try
            conobj = New SqlConnection("uid=sa;pwd=dlog24;DataBase=emp;DataSource=local;")
            strSqlQuery = "Select * from emp1"
            DA = New SqlDataAdapter(strSqlQuery, conobj)
            DA.Fill(Ds, "phone_book")
            Dim response As HttpResponse = HttpContext.Current.Response
            response.Clear()
            response.Charset = " "
            response.ContentType = "application/vnd.ms-excel"
            response.AddHeader("Content-Disposition", "attachment;filename=Employee_info.xls;")
            Dim sw As New StringWriter
            Dim htw As New HtmlTextWriter(sw)
            Using sw
                Using htw
                    Dim dg As New GridView
                    dg.DataSource = Ds.Tables(0)
                    dg.DataBind()
                    dg.RenderControl(htw)
                    response.Write(sw.ToString())
                    response.End()
                End Using

            End Using
            htw.Dispose()
            sw.Dispose()
            DA.Dispose()
            Ds.Dispose()
            htw = Nothing
            sw = Nothing
        Catch ex As Exception
            Response.Write("unable to download the employee info")
        Finally
            DA = Nothing
            Ds = Nothing

        End Try
        

Below you can find the downloadable source code file.

In that code I uses the one Button control. In the Button control event I call the ExportToExcel method. Whenever you clicks the button it asks to save or open the excel file.If you want to save it to your disk, click save button or if you want to open click open button.