【Microsoft Visual Studio】VB.net 將dataGridView中的資料匯出到excel

＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊

＊　　　　　　　　　　電腦環境：Windows 7 64bit　　　　　　　　　　　　　　　　＊

＊　　　　　　　　　　Microsoft Visual Studio版本：2010 Professional　　　　　　　　 ＊

＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊

沿用上一個範例繼續http://bedingfield-tsots.blogspot.tw/2015/10/microsoft-visual-studio.html

目標：希望按下「查詢」可以在dataGridView把指定資料庫的內容載入

目標：希望按下「匯出Excel」可以把dataGridView中的內容轉為excel

就像這樣

＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊

＊　　　　　　　　　　程式語法：Visual Basic　　　　　　　　　　　　　　　　　　＊

＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊

Imports System.Data.SqlClient
Imports System.Globalization

Public Class Form1
    Private resourceCulture As Global.System.Globalization.CultureInfo
    Private Sub button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles button1.Click
        '先將listBox清空
        listBox1.Items.Clear()
        Dim cs As String = "server=(local)\abc;database=Northwind;integrated security=SSPI;"
        Dim qs As String = "SELECT * FROM [Northwind].[dbo].[Employees]"
        '1.引用SqlConnection物件連接資料庫 
        Using cn As New SqlClient.SqlConnection(cs)
            '2.開啟資料庫 
            cn.Open()
            Dim da As SqlDataAdapter = New SqlDataAdapter(qs, cs)
            Using command As New SqlCommand(qs, cn)

                Dim ds As DataSet = New DataSet()
                da.SelectCommand = command
                da.Fill(ds)
                DataGridView1.AutoGenerateColumns = True
                DataGridView1.DataSource = ds.Tables(0)

            End Using
            cn.Close()
            cn.Dispose()
        End Using
    End Sub

    Private Sub Button8_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button8.Click
        Dim MyExcel As Object = CreateObject("Excel.Application")
        MyExcel.Application.Workbooks.Add()
        MyExcel.Visible = True

        '獲取標題
        Dim Cols As Integer
        For Cols = 1 To DataGridView1.Columns.Count
            MyExcel.Cells(1, Cols) = DataGridView1.Columns(Cols - 1).HeaderText
        Next

        '往excel表裡添加資料()
        Dim i As Integer
        For i = 0 To DataGridView1.RowCount - 2
            Dim j As Integer
            For j = 0 To DataGridView1.ColumnCount - 1
                If Me.DataGridView1(j, i).Value Is System.DBNull.Value Then
                    MyExcel.Cells(i + 2, j + 1) = ""
                Else
                    MyExcel.Cells(i + 2, j + 1) = DataGridView1(j, i).Value.ToString
                End If
            Next j
        Next i
    End Sub
End Class