Creating a new Windows Forms Application project, then design a simple UI that allows you to select an excel file, then open, read and import data from excel file to DataGridView in vb.net as shown below.
How to Open and Read Excel Files in VB.NET
Right-clicking on your project, then select Manage Nuget Packages. Next, Search and install ExcelDataReader, ExcelDataReader.DataSet to your project.
ExcelDataReader is a lightweight written in C# for reading Microsoft Excel files (XLS, XLSX).
ExcelDataReader.DataSet is an extension for reading Microsoft Excel files into System.Data.DataSet.
Adding the click event handler to the Browse button allows you to select the excel file, then read data from the excel file to DataTable and return as a DataSet.
Private Sub btnBrowse_Click(sender As Object, e As EventArgs) Handles btnBrowse.Click
Using ofd As OpenFileDialog = New OpenFileDialog() With {.Filter = "Excel 97-2003 Workbook|*.xls|Excel Workbook|*.xlsx"}
If ofd.ShowDialog() = DialogResult.OK Then
txtFileName.Text = ofd.FileName
Using stream = File.Open(ofd.FileName, FileMode.Open, FileAccess.Read)
Using reader As IExcelDataReader = ExcelReaderFactory.CreateReader(stream)
Dim result As DataSet = reader.AsDataSet(New ExcelDataSetConfiguration() With {
.ConfigureDataTable = Function(__) New ExcelDataTableConfiguration() With {
.UseHeaderRow = True}})
tables = result.Tables
cboSheet.Items.Clear()
For Each table As DataTable In tables
cboSheet.Items.Add(table.TableName)
Next
End Using
End Using
End If
End Using
End SubAdding the SelectedIndexChanged event handler to the Combobox control that allows you to select a sheet name, then load data from DataTable to DataGridView based on the sheet name you selected.
Dim dt As DataTable = tables(cboSheet.SelectedItem.ToString())
DataGridView1.DataSource = dt
VIDEO TUTORIAL
