Reading Excel Data by OLEDB Connection in .NET

Reading Excel Data by OLEDB Connection in .NET

Dim MyConnection As System.Data.OleDb.OleDbConnection
        Dim DtSet As System.Data.DataSet

        Try
            pSheetNo = pSheetNo - 1
            If UCase(fileextension) = ".XLS" Then

                ''''''' Fetch Data from Excel

                Dim MyCommand As System.Data.OleDb.OleDbDataAdapter

                MyConnection = New 

               System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; ;data 

                source='" & PrmPathExcelFile & " '; Extended Properties=""Excel 8.0;IMEX=1;""")
                MyConnection.Open()

                ' Select the data from Sheet1 of the workbook.
                Dim myTableName = MyConnection.GetSchema("Tables").Rows(pSheetNo)

                ("TABLE_NAME")

                MyCommand = New 

                System.Data.OleDb.OleDbDataAdapter(String.Format("SELECT * FROM [{0}]", 

                 myTableName), MyConnection)
                MyCommand.TableMappings.Add("Table", "FO")
                DtSet = New System.Data.DataSet
                MyCommand.Fill(DtSet)
                '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
                MyConnection.Close()
                Return DtSet
            Else
                ''''''' Fetch Data from Excel

                Dim MyCommand As System.Data.OleDb.OleDbDataAdapter

                MyConnection = New 

                System.Data.OleDb.OleDbConnection("provider=Microsoft.ACE.OLEDB.12.0; ;data 

                source='" & PrmPathExcelFile & " '; Extended Properties=""Excel 12.0;IMEX=1;""")
                MyConnection.Open()

                ' Select the data from Sheet1 of the workbook.
                Dim myTableName = MyConnection.GetSchema("Tables").Rows(pSheetNo)

                ("TABLE_NAME")

                MyCommand = New 

                System.Data.OleDb.OleDbDataAdapter(String.Format("SELECT * FROM [{0}]", 

                myTableName), MyConnection)
                MyCommand.TableMappings.Add("Table", "FO")
                DtSet = New System.Data.DataSet
                MyCommand.Fill(DtSet)
                '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
                MyConnection.Close()
                Return DtSet
            End If
        Catch ex As Exception
            MyConnection.Close()
            System.Windows.Forms.MessageBox.Show(ex.Message, pmsgTitle, 

            System.Windows.Forms.MessageBoxButtons.OK, 

            System.Windows.Forms.MessageBoxIcon.Error)
        End Try