Imports Excel = Microsoft.Office.Interop.Excel
Imports System.Data
Imports System.Data.SqlClient
Imports System.Windows.Forms

Public Class Form1
    Const con As String = "Data Source=XL-DENNIS;" _
                          & "Initial Catalog=Northwind;" _
                          & "Integrated Security=SSPI;" _
                          & "Persist Security Info=False"

    Const SQLExpression As String = "SELECT CustomerID AS ID, CompanyName AS Company," & _
                                    "City, Region, Country FROM Customers" & _
                                    "ORDER BY CompanyName;"
    Dim adp As SqlDataAdapter
    Dim ds As DataSet

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) _
                           Handles MyBase.Load
        Dim cn As New SqlConnection(con)
        Dim cmd As New SqlCommand(SQLExpression, cn)
        cmd.CommandType = CommandType.Text
        cn.Open()
        adp = New SqlDataAdapter(cmd)
        Dim builder As SqlCommandBuilder = New SqlCommandBuilder(adp)
        ds = New DataSet
        adp.Fill(ds, "XL")
        Me.DataGridView1.DataSource = ds.Tables("XL").DefaultView
        cn.Close()
        cn = Nothing
    End Sub

    Private Sub Update_Btn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
                                 Handles Update_Btn.Click
        With Me
            .Validate()
            .adp.Update(Me.ds.Tables("XL"))
            .ds.AcceptChanges()
        End With
    End Sub

    Private Sub Close_Btn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
                                Handles Close_Btn.Click
        ds.Dispose()
        adp.Dispose()
        ds = Nothing
        adp = Nothing
        Me.Close()
    End Sub


    Private Sub Export_Btn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
                                 Handles Export_Btn.Click

        Dim RowsCount As Int32 = Me.DataGridView1.SelectedRows.Count - 1

        If RowsCount > -1 Then
            'The array for field names.
            Dim FldNames() As String = {"ID", "Company", "City", "Region", "Country"}
            'The array for the selected records.
            Dim DataArr(RowsCount, 4) As Object
            Dim ColsCounter As Int32 = 0

            'Populate the data array - The list is sorted in ascending order.
            For RowsCounter As Int32 = 0 To RowsCount
                For Each cell As DataGridViewCell In Me.DataGridView1 _
                                                     .SelectedRows(RowsCount - RowsCounter) _
                                                     .Cells
                    DataArr(RowsCounter, ColsCounter) = cell.Value
                    ColsCounter = ColsCounter + 1
                Next
                ColsCounter = 0
            Next

            'Populate the data array - The list is sorted in descending order. 
            'Dim Rows As Int32 = 0
            'For Each row As DataGridViewRow In Me.DataGridView1.SelectedRows
            'For Each cell As DataGridViewCell In Me.DataGridView1.SelectedRows(Rows).Cells
            'DataArr(Rows, ColsCounter) = cell.Value
            'ColsCounter = ColsCounter + 1
            'Next
            'ColsCounter = 0
            'Rows = Rows + 1
            'Next

            'Variables for Excel.
            Dim xlApp As New Excel.Application
            Dim xlWBook As Excel.Workbook = xlApp.Workbooks.Add( _
                                            Excel.XlWBATemplate.xlWBATWorksheet)
            Dim xlWSheet As Excel.Worksheet = CType(xlWBook.Worksheets(1), Excel.Worksheet)
            Dim xlCalc As Excel.XlCalculation

            'Save the present setting for Excel's calculation mode and turn it off. 
            With xlApp
                xlCalc = .Calculation
                .Calculation = Excel.XlCalculation.xlCalculationManual
            End With

            'Write the field names and the data to the targeting worksheet.
            With xlWSheet
                .Range(.Cells(1, 1), .Cells(1, 5)).Value = FldNames
                .Range(.Cells(2, 1), .Cells(RowsCount + 2, 5)).Value = DataArr
                .UsedRange.Columns.AutoFit()
            End With

            With xlApp
                .Visible = True
                .UserControl = True
                'Restore the calculation mode. 
                .Calculation = xlCalc
            End With

            'Relase objects from memory.                         
            xlWSheet = Nothing
            xlWBook = Nothing
            xlApp = Nothing
            GC.Collect()
        End If

    End Sub
End Class