How to Open and Read Excel Files in VB.NET

How to read excel file in vb.net windows application?

Open your Visual Studio, then create a new Windows Forms Application project.

Next, you can 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
Install-Package ExcelDataReader.DataSet

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
' vb.net open excel file using 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
            ' excel net vb file
            Using stream = File.Open(ofd.FileName, FileMode.Open, FileAccess.Read)
                ' vb net read excel file to datatable
                Using reader As IExcelDataReader = ExcelReaderFactory.CreateReader(stream)
                    ' vb.net read excel file into datatable
                    Dim result As DataSet = reader.AsDataSet(New ExcelDataSetConfiguration() With {
                                                                 .ConfigureDataTable = Function(__) New ExcelDataTableConfiguration() With {
                                                                 .UseHeaderRow = True}})
                    tables = result.Tables
                    cboSheet.Items.Clear()
                    ' vb.net open excel sheet
                    For Each table As DataTable In tables
                        cboSheet.Items.Add(table.TableName)
                    Next
                End Using
            End Using
        End If
    End Using
End Sub

Use the ExcelReaderFactory to create an instance of the ExcelDataReader, then open the Excel file and read its contents using the ExcelDataReader instance.

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.

If you encounter an error when reading excel file in vb.net as below

No data is available for encoding 1252

You can modify your vb.net code as shown below.

' vb.net open excel sheet
Private Sub btnBrowser_Click(sender As Object, e As EventArgs) Handles btnBrowse.Click
        Using ofd As OpenFileDialog = New OpenFileDialog() With {.Filter = "Excel Workbook|*.xlsx|Excel 97-2003 Workbook|*.xls"}
            If ofd.ShowDialog() = DialogResult.OK Then
                txtFileName.Text = ofd.FileName
                System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance)
                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

Just add this line before File.Open

System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance)

If you want to read an Excel file line by line in VB.NET, you can use the Microsoft.Office.Interop.Excel library, which allows you to work with Excel files.

First, You need to ddd a reference to the Microsoft Excel Object Library in your project, then use the Excel.Application to open the workbook and then iterate through each row.

For example, vb net read excel file line by line

Imports Microsoft.Office.Interop.Excel

Module Module1
    Sub Main()
        ' Create Excel application object
        Dim excelApp As New Application()
        ' Open the Excel file
        Dim workbook As Workbook = excelApp.Workbooks.Open("C:\path\to\your\file.xlsx")
        ' Get the first worksheet
        Dim worksheet As Worksheet = workbook.Sheets(1)
        ' Get the last row in the worksheet
        Dim lastRow As Integer = worksheet.Cells(worksheet.Rows.Count, 1).End(XlDirection.xlUp).Row

        ' Loop through rows starting from the first row
        For i As Integer = 1 To lastRow
            ' Read values in each row (example for column 1, 2, etc.)
            Dim value1 As String = worksheet.Cells(i, 1).Value
            Dim value2 As String = worksheet.Cells(i, 2).Value
            ' Print or process the row data
            Console.WriteLine($"Row {i}: {value1}, {value2}")
        Next

        ' Clean up
        workbook.Close(False)
        excelApp.Quit()
        ReleaseObject(worksheet)
        ReleaseObject(workbook)
        ReleaseObject(excelApp)
    End Sub

    ' Function to release COM objects
    Private Sub ReleaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub
End Module

VIDEO TUTORIAL