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

需引入命名空间：Imports System.Data.OleDb

''' <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中没有数据","错误提示")
                Else
                    LoadDataFromExcel = OleDsExcle.Tables.Item(0)
                End If

            End If

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


    End Function

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!