VB.NET OLEDB 快速获取Excel表名称列表

本篇文章分享vb.net如果快速读取Excel文件中的Sheet表名称,无视Excel文件大小,轻松快速获取

应用场景:快速获取某个文件夹下的Excel文件Sheet表信息

1.帮助类源代码

Imports System.Data
Imports System.Data.OleDb



''' <summary>
''' Excel OleDB数据类操作类
''' </summary>
Public Class ExcelOleDBUtilsTools
    Implements IDisposable

    Private disposedValue As Boolean

    ''' <summary>
    ''' OLE链接字符串
    ''' </summary>
    ''' <returns></returns>
    Private Property StrConn As String

    ''' <summary>
    ''' 构造函数
    ''' </summary>
    Public Sub New()
    End Sub

    ''' <summary>
    ''' 构造函数
    ''' </summary>
    ''' <param name="fileName"></param>
    Public Sub New(fileName As String)
        If fileName.EndsWith(".xls") Then
            ''读取“.xls”时使用"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ExcelPath + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'" Office 07及以上版本 不能出现多余的空格 而且分号注意
            StrConn = $"Provider=Microsoft.Jet.OLEDB.4.0;Data Source ={fileName};Extended Properties='Excel 8.0;HDR=Yes;IMEX=0'"
        ElseIf fileName.EndsWith(".xlsx") Or fileName.EndsWith(".xlsm") Then
            ''读取“.xlsx”时使用 "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ExcelPath + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'"Office 07以下版本 
            StrConn = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source ={fileName};Extended Properties='Excel 8.0;HDR=Yes;IMEX=0'"
        End If
    End Sub

    ''' <summary>
    ''' 获取工作表列表
    ''' </summary>
    ''' <returns></returns>
    Public Function GetWorkSheetList() As List(Of String)
        Try
            Using myCon As New OleDb.OleDbConnection(StrConn) ''连接数据库    
                myCon.Open() ''打开数据库
                ''---------------------
                Dim tableName As New List(Of String)
                Dim dt As System.Data.DataTable = myCon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "Table"}) ''获取所有sheet的工作表
                For Each row As DataRow In dt.Rows
                    Dim SheetTableName As String = row.Item("TABLE_NAME").ToString()
                    If SheetTableName.Contains("$") AndAlso SheetTableName.Replace("'", "").EndsWith("$") Then '' 过滤无效SheetName 
                        SheetTableName = SheetTableName.Replace("'", "")
                        SheetTableName = SheetTableName.Substring(0, SheetTableName.Length - 1)
                        tableName.Add(SheetTableName)
                    End If
                Next
                ''--------------------
                Return tableName
            End Using
        Catch ex As Exception
            Return New List(Of String)
        End Try
    End Function



#Region "释放对象"

    Protected Overridable Sub Dispose(disposing As Boolean)
        If Not disposedValue Then
            If disposing Then
                ' TODO: 释放托管状态(托管对象)
            End If

            ' TODO: 释放未托管的资源(未托管的对象)并重写终结器
            ' TODO: 将大型字段设置为 null
            disposedValue = True
        End If
    End Sub

    ' ' TODO: 仅当“Dispose(disposing As Boolean)”拥有用于释放未托管资源的代码时才替代终结器
    ' Protected Overrides Sub Finalize()
    '     ' 不要更改此代码。请将清理代码放入“Dispose(disposing As Boolean)”方法中
    '     Dispose(disposing:=False)
    '     MyBase.Finalize()
    ' End Sub

    Public Sub Dispose() Implements IDisposable.Dispose
        ' 不要更改此代码。请将清理代码放入“Dispose(disposing As Boolean)”方法中
        Dispose(disposing:=True)
        GC.SuppressFinalize(Me)
    End Sub

#End Region


End Class

2.调用方法

Dim oledb As New ExcelOleDBUtilsTools(file)
Dim SheetNameList As List(of String)=oledb.GetWorkSheetList()