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

How Do These Solutions Compare at a Glance?

How Can Developers Read Excel Data Into Arrays Using IronXL?

IronXL is a .NET library that provides a streamlined approach to automate Excel operations without requiring a local Office installation. To utilize this library in your program, simply follow the following steps: install the package via NuGet, load your workbook, and access the default worksheet.

Imports IronXL
Module ReadExcelToArray
    Sub Main()
        ' Load the Excel workbook from 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 range object to read from
         Dim dataRange As IronXL.Range = sheet.GetRange("A1:D5")
        ' Create a two dimensional array to store values
        Dim rowCount As Integer = dataRange.Rows.Count
        Dim colCount As Integer = dataRange.Columns.Count
        Dim myArray(rowCount - 1, colCount - 1) As String
        ' Fill the array with cell values from each worksheet row
        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
                myArray(rowIndex, colIndex) = cell.StringValue
                colIndex += 1
            Next
            rowIndex += 1
        Next
        ' Display the string data from the array
        Console.WriteLine("Data loaded into array:")
        For i As Integer = 0 To rowCount - 1
            For j As Integer = 0 To colCount - 1
                Console.Write(myArray(i, j) & vbTab)
            Next
            Console.WriteLine()
        Next
    End Sub
End Module

Imports IronXL
Module ReadExcelToArray
    Sub Main()
        ' Load the Excel workbook from 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 range object to read from
         Dim dataRange As IronXL.Range = sheet.GetRange("A1:D5")
        ' Create a two dimensional array to store values
        Dim rowCount As Integer = dataRange.Rows.Count
        Dim colCount As Integer = dataRange.Columns.Count
        Dim myArray(rowCount - 1, colCount - 1) As String
        ' Fill the array with cell values from each worksheet row
        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
                myArray(rowIndex, colIndex) = cell.StringValue
                colIndex += 1
            Next
            rowIndex += 1
        Next
        ' Display the string data from the array
        Console.WriteLine("Data loaded into array:")
        For i As Integer = 0 To rowCount - 1
            For j As Integer = 0 To colCount - 1
                Console.Write(myArray(i, j) & vbTab)
            Next
            Console.WriteLine()
        Next
    End Sub
End Module

net

Output

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.

Start for Free

What Is the Traditional Microsoft Excel Approach for Excel Array Operations?

Microsoft Office Interop relies on COM automation to connect with an Excel application instance. To set this up in Visual Studio, you must navigate to the Project Menu, select "Add Reference," and perform a query for the Microsoft Excel Object Library under the COM tab.

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
            ' Disable screen updating for better performance
            excelApp.ScreenUpdating = False
            ' Open the Excel workbook by filename
            workbook = workbooks.Open("C:\Data\SalesData.xlsx")
            ' Get the first sheet from the workbook
            sheet = CType(workbook.Sheets(1), Worksheet)
            ' Create a range object for the data area
            Dim dataRange As Range = sheet.Range("A1", "D5")
            ' Read values into a two dimensional array
            Dim values(,) As Object = CType(dataRange.Value, Object(,))
            ' Process the array dimensions
            Dim rows As Integer = values.GetUpperBound(0)
            Dim columns As Integer = values.GetUpperBound(1)
            ' Write output to console
            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
            ' Clean up COM objects to prevent memory leaks
            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
            ' Disable screen updating for better performance
            excelApp.ScreenUpdating = False
            ' Open the Excel workbook by filename
            workbook = workbooks.Open("C:\Data\SalesData.xlsx")
            ' Get the first sheet from the workbook
            sheet = CType(workbook.Sheets(1), Worksheet)
            ' Create a range object for the data area
            Dim dataRange As Range = sheet.Range("A1", "D5")
            ' Read values into a two dimensional array
            Dim values(,) As Object = CType(dataRange.Value, Object(,))
            ' Process the array dimensions
            Dim rows As Integer = values.GetUpperBound(0)
            Dim columns As Integer = values.GetUpperBound(1)
            ' Write output to console
            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
            ' Clean up COM objects to prevent memory leaks
            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
            ' Disable screen updating for better performance
            excelApp.ScreenUpdating = False
            ' Open the Excel workbook by filename
            workbook = workbooks.Open("C:\Data\SalesData.xlsx")
            ' Get the first sheet from the workbook
            sheet = CType(workbook.Sheets(1), Worksheet)
            ' Create a range object for the data area
            Dim dataRange As Range = sheet.Range("A1", "D5")
            ' Read values into a two dimensional array
            Dim values(,) As Object = CType(dataRange.Value, Object(,))
            ' Process the array dimensions
            Dim rows As Integer = values.GetUpperBound(0)
            Dim columns As Integer = values.GetUpperBound(1)
            ' Write output to console
            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
            ' Clean up COM objects to prevent memory leaks
            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

The Interop approach requires careful attention to COM object lifecycle management. Developers must manually delete or release each reference to avoid orphaned Excel processes hanging in the background.

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 NuGet query, while Interop requires complex environment configurations. This is critical when your program needs to read an XLSX file on a server where no user is logged in.

Code Maintainability: Handling a data table via IronXL reduces the complexity of managing system documents. Obtaining an accurate row count is a built-in property, whereas Interop requires calculating upper bounds on objects.

Format Flexibility: Beyond standard Excel workbook formats like XLSX, IronXL natively supports CSV parsing, 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.

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

For developers seeking comprehensive Excel file manipulation capabilities, IronXL provides the correct balance of power and simplicity.

Conclusion

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 robust deployment options. The library eliminates 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 here. For enterprise applications, licensing options provide flexible terms to fit any project scope.

Purchase a license to unlock IronXL's full potential for your production applications.