Website Design United States, Website Design California, Website Designing United States, Website Designing California

Open Excel WorkSheet Like an SQL Table in VB.Net - Visual Basic .NET

If you have an Excel spreadsheet that is set up in table format (with headers on the columns) then you can read it as if it were a table in an SQL database. This is a very nice feature and makes retrieving information from Excel a breeze. There is no exception handling in this example to make it easier to see what is happening but I would recommend adding it once you understand the process.

Imports System
Imports System.Data
Imports System.Data.OleDb

Module Module1
'Viewing an Excel Worksheet like an SQL table

Sub Main()
'Name of workbook
Dim strWorkbookName As String = "C:\NameofExcelFile.XLS"

'Create connection string
Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strWorkbookName + ";Extended Properties=Excel 8.0;"

'Connect to workbook
Dim oleConn As OleDbConnection = New OleDbConnection
oleConn.ConnectionString = strConn

'Name of workSheet to be accessed
Dim strSheetName As String = "Sheet1"

'Fill array with queried information
Dim oleCmd As OleDbDataAdapter = New OleDbDataAdapter("SELECT * FROM [" + strSheetName + "$]", oleConn)
Dim oDS As DataSet = New DataSet

'Display information in console
Dim oleRowCount As Integer = 1 'just for display purposes
For Each oleRow As DataRow In oDS.Tables(0).Rows
Console.WriteLine("Row: " + oleRowCount.ToString("00#") + " " + oleRow(0).ToString + " " + oleRow(1).ToString)
oleRowCount = oleRowCount + 1 'just for display purposes, doesn't effect the iteration

'Close workbook connection
If oleConn.State = ConnectionState.Open Then
End If

'Keep console open until user is finished viewing data
Console.Write("Press Enter to quit.")
End Sub
End Module

42 B Malviya Nagar , New Delhi-110017

Skype: manmeetsi
Tel: 91-011-40502005, 9810067295



© 2008-2009