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 asp.net 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")
Else
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.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 & "<tr>"
For Each DTCol In DT.Columns
tempTable = tempTable & "<td style='font-weight:bold;'>"
tempTable = tempTable & DTCol.ColumnName.ToString()
tempTable = tempTable & "</td>"
Next
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>"
Next
tempTable = tempTable & "</tr>"
Next
tempTable = tempTable & "</table>"
Response.Write(tempTable)
Response.End()
End Sub
