My Blog

Import data from excel file to DataGridView

In continuation from here

We created a file earlier here.

Now let’s try and import the data into a DataGridView. In your form, add a DataGridView and also add a button as shown in the image below.

What we will do is ask user to select the excel file and then we will import the data from Sheet1 into a Dataset and finally bind the DataGridView with it.

This is how our code will look like

Imports System.Data.OleDb

Public Class Form1
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim olecon As OleDbConnection = New OleDbConnection
        Dim fd As OpenFileDialog = New OpenFileDialog()

        fd.Title = "Please select Excel File"
        fd.Filter = "Excel files (*.xlsx)|*.xlsx|Excel files (*.xlsx)|*.xlsx"
        fd.FilterIndex = 2
        fd.RestoreDirectory = True

        If fd.ShowDialog() = DialogResult.OK Then
            Dim FileName As String = fd.FileName

            '~~> Construct your connection string
            Dim connstring As String = "Provider=Microsoft.ACE.OLEDB.12.0;" &
                                       "Data Source=" & FileName & ";" &
                                       "Extended Properties=""Excel 12.0 Xml;HDR=YES;"""

            olecon.ConnectionString = connstring
            olecon.Open()

            '~~> Change Sheet1 to relevant sheet
            Dim dta As OleDbDataAdapter = New OleDbDataAdapter("Select * From [Sheet1$]", olecon)

            olecon.Close()

            Dim dts As DataSet = New DataSet
            dta.Fill(dts, "[Sheet1$]")
            DataGridView1.DataSource = dts
            DataGridView1.DataMember = "[Sheet1$]"

        End If
    End Sub
End Class

This is how our DataGridView will look like after it is populated.