Read Excel Sheet Data into DataTable or Dataset

In VB.NET, it is possible to perform various operations such as inserting, editing, deleting, and selecting cell content in an Excel file without directly using the Excel Object. This can be achieved by utilizing the OLEDB (Object Linking and Embedding Database) technology.

To accomplish these operations, several key components are employed: OleDbConnection, OleDbDataAdapter, and DataSet. These components provide the necessary functionality for establishing a connection to the Excel file, retrieving and manipulating data, and storing the results in a DataSet object.

Import Excel to DataTable

sample Select sql

sql = "select * from [Sheet1$]"

Here is the sample Excel file.


Open the connection using OLEDB Provider

In order to utilize these capabilities, it is essential to import the System.Data namespace into the VB.NET project. This ensures that the required classes and methods are available for working with the Excel file using OLEDB.

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


When reading the content from an Excel file using ADO.NET, a familiar approach similar to SQL operations can be employed. The SELECT command is utilized to query and retrieve specific data from the Excel file. This allows developers to apply filtering, sorting, and other operations on the Excel data, just as they would with a traditional database.

Fastest way to read an Excel Sheet into a DataTable.

Full Source VB.NET
Imports System.Data Public Class Form1 Private Sub Button1_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button1.Click Try 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 MyCommand.Fill(DtSet) DataGridView1.DataSource = DtSet.Tables(0) MyConnection.Close() Catch ex As Exception MsgBox(ex.ToString) End Try End Sub End Class


By using the power of OLEDB and ADO.NET in VB.NET, developers can seamlessly interact with the content of Excel files. This provides a flexible and efficient approach for manipulating cell content, enabling the creation of robust applications that handle Excel data with ease.