Read Data from Excel using OLEDB in VB.NET 2005

By: Issac Emailed: 1786 times Printed: 2623 times    

We can see about OLEDB object which can be used to work with data in cell (i.e. insert, edit, delete, select etc), we can use OleDbConnection, OleDbDataAdapter and dataset for doing these operations in an Excel File, also you have to import system.Data

Sample Select sql
Sql = "select * from [Sheet1$]"
Here is the sample Excel file.

vb.net_excel.JPG

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 shot after reading from Excel file.

vb.net_excel_oledb.JPG

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

When you execute this program you will get the contents in the excel file to the DataGrid.

Most Viewed Articles (in VB.net )

Latest Articles (in VB.net)

Comment on this tutorial

Subscribe to Tutorials

Related Tutorials

Archived Comments

1. hi issac thank you for your sample. can you please
View Tutorial          By: edwin at 2009-05-20 22:04:14

2. can u please help me to read excel sheet in VB
View Tutorial          By: Mohd at 2009-07-05 05:03:11

3. mohd see this link http://www.java-samples.com/sho
View Tutorial          By: Issac at 2011-05-31 02:39:55

4. mohd see this link <a href> http://www.java-
View Tutorial          By: Issac at 2011-08-01 07:40:48

5. can u please help me import data of excel to micro
View Tutorial          By: pronomtech at 2016-08-09 08:17:48