by suraj babu malla (3 Submissions)
Category: Databases/Data Access/DAO/ADO
Compatability: VB 6.0
Difficulty: Unknown Difficulty
Originally Published: Thu 31st August 2006
Date Added: Mon 8th February 2021
Rating: 
(2 Votes)
Export to excel by SQL Query
VBcoders
'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