如何在不指定工作表名称的情况下动态读取excel工作表

Dim MyConnection As System.Data.OleDb.OleDbConnection
        Dim DtSet As System.Data.DataSet
        Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
        Dim filePath = GlobalVariable.savedPath + GlobalVariable.excelFileName

        'Code to Use an Oledb Connection to get data from the Excel File
        MyConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & filePath & ";Extended Properties='Excel 12.0 Xml;HDR=YES;';")
        'Code to Read the Sheet Selected from the Excel Spread Sheet'
        MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection)
        '-- Extra Code Not Needed which Maps the tables as Columns from the Spreadsheet
        'MyCommand.TableMappings.Add("Table", "Net-informations.com")
        DtSet = New System.Data.DataSet
        MyCommand.Fill(DtSet)
        'Populates GridView with the excel Spreadsheet
        GridView1.DataSource = DtSet.Tables(0)
        'Code Below needed to show the excel to GridView
        GridView1.DataBind()
        MyConnection.Close()

This code basically shows the excel file using select * from Sheet1$ in the grid view. However, I want whatever file I have in the datasource to show up the excel sheet dynamically.

该代码在网格视图中使用select *从Sheet1$显示excel文件。但是，我希望在datasource中有任何文件可以动态显示excel表。

1 个解决方案

#1

The following will collect sheet names from a specific Excel file via OleDb which unfortunately sorts a-z when fetching sheets unlike Excel automation you can collect sheet names via ordinal position.

下面将通过OleDb从一个特定的Excel文件中收集表单名称，但不幸的是，当取到与Excel自动化不同的表单时，您可以通过序号位置收集表名。

Next up, as I am sure you know but will say it anyways, the first row of a sheet can be column names or data and dependent on which it is the connection string needs to be set for the right one (as shown in the code) but there is no true method to determine this, usually it's "you know it" or "customers decide".

接下来,我相信你知道但会说不管怎样,表的第一行可以列名或数据,依赖于它的需要设置连接字符串是正确的(如代码所示),但没有正确的方法来确定,通常是“你知道”或“客户决定”。

First up, supporting classes (which are taken from this code sample)

首先，支持类(来自这个代码示例)

Imports System.Data.OleDb
Public Class Connections
    Public Sub New()
    End Sub
    ''' <summary>
    ''' Create a connection where first row contains column names
    ''' </summary>
    ''' <param name="FileName"></param>
    ''' <param name="IMEX"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    <System.Diagnostics.DebuggerStepThrough()> _
    Public Function HeaderConnectionString(ByVal FileName As String, Optional ByVal IMEX As Integer = 1) As String
        Dim Builder As New OleDbConnectionStringBuilder
        If IO.Path.GetExtension(FileName).ToUpper = ".XLS" Then
            Builder.Provider = "Microsoft.Jet.OLEDB.4.0"
            Builder.Add("Extended Properties", String.Format("Excel 8.0;IMEX={0};HDR=Yes;", IMEX))
        Else
            Builder.Provider = "Microsoft.ACE.OLEDB.12.0"
            Builder.Add("Extended Properties", String.Format("Excel 12.0;IMEX={0};HDR=Yes;", IMEX))
        End If

        Builder.DataSource = FileName

        Return Builder.ToString

    End Function
    ''' <summary>
    ''' Create a connection where first row contains data
    ''' </summary>
    ''' <param name="FileName"></param>
    ''' <param name="IMEX"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    <System.Diagnostics.DebuggerStepThrough()> _
    Public Function NoHeaderConnectionString(ByVal FileName As String, Optional ByVal IMEX As Integer = 1) As String
        Dim Builder As New OleDbConnectionStringBuilder
        If IO.Path.GetExtension(FileName).ToUpper = ".XLS" Then
            Builder.Provider = "Microsoft.Jet.OLEDB.4.0"
            Builder.Add("Extended Properties", String.Format("Excel 8.0;IMEX={0};HDR=No;", IMEX))
        Else
            Builder.Provider = "Microsoft.ACE.OLEDB.12.0"
            Builder.Add("Extended Properties", String.Format("Excel 12.0;IMEX={0};HDR=No;", IMEX))
        End If

        Builder.DataSource = FileName

        Return Builder.ToString

    End Function
End Class

Another supporting class

另一个支持类

Imports System.Data.OleDb

Public Class Utility
    Public Sub New()
    End Sub
    ''' <summary>
    ''' Returns a list of sheet names from Excel or table names from Access
    ''' </summary>
    ''' <param name="ConnectionString"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function SheetNames(ByVal ConnectionString As String) As List(Of SheetNameData)
        Dim Names As New List(Of SheetNameData)

        Using cn As New OleDbConnection(ConnectionString)
            cn.Open()
            Dim dt As DataTable = New DataTable With {.TableName = "AvailableSheetsTables"}
            dt = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "TABLE"})
            cn.Close()

            Names =
                (
                    From F In dt.Rows.Cast(Of DataRow)()
                    Select New SheetNameData With
                           {
                               .DisplayName = F.Field(Of String)("TABLE_NAME").Replace("$", ""),
                               .ActualName = F.Field(Of String)("TABLE_NAME")
                           }
                ).ToList

        End Using

        Return Names
    End Function
End Class
Public Class SheetNameData
    Public Sub New()

    End Sub
    Public Property DisplayName As String
    Public Property ActualName As String

End Class

First thing you will note is the file name and path are hard coded, no checks to see if it exists as this entire reply is a sample, keeps to the task at hand. In the button click event we get the sheet names, this of course could be done in another event e.g. form load. Sheet names are placed into a strong type list, first sheet is selected (if unsure if there are sheets then assertion would be used). I open the sheet with first row as data, if your's has column headers I have included a connection method for this too. A DataTable is loaded with used rows and column and the DataTable becomes the DataSource of the DataGridView.

首先要注意的是文件名称和路径是硬编码的，不检查它是否存在，因为整个回复是一个示例，保存在手边的任务中。在按钮单击事件中，我们会得到表单名称，当然这也可以在另一个事件中完成，例如表单加载。表名被放置在一个强类型列表中，第一个表单被选中(如果不确定是否有表，那么就会使用断言)。我用第一行作为数据打开这个表，如果你的表有列标题，我也为此包含了一个连接方法。DataTable使用已使用的行和列加载，DataTable将成为DataGridView的数据源。

Public Class Form1
    Private FileName As String = IO.Path.Combine(
        AppDomain.CurrentDomain.BaseDirectory, "PeopleData.xlsx")
    Private Connection As Connections = New Connections
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim Utils = New Utility

        Dim SheetsList As List(Of SheetNameData) = Utils.SheetNames(
            Connection.NoHeaderConnectionString(IO.Path.GetFileName(FileName)))

        Dim dt As New DataTable
        Dim ConnectionString As String = Connection.NoHeaderConnectionString(FileName)

        Using cn As New OleDb.OleDbConnection With {.ConnectionString = ConnectionString}
            Using cmd As New OleDb.OleDbCommand(
                String.Format("SELECT * FROM [{0}]",
                              SheetsList.FirstOrDefault.ActualName),
                          cn)
                cn.Open()
                dt.Load(cmd.ExecuteReader)
            End Using
        End Using

        DataGridView1.DataSource = dt

    End Sub
End Class

Ending note, if you want a specific sheet, not a random sheet you would have to open each sheet, check for expected data e.g. if the first row has field/column names read the first row and valid this with the connection string for no headers then once the correct sheet is found, load it using the connection string with the header set.

结束,如果你想要一个特定的表,而不是一个随机表你需要打开每个表,检查预期数据如:如果第一行字段/列名读第一行和有效的连接字符串没有头一旦找到正确的表,使用连接字符串加载它的头。

Hope this helps :-)

希望这有助于:-)