Private Function GetData() As DataTable
Dim constr As String
Dim connection As SqlConnection
constr = ConfigurationManager.ConnectionStrings("Connectionstring").ToString
connection = New SqlConnection(constr)
Using con As SqlConnection = New SqlConnection(constr)
Using cmd As SqlCommand = New SqlCommand("SELECT * FROM customers")
Using sda As SqlDataAdapter = New SqlDataAdapter()
cmd.Connection = con
sda.SelectCommand = cmd
Using dt As DataTable = New DataTable()
sda.Fill(dt)
Return dt
End Using
End Using
End Using
End Using
End Function
Protected Sub exportexcel() '
Dim dt As DataTable = GetData()
dt.TableName = "GridView_Data"
Using wb As XLWorkbook = New XLWorkbook()
wb.Worksheets.Add(dt)
Using memoryStream As MemoryStream = New MemoryStream()
wb.SaveAs(memoryStream)
Dim bytes As Byte() = memoryStream.ToArray()
memoryStream.Close()
Using mm As MailMessage = New MailMessage("sender@gmail.com", "receiver@gmail.com")
mm.Subject = "GridView Exported Excel"
mm.Body = "GridView Exported Excel Attachment"
mm.Attachments.Add(New Attachment(New MemoryStream(bytes), "GridView.xlsx"))
mm.IsBodyHtml = True
Dim smtp As SmtpClient = New SmtpClient()
smtp.Host = "smtp.gmail.com"
smtp.EnableSsl = True
Dim credentials As New NetworkCredential("xxxxxx@xxxxx.com", "xxxxxxxx")
smtp.UseDefaultCredentials = True
smtp.Credentials = credentials
smtp.Port = 587
smtp.Send(mm)
End Using
End Using
End Using
End Sub
Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click
'ExportExcel()
If Not Me.IsPostBack Then
GridView1.DataSource = Me.GetData()
GridView1.DataBind()
exportexcel()
End If
End Sub
