Dim strConnString As String = "Server=localhost;UID=sa;PASSWORD=;database=mydatabase"
Dim objConn = New SqlConnection(strConnString)
Dim dtAdapter = New SqlDataAdapter()
Dim dt = New DataTable()
objConn.Open()
Dim strSQL As String = "SELECT * FROM myTable"
dtAdapter = New SqlDataAdapter(strSQL, objConn)
dtAdapter.Fill(dt)
Dim template As New FileInfo(Server.MapPath("Xls/Template.xlsx"))
Using package = New ExcelPackage(template)
Dim workbook = package.Workbook
'*** Sheet 1
Dim worksheet = workbook.Worksheets.First()
Dim startRows As Integer = 3
For i As Integer = 0 To dt.Rows.Count - 1
worksheet.Cells("A" + (i + startRows)).Value = dt.Rows(i)("Column1").ToString()
worksheet.Cells("B" + (i + startRows)).Value = dt.Rows(i)("Column2").ToString()
worksheet.Cells("C" + (i + startRows)).Value = dt.Rows(i)("Column3").ToString()
worksheet.Cells("D" + (i + startRows)).Value = dt.Rows(i)("Column4").ToString()
worksheet.Cells("E" + (i + startRows)).Value = dt.Rows(i)("Column5").ToString()
Next
package.SaveAs(New FileInfo(Server.MapPath("Xls/myExcel.xlsx")))
End Using
dtAdapter = Nothing
objConn.Close()