How to export DataTable to Excel file using VB.NET?

Excel is very common practice to share data across Network. Recently one of my Customer wants to provide export an excel button in their products list page. What I did to implement Export DataTable to Excel functionality sharing the same here.

I added a server side button with text “Export to Excel”. Then inside this button click event I am calling a function Export2Excel. Export2Excel function accepts 2 parameters Select Query & output File Name. Inside the function first I am clearing response object. Then defining Content Type & File Header information. Then fetching data in SQLDataAdapter & Cloning the same data to DataTable. To Create table running loop over TR. According to requirement showing the number of TD’s. Finally writing the complete string using response.write.

Button Click

Protected Sub btnExportToExcel_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnExportToExcel.Click
If Request("Keyword") = 1 Then
Export2Excel("SELECT SearchString, DateOfSearch FROM SearchString WHERE FoundNotFound=1 ORDER BY DateOfSearch DESC", "SearchStrings.xls")
Export2Excel("SELECT SearchString, DateOfSearch FROM SearchString WHERE FoundNotFound=0 ORDER BY DateOfSearch DESC", "SearchStrings.xls")
End If
End Sub

Export DataTable to Excel Function

Private Sub Export2Excel(ByVal SqlQry As String, ByVal FileName As String)
Response.ContentType = "Application/"
Response.AddHeader("Content-Disposition", "attachment; filename=" & FileName & "")

Dim SqlDataAdp As New SqlDataAdapter(SqlQry, sqlConn)
Dim DT As New DataTable

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 & "<tr>"
For Each DTCol In DT.Columns
tempTable = tempTable & "<td style='font-weight:bold;'>"
tempTable = tempTable & DTCol.ColumnName.ToString()
tempTable = tempTable & "</td>"
tempTable = tempTable & "</tr>"

For Each DTRow In DT.Rows
tempTable = tempTable & "<tr>"
For i = 0 To DT.Columns.Count - 1
tempTable = tempTable & "<td align='left'>"
tempTable = tempTable & DTRow(i).ToString()
tempTable = tempTable & "</td>"
tempTable = tempTable & "</tr>"

tempTable = tempTable & "</table>"

End Sub