Export to excel by SQL Query

Submitted by:Andery Smith

Date added:31 October, 2011

Category:Visual Basic

It is a class file which can export to excel. simply call the function and give sql Query and sql connection.

Tags: sql query

Code Snippet:

'Created By : Suraj babu malla
'Created Date: August 30 2006
'Objective : To export dato to excel
'Requiredment : MS Office(excel) 2003
'Remark : Just Add a refrence and make a object and passed to function(ExportToExcel)Query and sqlconnection

Imports System.Data.SqlClient
Public Class ClsExcelExporter
Dim objExcel As New Microsoft.Office.Interop.Excel.Application
Private dataAdapter As New SqlDataAdapter()
Public Function ExportToExcel(ByVal Query As String, ByVal SQlConnection As SqlConnection) As Boolean
Dim Flag As Boolean
Flag = False
Dim table As New DataTable()
table.Locale = System.Globalization.CultureInfo.InvariantCulture
Dim da As New Data.SqlClient.SqlDataAdapter
Me.dataAdapter = New SqlDataAdapter(Query, SQlConnection.ConnectionString)
Dim row As Integer = 0
Dim Columns As Integer = 0
With objExcel
Dim iHeader As Integer
Dim Item As DataColumn
Dim ItemC As DataColumn
Dim ItemR As DataRow
iHeader = 1
'Adding Header
For Each Item In table.Columns
.Cells(1, iHeader) = Item.ColumnName
.Cells(1, iHeader).Font.Bold = True
iHeader = iHeader + 1
Next Item
'Adding Item
For Each ItemR In table.Rows
For Each ItemC In table.Columns
.Cells(row + 2, Columns + 1) = table.Rows(row).Item(Columns).ToString
Columns = Columns + 1
Columns = 0
row = row + 1
.Visible = True
End With
'end of export
objExcel = Nothing
dataAdapter = Nothing
table = Nothing
Flag = True
Query = Nothing
End Function
End Class