How to Export SQL Query to Excel file using VB.NET?

Programming
May 14, 2016
5 (1 votes)
How to Export SQL Query to Excel file using VB.NET?
5 out of 5 based on 1 user ratings
Publish with us | Our Do-Follow links are lifelong

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

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 (SqlDataAdp). Then assigning data from Data Adapter to Data Table. Using a variable tempTable I am creating the tabular format of data from data table. To do this here I used for loops for data table columns & 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

Tags:, , ,