VB.net 导入Excel方法(2010及以下版本适用)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
''' <summary>
    ''' 读取Excel
    ''' </summary>
    Public Shared Function LoadDataFromExcel() As System.Data.DataTable
        Dim GeneralCommon As New GeneralCommon

        LoadDataFromExcel = Nothing

        Try

            Dim ofd As New OpenFileDialog
            ofd.Filter = "Excel 文件|*.xls;*.xlsx"
            '"Excel文件(*.xls)|*.xls;*.xlsx|"

            If ofd.ShowDialog() = DialogResult.OK Then

                Dim filePath As String = ofd.FileName

                If Not System.IO.Path.GetExtension(filePath) Like ".xls*" Then
                    GeneralCommon.Gp_MsgBoxDisplay("导入Excel失败!失败原因：选择的不是Excel文件", "W", "错误提示")
                End If

                Dim strConn As String
                'Excel07及以下版本
                ' strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'"
                'Excel2010版本及以下
                strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'"
                Dim OleConn As New OleDbConnection(strConn)
                OleConn.Open()
                Dim sql As String = "SELECT * FROM  [Sheet1$]  " '可是更改Sheet名称，比如sheet2，等等    

                Dim OleDaExcel As New OleDbDataAdapter(sql, OleConn)
                Dim OleDsExcle As New DataSet
                OleDaExcel.Fill(OleDsExcle, "Sheet1")
                OleConn.Close()

                If OleDsExcle.Tables.Item(0).Rows.Count = 0 Then
                    GeneralCommon.Gp_MsgBoxDisplay("导入Excel失败!失败原因：选择的Excel中没有数据", "W", "错误提示")
                Else
                    LoadDataFromExcel = OleDsExcle.Tables.Item(0)
                End If

            End If

        Catch ex As Exception
            GeneralCommon.Gp_MsgBoxDisplay("数据绑定Excel失败!失败原因：" + ex.Message, "W", "错误提示")
        End Try


    End Function