ExcelReader

来源:互联网 发布:淘宝sku图片大小多少 编辑:程序博客网 时间:2024/03/28 17:42
Imports System.DataImports System.Data.OleDbImports System.IO''' <summary>''' 读取Excel''' </summary>Public Class ExcelReader    Implements IDisposable    '连接字符串说明    'HDR=Yes:将第一行作为DataTable的列名,根据该列的数据判断该列的数据类型    'HDR=No:将所有行都作为数据,所有的数据类型都是string,空值为空字符串""    'IMEX=0:汇出模式,这个模式开启的Excel档案只能用来做“写入”用途。    'IMEX=1:汇入模式,这个模式开启的Excel档案只能用来做“读取”用途。    'IMEX=2:连結模式,这个模式开启的Excel档案可同时支持“读取”与“写入”用途。    Private Const connectionString As String = "Provider={0};Data Source={1};Extended Properties='{2};HDR={3};IMEX=1'"    Private con As OleDbConnection    Sub New(ByVal filename As String)        Me.New(filename, True)    End Sub    Sub New(ByVal filename As String, ByVal hasHead As Boolean)        ChangeExcel(filename, hasHead)    End Sub    Protected Sub ChangeExcel(ByVal filename As String, ByVal hasHead As Boolean)        con = Nothing        If ValidatePath(filename) Then            con = New OleDbConnection            con.ConnectionString = GetConnectionStr(filename, hasHead)        End If    End Sub    Public Function ReadDataSet() As DataSet        If con Is Nothing Then Return Nothing        Dim sheetNames As List(Of String) = GetSheetNames()        sheetNames.RemoveAll(            Function(n)                Return Not n.EndsWith("$")            End Function)        Return ReadDataSet(sheetNames)    End Function    Public Function ReadDataSet(ByVal sheetNames As IEnumerable(Of String)) As DataSet        If con Is Nothing Then Return Nothing        Dim ds As New DataSet        For Each name In sheetNames            Dim dt As DataTable = ReadDataTable(name)            Try                ds.Tables.Add(dt)            Catch ex As Exception                MsgBox(ex.Message)            End Try        Next        Return ds    End Function    Public Function ReadDataTable(ByVal sheetName As String) As DataTable        If con Is Nothing Then Return Nothing        '过滤隐藏表,Oledb读取表会在表后面加上$符号,对于一些有公式的sheet,OleDb会创建一个隐藏表,但这些表没有加上$符号        If sheetName.EndsWith("$") Then            sheetName = sheetName.Substring(0, sheetName.Length - 1)        End If        Dim dt As New DataTable(sheetName)        Dim sqlText As String = String.Format("select * from [{0}$]", sheetName)        Using da As New OleDbDataAdapter(sqlText, con)            Try                da.Fill(dt)            Catch ex As Exception                Return Nothing            End Try        End Using        Return dt    End Function    ''' <summary>    ''' 获取Sheet名称列表    ''' </summary>    Public Function GetSheetNames() As IEnumerable(Of String)        If con Is Nothing Then Return Nothing        con.Open()        Dim dt As DataTable = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)        Dim list As New List(Of String)        For Each row As DataRow In dt.Rows            list.Add(row("TABLE_NAME").ToString)            'System.Threading.Interlocked.Increment(i)        Next        con.Close()        Return list    End Function    ''' <summary>    ''' 验证文件Path    ''' </summary>    Protected Shared Function ValidatePath(ByVal excelPath As String) As Boolean        If File.Exists(excelPath) Then            Dim suffix As String = Path.GetExtension(excelPath).ToLower            If suffix.Equals(".xls") OrElse suffix.Equals(".xlsx") Then                Return True            End If        End If        Return False    End Function    ''' <summary>    ''' 构造连接字符串    ''' </summary>    Protected Shared Function GetConnectionStr(ByVal excelPath As String, ByVal hasHead As Boolean) As String        Dim constr As String = String.Empty        If File.Exists(excelPath) Then            Dim suffix As String = Path.GetExtension(excelPath).ToLower            Dim excelVersion As String            Dim provider As String            If suffix.Equals(".xlsx") Then                provider = "Microsoft.Ace.OleDb.12.0"                excelVersion = "Excel 12.0"            ElseIf suffix.Equals(".xls") Then                provider = "Microsoft.Jet.OLEDB.4.0"                excelVersion = "Excel 8.0"            Else                Return String.Empty            End If            Return String.Format(connectionString, provider, excelPath, excelVersion, If(hasHead, "yes", "no"))        End If        Return String.Empty    End Function#Region "IDisposable Support"    Private disposedValue As Boolean ' 检测冗余的调用    ' IDisposable    Protected Overridable Sub Dispose(ByVal disposing As Boolean)        If Not Me.disposedValue Then            If disposing Then                ' TODO: 释放托管状态(托管对象)。            End If            con.Dispose()            ' TODO: 释放非托管资源(非托管对象)并重写下面的 Finalize()。            ' TODO: 将大型字段设置为 null。        End If        Me.disposedValue = True    End Sub    ' TODO: 仅当上面的 Dispose(ByVal disposing As Boolean)具有释放非托管资源的代码时重写 Finalize()。    'Protected Overrides Sub Finalize()    '    ' 不要更改此代码。请将清理代码放入上面的 Dispose(ByVal disposing As Boolean)中。    '    Dispose(False)    '    MyBase.Finalize()    'End Sub    ' Visual Basic 添加此代码是为了正确实现可处置模式。    Public Sub Dispose() Implements IDisposable.Dispose        ' 不要更改此代码。请将清理代码放入上面的 Dispose(ByVal disposing As Boolean)中。        Dispose(True)        GC.SuppressFinalize(Me)    End Sub#End RegionEnd Class

原创粉丝点击