Message icon
Skip to footer content
  1. Iron Software
  2. IronXL Blog
  3. Compare to Other Components
  4. Vb Net Read Excel Array
COMPARE TO OTHER COMPONENTS

VB.NET Read Excel File Into Array: IronXL vs Microsoft Interop

Jordi Bardia
Jordi Bardia
Updated:

Reading Excel data into arrays in VB.NET requires loading a workbook, selecting a worksheet, iterating rows and columns, and storing each cell value in a two-dimensional array. With IronXL, you accomplish this in a few lines without installing Microsoft Office. Install the NuGet package, call WorkBook.Load, get a Range, then loop its Rows collection to populate your array.

Start your free trial to explore how IronXL simplifies Excel automation in your Visual Studio projects.

How Do These Solutions Compare at a Glance?

IronXL vs Microsoft Office Interop -- Feature Comparison for VB.NET Array Operations
Feature IronXL Microsoft Office Interop
Office installation required No Yes
NuGet installation Yes -- one package No -- COM reference
Server/CI environment support Yes Limited
COM object lifecycle management Not required Required
Supported formats XLSX, XLS, CSV, TSV, JSON XLSX, XLS (Excel only)
Type-safe API Yes Partial -- late-bound COM
Linux / macOS support Yes (.NET 6+) No
Exception handling style Standard .NET exceptions COM exceptions + manual cleanup

How Do You Install IronXL for VB.NET Projects?

The fastest way to add IronXL to any .NET project is through the NuGet Package Manager. Open the Package Manager Console in Visual Studio and run:

Install-Package IronXL.Excel
Install-Package IronXL.Excel
SHELL

Alternatively, use the .NET CLI:

dotnet add package IronXL.Excel
dotnet add package IronXL.Excel
SHELL

After installation, add Imports IronXL at the top of your VB.NET module. No Office installation is needed -- IronXL reads and writes Excel files entirely through its own parsing engine.

For projects that target older frameworks or require specific version pinning, the IronXL NuGet page lists every published release. The IronXL documentation provides installation guides for Visual Studio 2019, 2022, and the .NET CLI.

How Can Developers Read Excel Data Into Arrays Using IronXL?

IronXL provides a clean API for opening workbooks, selecting ranges, and iterating cell values without requiring a local Office installation. The WorkBook.Load method accepts a file path and returns a WorkBook object that exposes all worksheets.

Imports IronXL

Module ReadExcelToArray
    Sub Main()
        ' Load the Excel workbook from a file path
        Dim workbook As WorkBook = WorkBook.Load("SalesData.xlsx")

        ' Access the first worksheet in the workbook
        Dim sheet As WorkSheet = workbook.DefaultWorkSheet

        ' Define the cell range to extract
        Dim dataRange As IronXL.Range = sheet.GetRange("A1:D5")

        ' Determine array dimensions from the range
        Dim rowCount As Integer = dataRange.Rows.Count
        Dim colCount As Integer = dataRange.Columns.Count
        Dim salesArray(rowCount - 1, colCount - 1) As String

        ' Populate the two-dimensional array from cell values
        Dim rowIndex As Integer = 0
        For Each row As RangeRow In dataRange.Rows
            Dim colIndex As Integer = 0
            For Each cell As Cell In row
                salesArray(rowIndex, colIndex) = cell.StringValue
                colIndex += 1
            Next
            rowIndex += 1
        Next

        ' Print each row to the console
        Console.WriteLine("Data loaded into array:")
        For i As Integer = 0 To rowCount - 1
            For j As Integer = 0 To colCount - 1
                Console.Write(salesArray(i, j) & vbTab)
            Next
            Console.WriteLine()
        Next
    End Sub
End Module
Imports IronXL

Module ReadExcelToArray
    Sub Main()
        ' Load the Excel workbook from a file path
        Dim workbook As WorkBook = WorkBook.Load("SalesData.xlsx")

        ' Access the first worksheet in the workbook
        Dim sheet As WorkSheet = workbook.DefaultWorkSheet

        ' Define the cell range to extract
        Dim dataRange As IronXL.Range = sheet.GetRange("A1:D5")

        ' Determine array dimensions from the range
        Dim rowCount As Integer = dataRange.Rows.Count
        Dim colCount As Integer = dataRange.Columns.Count
        Dim salesArray(rowCount - 1, colCount - 1) As String

        ' Populate the two-dimensional array from cell values
        Dim rowIndex As Integer = 0
        For Each row As RangeRow In dataRange.Rows
            Dim colIndex As Integer = 0
            For Each cell As Cell In row
                salesArray(rowIndex, colIndex) = cell.StringValue
                colIndex += 1
            Next
            rowIndex += 1
        Next

        ' Print each row to the console
        Console.WriteLine("Data loaded into array:")
        For i As Integer = 0 To rowCount - 1
            For j As Integer = 0 To colCount - 1
                Console.Write(salesArray(i, j) & vbTab)
            Next
            Console.WriteLine()
        Next
    End Sub
End Module
Imports IronXL

Module ReadExcelToArray
    Sub Main()
        ' Load the Excel workbook from a file path
        Dim workbook As WorkBook = WorkBook.Load("SalesData.xlsx")

        ' Access the first worksheet in the workbook
        Dim sheet As WorkSheet = workbook.DefaultWorkSheet

        ' Define the cell range to extract
        Dim dataRange As IronXL.Range = sheet.GetRange("A1:D5")

        ' Determine array dimensions from the range
        Dim rowCount As Integer = dataRange.Rows.Count
        Dim colCount As Integer = dataRange.Columns.Count
        Dim salesArray(rowCount - 1, colCount - 1) As String

        ' Populate the two-dimensional array from cell values
        Dim rowIndex As Integer = 0
        For Each row As RangeRow In dataRange.Rows
            Dim colIndex As Integer = 0
            For Each cell As Cell In row
                salesArray(rowIndex, colIndex) = cell.StringValue
                colIndex += 1
            Next
            rowIndex += 1
        Next

        ' Print each row to the console
        Console.WriteLine("Data loaded into array:")
        For i As Integer = 0 To rowCount - 1
            For j As Integer = 0 To colCount - 1
                Console.Write(salesArray(i, j) & vbTab)
            Next
            Console.WriteLine()
        Next
    End Sub
End Module
$vbLabelText   $csharpLabel

Understanding the WorkBook and WorkSheet Objects

WorkBook.Load supports XLSX, XLS, CSV, and TSV files. Once loaded, workbook.DefaultWorkSheet returns the first sheet. You can also access sheets by name with workbook.GetWorkSheet("Sheet1") or by index with workbook.WorkSheets(0).

The GetRange("A1:D5") call returns an IronXL.Range that exposes Rows and Columns collections. Each RangeRow iterates Cell objects, and cell.StringValue returns the display string regardless of the underlying cell type.

How Do You Handle Typed Cell Values?

IronXL cells expose typed properties alongside StringValue:

  • cell.IntValue -- parses the cell as an integer
  • cell.DoubleValue -- parses the cell as a double
  • cell.DateTimeValue -- parses date-formatted cells
  • cell.IsFormula -- indicates whether the cell contains a formula

For financial data, declare the array as Double and assign cell.DoubleValue directly. This avoids string-to-number conversions during downstream processing.

Imports IronXL

Module ReadExcelToDoubleArray
    Sub Main()
        Dim workbook As WorkBook = WorkBook.Load("Revenue.xlsx")
        Dim sheet As WorkSheet = workbook.DefaultWorkSheet
        Dim dataRange As IronXL.Range = sheet.GetRange("B2:E10")

        Dim rowCount As Integer = dataRange.Rows.Count
        Dim colCount As Integer = dataRange.Columns.Count
        Dim revenueArray(rowCount - 1, colCount - 1) As Double

        Dim rowIndex As Integer = 0
        For Each row As RangeRow In dataRange.Rows
            Dim colIndex As Integer = 0
            For Each cell As Cell In row
                revenueArray(rowIndex, colIndex) = cell.DoubleValue
                colIndex += 1
            Next
            rowIndex += 1
        Next

        ' Calculate column totals
        For j As Integer = 0 To colCount - 1
            Dim total As Double = 0
            For i As Integer = 0 To rowCount - 1
                total += revenueArray(i, j)
            Next
            Console.WriteLine($"Column {j + 1} total: {total:C}")
        Next
    End Sub
End Module
Imports IronXL

Module ReadExcelToDoubleArray
    Sub Main()
        Dim workbook As WorkBook = WorkBook.Load("Revenue.xlsx")
        Dim sheet As WorkSheet = workbook.DefaultWorkSheet
        Dim dataRange As IronXL.Range = sheet.GetRange("B2:E10")

        Dim rowCount As Integer = dataRange.Rows.Count
        Dim colCount As Integer = dataRange.Columns.Count
        Dim revenueArray(rowCount - 1, colCount - 1) As Double

        Dim rowIndex As Integer = 0
        For Each row As RangeRow In dataRange.Rows
            Dim colIndex As Integer = 0
            For Each cell As Cell In row
                revenueArray(rowIndex, colIndex) = cell.DoubleValue
                colIndex += 1
            Next
            rowIndex += 1
        Next

        ' Calculate column totals
        For j As Integer = 0 To colCount - 1
            Dim total As Double = 0
            For i As Integer = 0 To rowCount - 1
                total += revenueArray(i, j)
            Next
            Console.WriteLine($"Column {j + 1} total: {total:C}")
        Next
    End Sub
End Module
IRON VB CONVERTER ERROR developers@ironsoftware.com
$vbLabelText   $csharpLabel

Output

VB .NET Read Excel File Into Array: IronXL vs Microsoft Interop Comparison: Image 1 - IronXL vs. Office Excel for doing VB .NET read Excel File into array

This code example demonstrates IronXL's intuitive API. The WorkBook.Load method opens the Excel file directly from your designated folder, while the Range object provides access to specific cell regions within the Excel spreadsheet. Unlike legacy VBA macros, this approach is fully type-safe and integrated into the .NET ecosystem.

Get stated with IronXL now.
green arrow pointer

What Is the Traditional Microsoft Office Interop Approach?

Microsoft Office Interop relies on COM automation to connect with an Excel application instance. To set this up in Visual Studio, navigate to the Project menu, select "Add Reference," and search for the Microsoft Excel Object Library under the COM tab. A local Excel installation must exist on every machine that runs the code.

Imports Microsoft.Office.Interop.Excel

Module InteropExcelArray
    Sub Main()
        Dim excelApp As New Application()
        Dim workbooks As Workbooks = excelApp.Workbooks
        Dim workbook As Workbook = Nothing
        Dim sheet As Worksheet = Nothing

        Try
            ' Suppress screen updates during processing
            excelApp.ScreenUpdating = False

            ' Open the workbook by full file path
            workbook = workbooks.Open("C:\Data\SalesData.xlsx")

            ' Reference the first worksheet
            sheet = CType(workbook.Sheets(1), Worksheet)

            ' Define a range and pull values into an object array
            Dim dataRange As Range = sheet.Range("A1", "D5")
            Dim values(,) As Object = CType(dataRange.Value, Object(,))

            ' COM arrays are 1-based, so enumerate from index 1
            Dim rows As Integer = values.GetUpperBound(0)
            Dim columns As Integer = values.GetUpperBound(1)

            For i As Integer = 1 To rows
                Dim line As String = ""
                For j As Integer = 1 To columns
                    line &= values(i, j).ToString() & vbTab
                Next
                Console.WriteLine(line)
            Next

        Catch ex As Exception
            Console.WriteLine("Error: " & ex.Message)

        Finally
            ' Release COM objects to prevent orphaned Excel processes
            If sheet IsNot Nothing Then
                System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet)
            End If
            If workbook IsNot Nothing Then
                workbook.Close(False)
                System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook)
            End If
            excelApp.Quit()
            System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp)
        End Try
    End Sub
End Module
Imports Microsoft.Office.Interop.Excel

Module InteropExcelArray
    Sub Main()
        Dim excelApp As New Application()
        Dim workbooks As Workbooks = excelApp.Workbooks
        Dim workbook As Workbook = Nothing
        Dim sheet As Worksheet = Nothing

        Try
            ' Suppress screen updates during processing
            excelApp.ScreenUpdating = False

            ' Open the workbook by full file path
            workbook = workbooks.Open("C:\Data\SalesData.xlsx")

            ' Reference the first worksheet
            sheet = CType(workbook.Sheets(1), Worksheet)

            ' Define a range and pull values into an object array
            Dim dataRange As Range = sheet.Range("A1", "D5")
            Dim values(,) As Object = CType(dataRange.Value, Object(,))

            ' COM arrays are 1-based, so enumerate from index 1
            Dim rows As Integer = values.GetUpperBound(0)
            Dim columns As Integer = values.GetUpperBound(1)

            For i As Integer = 1 To rows
                Dim line As String = ""
                For j As Integer = 1 To columns
                    line &= values(i, j).ToString() & vbTab
                Next
                Console.WriteLine(line)
            Next

        Catch ex As Exception
            Console.WriteLine("Error: " & ex.Message)

        Finally
            ' Release COM objects to prevent orphaned Excel processes
            If sheet IsNot Nothing Then
                System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet)
            End If
            If workbook IsNot Nothing Then
                workbook.Close(False)
                System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook)
            End If
            excelApp.Quit()
            System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp)
        End Try
    End Sub
End Module
Imports Microsoft.Office.Interop.Excel

Module InteropExcelArray
    Sub Main()
        Dim excelApp As New Application()
        Dim workbooks As Workbooks = excelApp.Workbooks
        Dim workbook As Workbook = Nothing
        Dim sheet As Worksheet = Nothing

        Try
            ' Suppress screen updates during processing
            excelApp.ScreenUpdating = False

            ' Open the workbook by full file path
            workbook = workbooks.Open("C:\Data\SalesData.xlsx")

            ' Reference the first worksheet
            sheet = CType(workbook.Sheets(1), Worksheet)

            ' Define a range and pull values into an object array
            Dim dataRange As Range = sheet.Range("A1", "D5")
            Dim values(,) As Object = CType(dataRange.Value, Object(,))

            ' COM arrays are 1-based, so enumerate from index 1
            Dim rows As Integer = values.GetUpperBound(0)
            Dim columns As Integer = values.GetUpperBound(1)

            For i As Integer = 1 To rows
                Dim line As String = ""
                For j As Integer = 1 To columns
                    line &= values(i, j).ToString() & vbTab
                Next
                Console.WriteLine(line)
            Next

        Catch ex As Exception
            Console.WriteLine("Error: " & ex.Message)

        Finally
            ' Release COM objects to prevent orphaned Excel processes
            If sheet IsNot Nothing Then
                System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet)
            End If
            If workbook IsNot Nothing Then
                workbook.Close(False)
                System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook)
            End If
            excelApp.Quit()
            System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp)
        End Try
    End Sub
End Module
$vbLabelText   $csharpLabel

Why Does COM Object Management Matter?

The Interop approach requires careful attention to COM object lifecycle management. Every object returned by the Excel application -- workbooks, worksheets, ranges, and cells -- holds a reference-counted COM pointer. Failing to call Marshal.ReleaseComObject on each of these objects leaves an EXCEL.EXE process running silently in the background.

On servers, orphaned processes accumulate over time and eventually exhaust available memory or file handles. The Try...Finally pattern above is the minimum safe approach, but many enterprise codebases add a dedicated COM cleanup utility to handle nested object graphs.

The Interop array indexing convention also differs from standard VB.NET arrays: COM-based arrays start at index 1, not 0. This means any row or column loop must account for the off-by-one difference, which is a common source of runtime errors and data truncation bugs.

Which Solution Offers Better Developer Experience?

When evaluating these approaches for production use, several factors favor IronXL as the preferred solution:

Deployment Simplicity: IronXL installs via a single NuGet package, while Interop requires complex environment configurations. This matters when your program needs to process an XLSX file on a build server or cloud function where no user session exists.

Code Maintainability: Reading a data table through IronXL keeps the code short and readable. Row and column counts are available as first-class properties. With Interop, you calculate upper bounds on 1-indexed COM arrays and cast late-bound Object values manually.

Format Flexibility: Beyond standard Excel workbook formats like XLSX, IronXL natively supports CSV parsing, which is useful when test data arrives in multiple formats. The library can also create new workbook files and write values back to cells with equal simplicity. See the IronXL format guide for a full list.

Error Handling: Standard .NET exception patterns apply to IronXL, making error recovery straightforward. COM-based exceptions require additional handling logic and can leave Excel instances running if not properly managed.

Cross-Platform Support: IronXL runs on Linux and macOS via .NET 6 and later. Interop is Windows-only because it depends on the Win32 COM subsystem.

Performance and Maintenance Trade-offs for Array Reading in VB.NET
Criterion IronXL Microsoft Interop
Lines of code for basic array read ~20 ~40+
Manual COM cleanup required No Yes
Works without Excel installed Yes No
Runs in Docker / CI pipelines Yes No
Array indexing convention 0-based (.NET standard) 1-based (COM standard)

How Do You Work with Dynamic Excel Ranges in VB.NET?

Production spreadsheets rarely have a fixed number of rows. IronXL provides the UsedRange property on each worksheet, which returns the bounding rectangle of all non-empty cells. You can use this instead of a hard-coded range string.

Imports IronXL

Module DynamicRangeExample
    Sub Main()
        Dim workbook As WorkBook = WorkBook.Load("DynamicData.xlsx")
        Dim sheet As WorkSheet = workbook.DefaultWorkSheet

        ' Get the bounding range of all populated cells
        Dim usedRange As IronXL.Range = sheet.UsedRange

        Dim rowCount As Integer = usedRange.Rows.Count
        Dim colCount As Integer = usedRange.Columns.Count
        Dim dynamicArray(rowCount - 1, colCount - 1) As String

        Dim rowIndex As Integer = 0
        For Each row As RangeRow In usedRange.Rows
            Dim colIndex As Integer = 0
            For Each cell As Cell In row
                dynamicArray(rowIndex, colIndex) = cell.StringValue
                colIndex += 1
            Next
            rowIndex += 1
        Next

        Console.WriteLine($"Loaded {rowCount} rows x {colCount} columns from UsedRange.")
    End Sub
End Module
Imports IronXL

Module DynamicRangeExample
    Sub Main()
        Dim workbook As WorkBook = WorkBook.Load("DynamicData.xlsx")
        Dim sheet As WorkSheet = workbook.DefaultWorkSheet

        ' Get the bounding range of all populated cells
        Dim usedRange As IronXL.Range = sheet.UsedRange

        Dim rowCount As Integer = usedRange.Rows.Count
        Dim colCount As Integer = usedRange.Columns.Count
        Dim dynamicArray(rowCount - 1, colCount - 1) As String

        Dim rowIndex As Integer = 0
        For Each row As RangeRow In usedRange.Rows
            Dim colIndex As Integer = 0
            For Each cell As Cell In row
                dynamicArray(rowIndex, colIndex) = cell.StringValue
                colIndex += 1
            Next
            rowIndex += 1
        Next

        Console.WriteLine($"Loaded {rowCount} rows x {colCount} columns from UsedRange.")
    End Sub
End Module
IRON VB CONVERTER ERROR developers@ironsoftware.com
$vbLabelText   $csharpLabel

How Do You Filter and Validate Array Data After Loading?

After populating the array, common post-processing steps include:

  • Skip header rows: Start the display loop at index 1 instead of 0 to omit column headers.
  • Trim whitespace: Call .Trim() on cell.StringValue to remove leading and trailing spaces copied from the spreadsheet.
  • Handle null cells: IronXL returns an empty string for blank cells, so null checks are unnecessary in most cases.
  • Validate numeric ranges: After loading doubles, apply a range check before storing to catch cells that contain placeholder values like -1 or 9999.

For larger datasets, consider using List(Of T) or a strongly typed class instead of a raw two-dimensional array. Loading data into typed objects makes downstream code easier to read and test. The IronXL object model reference documents all available cell properties and worksheet methods.

How Do You Export a VB.NET Array Back to Excel?

IronXL supports writing array data back to a worksheet with the same API used for reading. This is useful for scenarios where you transform the data in memory and need to save the result as a new Excel file.

Imports IronXL

Module WriteArrayToExcel
    Sub Main()
        ' Create a new workbook and worksheet
        Dim workbook As WorkBook = WorkBook.Create(ExcelFileFormat.XLSX)
        Dim sheet As WorkSheet = workbook.CreateWorkSheet("Results")

        Dim outputArray(,) As String = {
            {"Region", "Q1", "Q2", "Q3"},
            {"North", "12500", "14200", "15800"},
            {"South", "9800", "10500", "11300"},
            {"East", "8200", "9100", "9900"}
        }

        ' Write the array contents to the worksheet cell by cell
        For i As Integer = 0 To outputArray.GetUpperBound(0)
            For j As Integer = 0 To outputArray.GetUpperBound(1)
                Dim cellAddress As String = IronXL.ExcelAddress.ToAddress(i, j)
                sheet(cellAddress).Value = outputArray(i, j)
            Next
        Next

        workbook.SaveAs("Output.xlsx")
        Console.WriteLine("Workbook saved as Output.xlsx")
    End Sub
End Module
Imports IronXL

Module WriteArrayToExcel
    Sub Main()
        ' Create a new workbook and worksheet
        Dim workbook As WorkBook = WorkBook.Create(ExcelFileFormat.XLSX)
        Dim sheet As WorkSheet = workbook.CreateWorkSheet("Results")

        Dim outputArray(,) As String = {
            {"Region", "Q1", "Q2", "Q3"},
            {"North", "12500", "14200", "15800"},
            {"South", "9800", "10500", "11300"},
            {"East", "8200", "9100", "9900"}
        }

        ' Write the array contents to the worksheet cell by cell
        For i As Integer = 0 To outputArray.GetUpperBound(0)
            For j As Integer = 0 To outputArray.GetUpperBound(1)
                Dim cellAddress As String = IronXL.ExcelAddress.ToAddress(i, j)
                sheet(cellAddress).Value = outputArray(i, j)
            Next
        Next

        workbook.SaveAs("Output.xlsx")
        Console.WriteLine("Workbook saved as Output.xlsx")
    End Sub
End Module
Imports IronXL

Module WriteArrayToExcel
    Sub Main()
        ' Create a new workbook and worksheet
        Dim workbook As WorkBook = WorkBook.Create(ExcelFileFormat.XLSX)
        Dim sheet As WorkSheet = workbook.CreateWorkSheet("Results")

        Dim outputArray(,) As String = {
            {"Region", "Q1", "Q2", "Q3"},
            {"North", "12500", "14200", "15800"},
            {"South", "9800", "10500", "11300"},
            {"East", "8200", "9100", "9900"}
        }

        ' Write the array contents to the worksheet cell by cell
        For i As Integer = 0 To outputArray.GetUpperBound(0)
            For j As Integer = 0 To outputArray.GetUpperBound(1)
                Dim cellAddress As String = IronXL.ExcelAddress.ToAddress(i, j)
                sheet(cellAddress).Value = outputArray(i, j)
            Next
        Next

        workbook.SaveAs("Output.xlsx")
        Console.WriteLine("Workbook saved as Output.xlsx")
    End Sub
End Module
$vbLabelText   $csharpLabel

The ExcelAddress.ToAddress(row, column) helper converts zero-based integer coordinates to A1-notation strings. This keeps the write loop clean and avoids manually computing cell addresses. Review the IronXL writing tutorial for more patterns including formula injection and style application.

How Do You Read Multiple Worksheets Into Separate Arrays?

Some workbooks store data across multiple sheets -- for example, one sheet per month or per region. IronXL exposes all sheets through workbook.WorkSheets, which you can iterate to load each sheet into its own array.

Imports IronXL

Module MultiSheetArrayLoader
    Sub Main()
        Dim workbook As WorkBook = WorkBook.Load("AnnualReport.xlsx")

        For Each sheet As WorkSheet In workbook.WorkSheets
            Console.WriteLine($"Loading sheet: {sheet.Name}")

            Dim dataRange As IronXL.Range = sheet.UsedRange
            Dim rowCount As Integer = dataRange.Rows.Count
            Dim colCount As Integer = dataRange.Columns.Count
            Dim sheetArray(rowCount - 1, colCount - 1) As String

            Dim rowIndex As Integer = 0
            For Each row As RangeRow In dataRange.Rows
                Dim colIndex As Integer = 0
                For Each cell As Cell In row
                    sheetArray(rowIndex, colIndex) = cell.StringValue
                    colIndex += 1
                Next
                rowIndex += 1
            Next

            Console.WriteLine($"  Loaded {rowCount} rows x {colCount} columns.")
        Next
    End Sub
End Module
Imports IronXL

Module MultiSheetArrayLoader
    Sub Main()
        Dim workbook As WorkBook = WorkBook.Load("AnnualReport.xlsx")

        For Each sheet As WorkSheet In workbook.WorkSheets
            Console.WriteLine($"Loading sheet: {sheet.Name}")

            Dim dataRange As IronXL.Range = sheet.UsedRange
            Dim rowCount As Integer = dataRange.Rows.Count
            Dim colCount As Integer = dataRange.Columns.Count
            Dim sheetArray(rowCount - 1, colCount - 1) As String

            Dim rowIndex As Integer = 0
            For Each row As RangeRow In dataRange.Rows
                Dim colIndex As Integer = 0
                For Each cell As Cell In row
                    sheetArray(rowIndex, colIndex) = cell.StringValue
                    colIndex += 1
                Next
                rowIndex += 1
            Next

            Console.WriteLine($"  Loaded {rowCount} rows x {colCount} columns.")
        Next
    End Sub
End Module
Imports IronXL

Module MultiSheetArrayLoader
    Sub Main()
        Dim workbook As WorkBook = WorkBook.Load("AnnualReport.xlsx")

        For Each sheet As WorkSheet In workbook.WorkSheets
            Console.WriteLine($"Loading sheet: {sheet.Name}")

            Dim dataRange As IronXL.Range = sheet.UsedRange
            Dim rowCount As Integer = dataRange.Rows.Count
            Dim colCount As Integer = dataRange.Columns.Count
            Dim sheetArray(rowCount - 1, colCount - 1) As String

            Dim rowIndex As Integer = 0
            For Each row As RangeRow In dataRange.Rows
                Dim colIndex As Integer = 0
                For Each cell As Cell In row
                    sheetArray(rowIndex, colIndex) = cell.StringValue
                    colIndex += 1
                Next
                rowIndex += 1
            Next

            Console.WriteLine($"  Loaded {rowCount} rows x {colCount} columns.")
        Next
    End Sub
End Module
$vbLabelText   $csharpLabel

Understanding Sheet Navigation

The workbook.WorkSheets collection is zero-indexed and supports both For Each and indexed access. Use sheet.Name to identify sheets programmatically and branch logic based on known sheet names. The IronXL worksheet guide covers range selection, named ranges, and dynamic range detection in detail.

For workbooks with dozens of sheets, filter by name before loading to avoid processing irrelevant sheets and wasting memory.

What Should Developers Know About the Interop Array Format?

The Interop approach returns cell data as a Object(,) array using COM's 1-based indexing. The first element in the first row is at values(1, 1), not values(0, 0). This is a frequent source of off-by-one errors.

An additional concern is null handling: Interop returns Nothing for empty cells. Calling .ToString() on a null reference throws a NullReferenceException at runtime. You must add a null guard around every cell access:

If values(i, j) IsNot Nothing Then
    line &= values(i, j).ToString() & vbTab
End If
If values(i, j) IsNot Nothing Then
    line &= values(i, j).ToString() & vbTab
End If
If values(i, j) IsNot Nothing Then
    line &= values(i, j).ToString() & vbTab
End If
$vbLabelText   $csharpLabel

IronXL eliminates this problem by returning an empty string for blank cells, which means the loop code works without any additional guards.

For authoritative information on the Excel Object Model used by Interop, refer to the Microsoft Excel VBA reference and the MSDN Office interop documentation.

What Are Your Next Steps?

Reading Excel data into arrays is one of the most common .NET spreadsheet tasks, and IronXL makes the implementation straightforward. To take the next step:

  • Download IronXL: Install via NuGet (Install-Package IronXL.Excel) and follow the getting started guide.
  • Explore format support: IronXL reads and writes XLSX, XLS, CSV, TSV, and JSON. See the supported formats page for details.
  • Try advanced features: Sort ranges, apply formulas, set cell styles, and generate charts -- all without Excel installed. Review the IronXL feature overview for the full capability list.
  • Browse code examples: The IronXL code examples library provides copy-paste snippets for dozens of common spreadsheet tasks.
  • Review licensing: For production deployments, IronXL licensing offers developer, team, and OEM tiers.

VB .NET Read Excel File Into Array: IronXL vs Microsoft Interop Comparison: Image 2 - IronXL output

Both IronXL and Microsoft Office Interop can read Excel data into arrays, but IronXL delivers a superior developer experience through its Office-independent architecture, cleaner API, and flexible deployment options. The library removes common pain points like COM object management and system dependencies while providing access to advanced features for spreadsheet automation.

Explore the full capabilities of IronXL with sample projects, resources, and documentation at the IronXL documentation hub. For enterprise applications, IronXL licensing options provide flexible terms to fit any project scope.

Frequently Asked Questions

What is the best way to read Excel files into arrays using VB.NET?

Reading Excel files into arrays in VB.NET can be effectively achieved using IronXL or Microsoft Office Interop. IronXL provides a simplified and efficient approach, eliminating the need for Microsoft Excel installation and reducing the complexity of the code.

How does IronXL compare to Microsoft Office Interop for reading Excel files?

IronXL offers a more streamlined and faster method for reading Excel files into arrays compared to Microsoft Office Interop. It does not require Excel to be installed on the server and handles Excel file manipulation with less overhead.

Can I manipulate Excel data without installing Microsoft Excel?

Yes, using IronXL allows you to manipulate Excel data without needing Microsoft Excel installed. This makes it a great option for server environments where installing Excel is not feasible.

Why should I consider using IronXL for Excel operations in VB.NET?

IronXL simplifies Excel operations by providing an intuitive API that handles various Excel file formats. It ensures faster performance and easier deployment, especially in environments where Microsoft Excel cannot be installed.

Is IronXL suitable for handling large Excel data sets in VB.NET?

Yes, IronXL is optimized for performance and can efficiently handle large Excel data sets, providing quick data extraction and manipulation.

What are the code examples available for reading Excel files into arrays using VB.NET?

The guide provides working code examples for both IronXL and Microsoft Office Interop, demonstrating how to read Excel files into arrays effectively in VB.NET.

Does IronXL support different Excel file formats?

IronXL supports a wide range of Excel file formats, including XLSX, XLS, CSV, and more, making it versatile for various data processing needs.

What are the benefits of using IronXL over Microsoft Office Interop?

IronXL is easier to use, does not require Excel to be installed, and offers better performance. It simplifies the code and reduces potential errors associated with COM interactions in Microsoft Office Interop.

How does IronXL handle two-dimensional arrays differently from Microsoft Interop?

IronXL provides direct methods to convert Excel data into two-dimensional arrays, ensuring a more efficient and straightforward approach compared to the more complex setup required with Microsoft Interop.

What best practices are recommended for reading Excel files into arrays?

Best practices include choosing the right tool for your environment, such as IronXL for its ease of use and performance, and ensuring efficient code structure to manage memory and processing power effectively.

Jordi Bardia
Jordi Bardia
  Chat with engineering team now
Software Engineer
Jordi is most proficient in Python, C# and C++, when he isn’t leveraging his skills at Iron Software; he’s game programming. Sharing responsibilities for product testing, product development and research, Jordi adds immense value to continual product improvement. The varied experience keeps him challenged and engaged, and he ...
Read More

Related Articles

30-day trial → Full product. No limits. No card. Start Free Trial

Iron Support Team

We're online 24 hours, 5 days a week.
Chat
Email
Call Me