Data Import/Export – Load Excel Sheet to DataTable

Loads an Excel worksheet into a DataTable using OLEDB.

Validates the provided FilePath and checks that the file exists.
Builds a connection string for OLEDB access using the ACE 12.0 provider.
If SheetName is not provided, it calls xlGetFirstSheetName to determine the first available worksheet.
Uses an OleDbDataAdapter to execute a SELECT query and fill a DataSet.
Returns the first table in the DataSet, if available.

Requirements:

The system must have the Microsoft Access Database Engine (ACE OLEDB 12.0) installed.
The Excel file must have headers in the first row (HDR=YES).

    Public Function xlLoadExcelTable(FilePath As String, 
                                     Optional SheetName As String = "", 
                                     Optional FieldsToKeep As String = "*") As Data.DataTable

        If String.IsNullOrWhiteSpace(FilePath) OrElse Not IO.File.Exists(FilePath) Then Return Nothing

        Try
            Dim ConnectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;" &
                                         "Data Source=""" & FilePath & """;" &
                                         "Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1"""

            Dim wsName As String = If(Not String.IsNullOrWhiteSpace(SheetName), SheetName, xlGetFirstSheetName(FilePath))
            If String.IsNullOrWhiteSpace(wsName) Then Return Nothing

            Using MyConnection As New OleDb.OleDbConnection(ConnectionString)
                MyConnection.Open()
                Using MyDataAdapter As New OleDb.OleDbDataAdapter($"SELECT {FieldsToKeep} FROM [{wsName}$]", MyConnection)
                    Dim DtSet As New DataSet()
                    MyDataAdapter.Fill(DtSet)
                    If DtSet.Tables.Count > 0 Then Return DtSet.Tables(0)
                End Using
            End Using

        Catch ex As Exception
            Debug.WriteLine($"[{NameOf(xlLoadExcelTable)}] Error: {ex.Message}")
        End Try

        Return Nothing
    End Function

    Public Function xlLoadExcelTable(FilePath As String, 
                                     Optional SheetName As String = "", 
                                     Optional FieldsToKeep As String = "*") As Data.DataTable

        If String.IsNullOrWhiteSpace(FilePath) OrElse Not IO.File.Exists(FilePath) Then Return Nothing

        Try
            Dim ConnectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;" &
                                         "Data Source=""" & FilePath & """;" &
                                         "Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1"""

            Dim wsName As String = If(Not String.IsNullOrWhiteSpace(SheetName), SheetName, xlGetFirstSheetName(FilePath))
            If String.IsNullOrWhiteSpace(wsName) Then Return Nothing

            Using MyConnection As New OleDb.OleDbConnection(ConnectionString)
                MyConnection.Open()
                Using MyDataAdapter As New OleDb.OleDbDataAdapter($"SELECT {FieldsToKeep} FROM [{wsName}$]", MyConnection)
                    Dim DtSet As New DataSet()
                    MyDataAdapter.Fill(DtSet)
                    If DtSet.Tables.Count > 0 Then Return DtSet.Tables(0)
                End Using
            End Using

        Catch ex As Exception
            Debug.WriteLine($"[{NameOf(xlLoadExcelTable)}] Error: {ex.Message}")
        End Try

        Return Nothing
    End Function

Posted by ipercube