後菜鳥阿華的筆記

使用WinForm
先做個選擇EXCEL的按鈕  SelectXLS
做click動作

public Sub SelectXLS_Click(sender As Object, e As EventArgs) Handles SelectXLS.Click
         '初始化 OpenFileDialog1
        OpenFileDialog1.Multiselect = False
         '如果有讀到檔案
        If OpenFileDialog1.ShowDialog() = DialogResult.OK Then
        '呼叫Import_Grid(檔案,副檔名,狀態) 自訂的SUB 
            For Each file As String In OpenFileDialog1.FileNames
                Import_Grid(file, Split(file, ".")(1), "Yes")
            Next
        End If
End Sub

Private Sub Import _Grid(ByVal FilePath As String, ByVal Extension As String, ByVal isHDR As String)

        Dim conStr As String = ""

        Dim sL_SQL As String = ""

        Dim sL_Excel_2003 As String ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'"

        Dim sL_Excel_2007 As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'"

        Try

           '用附檔名判斷excel版本

            Select Case Extension

                Case "xls"

                    'Excel 97-03

                    conStr = sL_Excel_2003

                    Exit Select

                Case "xlsx"

                    'Excel 07

                    conStr = sL_Excel_2007

                    Exit Select

            End Select

            conStr = String.Format(conStr, FilePath, isHDR)

            Dim connExcel As New OleDbConnection(conStr)

            Dim cmdExcel As New OleDbCommand()

            Dim oda As New OleDbDataAdapter()

            Dim dt As New DataTable()

            cmdExcel.Connection = connExcel

            connExcel.Open()

            Dim dtExcelSchema As DataTable

            dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)

            '讀取excel第一個 工作列1 

            Dim SheetName As String = dtExcelSchema.Rows(0)("TABLE_NAME").ToString()

            connExcel.Close()

            connExcel.Open()

 

            sL_SQL = "SELECT excel的標題1,excel的標題2,excel的標題3 "

            sL_SQL += " FROM [" & SheetName & "]"

            cmdExcel.CommandText = sL_SQL

            oda.SelectCommand = cmdExcel

            oda.Fill(dt)

            connExcel.Close()

            Me.DataGridView1.DataSource = dt

            Me.DataGridView1.Visible = True

            Me.btn2DB.Visible = True

        Catch ex As Exception

            MsgBox("讀取Excel發生錯誤:" + ex.Message) : WriteLog("讀取Excel發生錯誤:" + ex.Message)

        End Try

    End Sub