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.

vb.net_excel.JPG

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.

vb.net_excel_oledb.JPG

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

Conclusion

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.






Related Topics
  1. How to create an Excel file in VB.NET
  2. How to open or read an existing Excel file in VB.NET
  3. How to read an Excel file using VB.Net
  4. Add new worksheet in Excel file - VB.Net
  1. Delete worksheet from an excel file - VB.Net
  2. How to Format Excel Page in VB.NET
  3. How to insert a Picture in Excel through programing on VB.NET
  4. How to insert a background Picture in Excel through VB.NET
  5. How to create a Chart in Excel in VB.NET
  6. How to export a Chart in Excel as Picture file from VB.NET
  7. How to Excel Chart in VB.NET Picture Box
  8. How to Excel DataBar in VB.NET
  9. How to Excel Data Validation Input Message
  10. How to insert cell data in an Excel file using OLEDB
  11. How to update cell data in an Excel file using OLEDB
  12. How to export from database to excel
  13. How to export from DataGridView to excel in VB.Net