How to Read Excel Files Line by Line in VB .NET (Example)

Processing Excel data is a common challenge in business and scientific applications, often involving complex data extraction for reports, database integration, or log parsing. Traditional methods using Microsoft Office Interop are problematic due to their dependency on an installed Excel application, high resource consumption, and compatibility issues across versions. These drawbacks are particularly significant for server-side or cloud deployments where Excel installation is impractical.

This tutorial introduces IronXL, a robust .NET library that overcomes these limitations by eliminating the need for Excel to be installed. We will provide a technical, step-by-step guide on how to use IronXL to efficiently read Excel files line by line within a VB.NET application, offering a powerful and independent solution for developers.

How to Read an Excel File Line by Line in VB.NET

1. Create the VB.NET Console project in Visual Studio

2. Install the Excel library using the NuGet Package Manager

3. Load the Excel file in the program using the Workbook.Load method

4. Read Excel file using Excel library function in the for loop

6. Print Excel file data on a Console

Prerequisites

Before we dive into the code, ensure you have the necessary development environment set up. This will ensure a smooth and efficient coding experience.

Visual Studio Installation

You'll need Visual Studio 2017 or a more recent version installed on your development machine. These versions offer excellent support for .NET development, including advanced debugging tools and integrated NuGet package management, which are crucial for working with external libraries like IronXL.

IronXL Library Installation

IronXL is distributed as a NuGet package, making its installation straightforward. You have two primary methods to add it to your project:

• Using the NuGet Package Manager Console: Within Visual Studio, navigate to Tools > NuGet Package Manager > Package Manager Console.

In the console window, execute the following command:

  Install-Package IronXL.Excel

This command will download the IronXL package and its dependencies, automatically adding the necessary references to your VB.NET project.

• Using the NuGet Package Manager GUI: Alternatively, you can use the graphical interface. Right-click on your project in the Solution Explorer and select Manage NuGet Packages.

In the "Browse" tab of the NuGet Package Manager window, search for IronXL. Locate the package published by "Iron Software" and click the Install button. Follow any prompts to complete the installation.

Once IronXL is successfully installed, you can use it in your project.

Steps to Read an Excel file Line by Line VB.NET

Step 1: Prepare the Excel File

Let’s assume we have a file called employees.xlsx with the following structure:

ID	Name	Department
1	John Doe	IT
2	Jane Smith	HR
3	Sam Brown	Finance

Please save it to your project directory or provide the full path when loading it.

Step 2: Creating Your VB.NET Project

To begin, you'll need a new Visual Basic .NET project.

1. Open Visual Studio.

2. Select "Create a new project".

1. Choose the "Console App (VB.NET)" template. This type of project is perfect for demonstrating file operations without the complexities of a GUI.

1. Give your project a descriptive name, something like "ExcelReaderVB", and click "Create".

This sets up the basic structure for your application, including a Program.vb file where you'll write your code.

Importing the IronXL Namespace

To access IronXL's functionalities, you need to import its namespace into your code file. At the very top of your Module1.vb file (or any other code file where you'll be using IronXL), add the following line:

Imports IronXL

This statement makes all the classes, methods, and properties within the IronXL namespace directly accessible in your code.

Loading the Excel File

Now that IronXL is imported, the first step in processing an Excel document of xlsx format is to load the spreadsheet into the code. You can use the .xls file in the same way.

Dim workbook As WorkBook = WorkBook.Load("employees.xlsx")
Dim sheet As WorkSheet = workbook.DefaultWorkSheet

Working with Excel files is straightforward with IronXL! The line Dim workbook As WorkBook = WorkBook.Load("employees.xlsx") tells IronXL to open and read your Excel workbook, loading all worksheet data into a WorkBook object.

Once loaded, you can access your data:

• Default Sheet: Dim sheet As WorkSheet = workbook.DefaultWorkSheet gets you the first Excel worksheet.

• Specific Sheet: Need a different one? Use workbook.GetWorkSheet("SheetName") by name, or by its index.

Reading the File Row by Row

This is the core of our task: iterating through each row of Excel data.

' Loop through each row using WorkSheet.Rows, which returns IEnumerable(Of Range)
For Each row As Range In sheet.Rows
    Dim id As String = row(0).Text
    Dim name As String = row(1).Text
    Dim department As String = row(2).Text

    Console.WriteLine($"ID: {id}, Name: {name}, Department: {department}")
Next

Here's the technical breakdown:

• For Each row As Range In sheet.Rows: The sheet.Rows property returns an enumerable collection of Range objects. This allows you to use a For Each loop to iterate through every row in the worksheet sequentially. Each row in this loop is a Range object representing a full row.

• Dim id As String = row(0).Text: Each Range object representing a row can be indexed like an array to access individual cells within that row. row(0) refers to the cell in the first column (column A) of the current row. We then access its Text property to get the cell value displayed as a string. It follows the zero-based index.

• Console.WriteLine(...): This line simply prints the extracted data to the console, demonstrating that the data has been successfully read.

This loop will produce the following output:

Skipping Header Rows

It's very common for the first row of an Excel sheet to contain headers rather than data. You'll want to skip this row during processing.

Dim isFirstRow As Boolean = True

For Each row As Range In sheet.Rows
    If isFirstRow Then
        isFirstRow = False
        Continue For ' Skip header row
    End If

    ' Reading columns using index-based access
    Dim id As String = row(0).Text
    Dim name As String = row(1).Text
    Dim department As String = row(2).Text

    Console.WriteLine($"ID: {id}, Name: {name}, Department: {department}")
Next

By introducing a Boolean flag isFirstRow, we can conditionally skip the processing of the first iteration of the loop. Once the first row is encountered, the flag is set to False, and Continue For immediately moves the loop to the next row.

Handling Empty Rows (Optional)

Excel files can sometimes contain empty rows, especially at the bottom of a dataset, which you might want to ignore to prevent errors or process only meaningful data.

You can add a check within your loop:

If String.IsNullOrWhiteSpace(row.Columns(0).ToString()) Then
    Continue For ' Skip this row if the first column is empty or whitespace
End If

This if statement checks if the content of the first column (row. Column (0)) is null, empty, or consists only of whitespace characters. If it is, Continue For is executed, moving to the next row without processing the current one.

Encapsulating Logic into a Function

For better code organization, reusability, and readability, it's good practice to encapsulate your Excel reading logic within a dedicated function.

Imports IronXL

Private Sub ReadExcelFile(filePath As String)
    Try
        Dim workbook As WorkBook = WorkBook.Load(filePath)
        Dim sheet As WorkSheet = workbook.DefaultWorkSheet
        Dim isFirstRow As Boolean = True

        For Each row As Range In sheet.Rows
            If isFirstRow Then
                isFirstRow = False
                Continue For ' Skip header row
            End If

            ' Optional: Skip empty rows based on the first column
            If String.IsNullOrWhiteSpace(row(0).Text) Then
                Continue For
            End If

            Dim id As String = row(0).Text
            Dim name As String = row(1).Text
            Dim department As String = row(2).Text

            Console.WriteLine($"ID: {id}, Name: {name}, Department: {department}")
        Next
    Catch ex As Exception
        Console.WriteLine("Error reading Excel file: " & ex.Message)
    End Try
End Sub

To execute this function from your Main subroutine (the entry point of your console application), call it like this:

Sub Main()
    ReadExcelFile("employees.xlsx")
    Console.WriteLine("Press any key to exit.")
    Console.ReadKey() ' Keep console open until a key is pressed
End Sub

This structure makes your code modular and easier to maintain.

Error Handling

You can wrap your entire file reading logic within a Try...Catch block, as demonstrated in the ReadExcelFile function above:

Try
    ReadExcelFile("employees.xlsx")
Catch ex As Exception
    Console.WriteLine("Error reading Excel file: " & ex.Message)
End Try

This ensures that if an error occurs during file loading or processing, your application won't crash. Instead, it will catch the Exception and print an informative error message to the console.

Conclusion

Reading Excel files line by line in VB.NET is simple with IronXL, giving you precise control for applications like employee trackers or product catalogs. While IronXL handles many low-level complexities, it's still important to write solid logic for edge cases such as empty cells or malformed files. You can explore IronXL's capabilities with a free trial, and commercial licenses are available starting from $749.