Monday, June 29, 2009

Populate DataGridView From Excel Sheet

Populate DataGridView From Excel Where First Name of Employee starts with 'C'
Need to include the reference Microsoft DAO 3.6

Imports System.Data
Imports System.Data.OleDb

Private Sub btnPopulateGrid_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPopulateGrid.Click

Const stFile As String = "E:\EmployeeExcels\Employee.xls"

Const stCon As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & stFile & ";" & _
"Extended Properties=""Excel 8.0;HDR=YES"";"

Dim dao_dbE As dao.DBEngine
Dim dao_DB As dao.Database
Dim strFirstSheetName As String

'To get the Excel Sheet's Name
dao_dbE = New dao.DBEngine
dao_DB = dao_dbE.OpenDatabase("E:\EmployeeExcels\Employee.xls", False, True, "Excel 8.0;")

strFirstSheetName = dao_DB.TableDefs(1).Name

'The range is named Source and it also contains the fieldnames.

'The fieldnames are automatically added to the Datagrid columnnames.

Dim stSQL As String = "SELECT * FROM [" & strFirstSheetName & "] WHERE [First Name] LIKE 'C%'"

Dim cn As New OleDbConnection(stCon)


Dim da As New OleDbDataAdapter(stSQL, cn)

'The Datasets name is viewed on top of the Datagrid.

Dim dt As New DataTable
'Populate the Datatable


'Populate the DataGridView
With Me.dbGridView
.DataSource = dt

End With


' Adjust the column widths based on the displayed values.
Me.dbGridView.AutoResizeColumns( _

Catch ex As Exception

'Release objects from memory.

If Not dt Is Nothing Then
dt = Nothing
End If
If Not cn is Nothing Then
If cn.State = ConnectionState.Open Then
End If
cn = Nothing
End If

End Try

End Sub


No comments:

Post a Comment