SiteMap  | About    

Read Excel Sheet Data into DataTable or Dataset

Without using Excel Object we can insert , edit , delete , select etc. in cell content of an Excel file using OLEDB in VB.NET . Here we are using OleDbConnection , OleDbDataAdapter , DataSet for doing these operations in an Excel file. You have to import System.Data in the project for doing these operations . For read the content from Excel file using , We can use the SELECT command like in SQL Operations.

Import Excel to DataTable

sample Select sql

sql = "select * from [Sheet1$]"

Here is the sample Excel file .


Open the connection using OLEDB Provider

(provider=Microsoft.Jet.OLEDB.4.0;Data Source='Your Filename';Extended Properties=Excel 8.0;)

Specify which data you want to read

select * from [Sheet1$]

Here is the screen short after reading from Excel file .


Fastest way to read an Excel Sheet into a DataTable

Imports System.Data
Public Class Form1
    Private Sub Button1_Click(ByVal sender As System.Object, _
                ByVal e As System.EventArgs) Handles Button1.Click
            Dim MyConnection As System.Data.OleDb.OleDbConnection
            Dim DtSet As System.Data.DataSet
            Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
            MyConnection = New System.Data.OleDb.OleDbConnection _
            ("provider=Microsoft.Jet.OLEDB.4.0;"  _
            " Data Source='c:\testfile.xls'; " _
             "Extended Properties=Excel 8.0;")
            MyCommand = New System.Data.OleDb.OleDbDataAdapter _
                ("select * from [Sheet1$]", MyConnection)
            MyCommand.TableMappings.Add("Table", "TestTable")
            DtSet = New System.Data.DataSet
            DataGridView1.DataSource = DtSet.Tables(0)
        Catch ex As Exception
        End Try
    End Sub
End Class

When you execute this program you will get the contents in the excel file to the DataGrid. (C) 2017    Founded by raps mk
All Rights Reserved. All other trademarks are property of their respective owners.