Visual Basic: Load an Excel sheet into a DataTable and DataGridView

Platform:Visual Basic
Task:Load an Excel sheet into a DataTable and DataGridView
Discussion:
The following example loads the data from an Excel spreadsheet into a DataTable and subsequently, a DataGridView.
Example:
Imports System.Data.OleDb
Public Class Form2

    Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.WindowState = FormWindowState.Maximized
        LoadInputSheetIntoDataTable("C:\temp\2-ConvertedRawData\GPS\xxx.xls")
    End Sub

    Private Sub LoadInputSheetIntoDataTable(ByVal InputWorksheetName As String)
        Dim CaribouLocationsConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & InputWorksheetName & "; Extended Properties=""Excel 8.0;HDR=YES;IMEX=1"""
        Dim CaribouLocationsConnection As New OleDbConnection(CaribouLocationsConnectionString)
        CaribouLocationsConnection.Open()

        Dim CaribouLocationsDataTable As New DataTable
        Dim caribouLocationsCommand As New OleDbCommand("SELECT * FROM [xxx$]", CaribouLocationsConnection)
        Dim CaribouLocationsDataAdapter As New OleDbDataAdapter(caribouLocationsCommand)
        CaribouLocationsDataAdapter.Fill(CaribouLocationsDataTable)

        CaribouLocationsConnection.Close()
        CaribouLocationsDataGridView.DataSource = CaribouLocationsDataTable
    End Sub

End Class
Back to Index