Loading Excel 2007 or 2010 worksheet into DataTable in .NET

This article aims to explain how to load data from Excel 2007/ 2010 worksheet to a DataTable in .NET application.

Say, there is a worksheet "CitiesList" in 2007/ 2010 workbook "Geography.xlsx".

Now you want to display this list in a dropdownlist in your VB.NET Windows Application.

To achieve this,
1) Import System.Data, and System.Data.OleDb namespaces in your Windows Form.
Imports System.Data
Imports System.Data.OleDb
OleDb provider is used to retrieve data from Microsoft Office Excel. Write these statements at top before Class and Namespace declarations.

2) Write following code to load data from CitiesList spreadsheet to a DataTable, and bind DataTable with the combo box.

    Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        Dim odbcConn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Learnings\Cities.xlsx;Extended Properties='Excel 12.0;HDR=Yes;IMEX=1';")
        Try
            Dim citiesTable As New DataTable("cities")
            Dim odbcAdapter As New OleDbDataAdapter("Select * from [CitiesList$]", odbcConn)

            odbcConn.Open()
            odbcAdapter.Fill(citiesTable)

            cboCities.DataSource = citiesTable
            cboCities.DisplayMember = "Cities"
            cboCities.ValueMember = "Cities"
        Catch ex As Exception

        Finally
            odbcConn.Close()

        End Try

    End Sub
End Class

Run your application and you will see the ComboBox is filled with the data exist in Cities worksheet of the workbook Cities.xlsx