Windows Forms: How to Open and Read Excel Files in VB.NET
By FoxLearn 3/22/2024 3:22:53 PM 26.3K
How to read excel file in vb.net windows application
Open your Visual Studio, then create 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
First, You'll need to install the ExcelDataReader
package by right-clicking on your project, then select Manage Nuget Packages. Next, Search and install ExcelDataReader, ExcelDataReader.DataSet to your project.
You can do this via NuGet Package Manager or through the NuGet command-line interface. Here's the NuGet command to install it:
Install-Package ExcelDataReader
In C#, ExcelDataReader is a popular library used for reading data from Excel files. It's particularly useful when you need to work with Excel files without relying on Microsoft Office or Excel itself.
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.
Here's a basic example of how to use ExcelDataReader
to read data from an Excel file in VB.NET.
Add the click event handler to the Browse button that allows you to select the excel file, then read data from the excel file to DataTable and return as a DataSet.
' vb.net connect to excel file ' exceldatareader vb.net 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 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
In this example, We will open a dialog box that will allow us to select the path to the Excel file. The code will open the Excel file, read its contents, and display them to the DataGridView control. You can modify this code to suit your specific needs, such as processing the data in a different way or integrating it into a larger application.
Using ExcelDataReader libray you can easily connect to the excel file, then read excel file in vb.net without oledb.
VIDEO TUTORIAL
- How to protect an excel file
- Windows Forms: How To Open and Read an Excel file into a ListView in C#
- Windows Forms: How to Export DataTable to Excel without Interop in C#
- Windows Forms: Read Excel file in C# using OleDb
- Windows Forms: Read Excel file in C#
- Windows Forms: How to Read Excel file (*.xls, *.xlsx) in C#
- Windows Forms: How to Open and Read Excel Files in C#