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.