Export data from datareader to Excel in Vb.Net
Step 1: Create a table andd insert data into that table.
Step 2: Use the below Function directly.
Public Sub exportData()
Dim strData As String = ""
Dim bolFirstPass As Boolean = True
Dim oCn As SqlConnection = Nothing
Dim oCmd As SqlCommand = Nothing
Dim oDr As SqlDataReader = Nothing
Try
oCn = New SqlConnection("YOUR CONNECTION STRING ")
oCn.Open()
oCmd = New SqlCommand("select * from table", oCn)
'Dim da As SqlDataAdapter = New SqlDataAdapter(oCmd)
'Dim dt As DataTable = New DataTable()
'da.Fill(dt)
'DatatableToExcel(dt)
oDr = oCmd.ExecuteReader(CommandBehavior.CloseConnection)
While oDr.Read
If bolFirstPass Then
bolFirstPass = False
'Write the Header Row
strData = "<body><meta http-equiv='Content-Type' content='text/html;charset=utf-8'/>"
strData &= "<table border=1>"
strData &= "<tr>"
For i As Integer = 0 To oDr.FieldCount - 1
strData &= "<td>" & Replace(Replace(oDr.GetName(i), "[", ""), "]", "") & "</td>"
Next
strData &= "</tr>"
bolFirstPass = False
Response.Clear()
Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader("content-disposition", "attachment; filename=filename.xls")
Response.Write(strData)
End If
strData = "<tr>"
For i As Integer = 0 To oDr.FieldCount - 1
strData &= "<td>" & i.Tostring() & "</td>"
Next
strData &= "</tr>"
Response.Write(strData)
End While
Response.Write("</table></body>")
Response.End()
Catch ex As Exception
If Not ex.Message.Contains("Thread was being aborted.") Then
_global.WriteErrorLog("exportData.loadPage() failed! Error is: " & ex.Message)
End If
Finally
If Not oCn Is Nothing Then If oCn.State = ConnectionState.Open Then oCn.Close()
oDr.Close()
End Try
End Sub
Step 3: Call this function on your button Click.