Read Data from Excel using OLEDB in VB.NET 2005
By: Issac
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.
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.
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.
Archived Comments
1. can u please help me import data of excel to microsoft SQL using code vb.net ,thank you ...
View Tutorial By: pronomtech at 2016-08-09 08:17:48
2. mohd see this link <a href> http://www.java-samples.com/showtutorial.php?tutorialid=1049 </
View Tutorial By: Issac at 2011-08-01 07:40:48
3. mohd see this link http://www.java-samples.com/showtutorial.php?tutorialid=1049
View Tutorial By: Issac at 2011-05-31 02:39:55
4. can u please help me to read excel sheet in VB
View Tutorial By: Mohd at 2009-07-05 05:03:11
5. hi issac thank you for your sample. can you please explain how to make datagidview editable and upda
View Tutorial By: edwin at 2009-05-20 22:04:14
Comment on this tutorial
- Data Science
- Android
- AJAX
- ASP.net
- C
- C++
- C#
- Cocoa
- Cloud Computing
- HTML5
- Java
- Javascript
- JSF
- JSP
- J2ME
- Java Beans
- EJB
- JDBC
- Linux
- Mac OS X
- iPhone
- MySQL
- Office 365
- Perl
- PHP
- Python
- Ruby
- VB.net
- Hibernate
- Struts
- SAP
- Trends
- Tech Reviews
- WebServices
- XML
- Certification
- Interview
categories
Related Tutorials
Using Resume Next and Resume Line in VB.net
Using On Error GoTo 0 in VB.net
Getting an Exception's Number and Description in VB.net
Raising an Exception Intentionally in VB.net
Exception Filtering in the Catch Block in VB.net
Using Multiple Catch Statements in VB.net
Throwing an Exception in VB.net
Throwing a Custom Exception in VB.net
Changes in Controls from VB6 to VB.net
Unstructured Exception Handling in VB.net