How to import data from Excel to DataGridView on Visual Basic.NET 2015

Hi see you again on my Visual Basic 2015 learning corner.  Here I will show you how I import data from an Excel files  to a Data Grid View control on Visual Basic .NET 2015.

import excel to datagridview

When the Import button is clicked, it will read and load the data inside the Excel file to DataGridView.

load excel to data grid view vb.net

The code:

Public Class ImportExcel
    Private Sub btn_Import_Click(sender As Object, e As EventArgs) Handles btn_Import.Click
        Try
            Dim MyConnection As OleDb.OleDbConnection
            Dim Ds As System.Data.DataSet
            Dim MyAdapter As System.Data.OleDb.OleDbDataAdapter
            MyConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='E:\Personal\09. Blog\RockType.xls';Extended Properties=Excel 8.0;")
            MyAdapter = New System.Data.OleDb.OleDbDataAdapter("Select * from [RockType$]", MyConnection)
            Ds = New System.Data.DataSet
            MyAdapter.Fill(Ds)
            Me.DataGridView1.DataSource = Ds.Tables(0)
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub
End Class

The program will read RockType.xls located in my E:\Personal\09. Blog\ folder. If you want to adapt this code you will need to change the source (excel file) location with your own file location. And if you are using Excel file (.xlsx), you will need to edit the connection string with the following:

MyConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='E:\Personal\09. Blog\RockType.xlsx';Extended Properties='Excel 12.0 Xml;HDR=YES;'")

 

Admin

One Comment

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.