How to export Data from SQL tables to a Excel file using VB.NET?

Programming
May 14, 2016
5 (1 votes)
How to export Data from SQL tables to a Excel file using VB.NET? How to export Data from SQL tables to a Excel file using VB.NET?
2 5 87
High-Speed Hosting | best CPM Ad Network | Website Widgets

In application development generally during report generation Customer wants to add a button which can create an excel file from the filtered report grid. Recently during I worked for a filter page where I wrote a function (Export2Excel) related to this export excel. The environment where I wrote the function is in VB.NET. About the function Export2Excel() it accepts 2 parameters. SQL Query to extract data from the database and Name of the Excel file which you want to create. Look at the function below.

Under .NET Framework normally to fetch data from a database to code-behind we use SqlDataAdapter. SqlDataAdapter accepts 2 parameters. First one is SQL Query and database Connection String. Using popular data adapter Fill method here I am binding data from adapter to data table.

Export SQL Query to Excel Function

Private Sub Export2Excel(ByVal SqlQry As String, ByVal FileName As String)
Response.ClearContent()
Response.ContentType = "Application/vnd.ms-excel"
Response.AddHeader("Content-Disposition", "attachment; filename=" & FileName & "")
Response.Flush()
Dim SqlDataAdp As New SqlDataAdapter(SqlQry, sqlConn)
Dim DT As New DataTable
SqlDataAdp.Fill(DT)
sqlConn.Open()
Dim tempTable As String = String.Empty
Dim DTCol As DataColumn
Dim DTRow As DataRow
tempTable = "<table cellpadding='2' cellspacing='2' border='1' width='100%'>"
tempTable = tempTable &amp; "<tr>"
For Each DTCol In DT.Columns
tempTable = tempTable &amp; "<td valign='top' style='font-weight:bold'>"
tempTable = tempTable &amp; DTCol.ColumnName.ToString()
tempTable = tempTable &amp; "</td>"
Next
tempTable = tempTable &amp; "</tr>"
For Each DTRow In DT.Rows
tempTable = tempTable &amp; "<tr>"
For i = 0 To DT.Columns.Count - 1
tempTable = tempTable &amp; "<td valign='top' align='left'>"
tempTable = tempTable &amp; DTRow(i).ToString()
tempTable = tempTable &amp; "</td>"
Next
tempTable = tempTable &amp; "</tr>"
Next
tempTable = tempTable &amp; "</table>"
Response.Write(tempTable)
Response.End()
End Sub

In the above function I am fetching SQL data using a Data Adapter. Named as SqlDataAdp. Then assigning data from Data Adapter to a Data Table. Using a variable tempTable I am creating the tabular structure of data from data table. To do this here I used for loops for data table columns and rows. To run the function I am calling it from the button click event of “Export to Excel” button.

btnExportToExcel Click

Protected Sub btnExportToExcel_Click(ByVal sender As Object, ByVal e AsSystem.EventArgs) Handles btnExportToExcel.Click
Export2Excel("SELECT * FROM EmployeeTable", "Employees.xls")
End Sub

Set the connectiong String in your web.config file as below.

<connectionStrings>
    <add name="sqlConn"
         connectionString="Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\gadgetDatabase.mdf;Integrated Security=True" />
</connectionStrings>

You must noticed here I added below 3 lines inside my button click event. The first line Response.ClearContent() helps to create a new excel file. If there is a existing file on same name it helps to clear the file before append a new table.

Response.ClearContent()
Response.ContentType = "Application/vnd.ms-excel"
Response.AddHeader("Content-Disposition", "attachment; filename=" &amp; FileName &amp; "")

Response.ContentType = “Application/vnd.ms-excel” this line helps to generate the file type excel. Without using this line of Code its not possible to Create an excel file using VB.NET. AddHeader helps to write file header information.

Tags:, , ,