How to read an excel work sheet dynamically without specifying the name of the Sheet

Question

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.

Answer 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.

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.

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 :-)