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

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

＊　　　　　　　　　　電腦環境：Windows 7 64bit　　　　　　　　　　　　　　　　＊

＊　　　　　　　　　　Microsoft Visual Studio版本：2010 Professional　　　　　　　　 ＊

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

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

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

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

會彈跳出存檔視窗

excel打開就像這樣

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

＊　　　　　　　　　　程式語法：Visual Basic　　　　　　　　　　　　　　　　　　＊

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

首先加入所需References
1. View > Solution Explorer

2. 點一下專案 > 滑鼠右建 > Add Reference

3. 分別去.NET和COM的頁籤將下面這三個reference加入
Microsoft Office 12.0 Access Database Engine Object Library
Microsoft.Office.Interop.Excel
Microsoft Office.Tools.Excel.v9.0

4. 可以在程式中加入所需Import了
Imports Microsoft.Office.Interop
Imports Microsoft.Office.Tools.Excel

5. 程式全文如下

Imports System.Data.SqlClient
Imports System.Globalization
Imports Microsoft.Office.Interop
Imports Microsoft.Office.Tools.Excel
Imports System.IO

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)\LISasst;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 Button7_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button7.Click
        Call DataToExcel(DataGridView1)
    End Sub

    Public Sub DataToExcel(ByVal m_DataView As DataGridView)
        Dim kk As New SaveFileDialog()
        kk.Filter = "EXECL文件(*.xls) |*.xls"
        kk.Title = "Save an Excel File"
        kk.FileName = "Form " & " 報表_" & Now.ToString("yy-MM-dd HHmmss")
        kk.FilterIndex = 1

        If kk.ShowDialog() = DialogResult.OK Then
            Dim FileName As String = kk.FileName ' + ".xls"
            If File.Exists(FileName) Then
                File.Delete(FileName)
            End If
            Dim objFileStream As FileStream
            Dim objStreamWriter As StreamWriter
            Dim strLine As String = ""
            objFileStream = New FileStream(FileName, FileMode.OpenOrCreate, FileAccess.Write)
            objStreamWriter = New StreamWriter(objFileStream, System.Text.Encoding.Unicode)
            For i As Integer = 0 To m_DataView.Columns.Count - 1
                If m_DataView.Columns(i).Visible = True Then
                    strLine = strLine + m_DataView.Columns(i).HeaderText.ToString() + Convert.ToChar(9)
                End If
            Next
            objStreamWriter.WriteLine(strLine)
            strLine = ""

            For i As Integer = 0 To m_DataView.Rows.Count - 1
                If m_DataView.Columns(0).Visible = True Then
                    If m_DataView.Rows(i).Cells(0).Value Is Nothing Then
                        strLine = (strLine & " ") + Convert.ToChar(9)
                    Else
                        strLine = strLine + m_DataView.Rows(i).Cells(0).Value.ToString() + Convert.ToChar(9)
                    End If
                End If
                For j As Integer = 1 To m_DataView.Columns.Count - 1
                    If m_DataView.Columns(j).Visible = True Then
                        If m_DataView.Rows(i).Cells(j).Value Is Nothing Then
                            strLine = (strLine & " ") + Convert.ToChar(9)
                        Else
                            Dim rowstr As String = ""
                            rowstr = m_DataView.Rows(i).Cells(j).Value.ToString()
                            If rowstr.IndexOf(vbCr & vbLf) > 0 Then
                                rowstr = rowstr.Replace(vbCr & vbLf, " ")
                            End If
                            If rowstr.IndexOf(vbLf) > 0 Then
                                rowstr = rowstr.Replace(vbLf, " ")
                            End If
                            If rowstr.IndexOf(vbTab) > 0 Then
                                rowstr = rowstr.Replace(vbTab, " ")
                            End If
                            strLine = strLine + rowstr + Convert.ToChar(9)

                        End If
                    End If
                Next
                objStreamWriter.WriteLine(strLine)
                strLine = ""
            Next
            objStreamWriter.Close()
            objFileStream.Close()
            MessageBox.Show(Me, "儲存EXCEL成功", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information)
        End If
    End Sub

End Class