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
Me.Show()
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)
dataAdapter.Fill(table)
Dim row As Integer = 0
Dim Columns As Integer = 0
With objExcel
.Workbooks.Add()
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
Next
Columns = 0
row = row + 1
Next
Me.lblinfo.Refresh()
.Visible = True
End With
'end of export
objExcel = Nothing
dataAdapter = Nothing
table = Nothing
Flag = True
Query = Nothing
Me.Close()
End Function
End Class
 
 

Comments