Net-informations.com
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 ado.net , 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 .

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

vb.net_excel_oledb.JPG

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






net-informations.com (C) 2017    Founded by raps mk
All Rights Reserved. All other trademarks are property of their respective owners.