Windows Forms: How to Open and Read Excel Files in VB.NET

This post shows you How to Open and Read Excel Files using ExcelDataReader, ExcelDataReader.DataSet in VB.NET Windows Forms Application.

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.

Open and Read Excel Files in VB.NET

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 Sub

Adding 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