• Home
 · 
• Databases/Data Access/DAO/ADO
 · 
• Export to excel by SQL Query

Export to excel by SQL Query

Export to excel by SQL Query

Rate Export to excel by SQL Query

• 1
• 2
• 3
• 4
• 5

(2(2 Vote))

'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