Export database Table to Excel, 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.
Download source code here
