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

To play the demo, you should install ExcelDataReader, ExcelDataReader.DataSet from the Manage Nuget Packages to your project.

Add 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

Add the SelectedIndexChanged event handler to the Combobox 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