Excel

An Excel spreadsheet can be used in a VB.NET program. With the Microsoft.Office.Interop.Excel assembly, we use Excel spreadsheets and data.

Some approaches yield better performance than others. It is important to use an optimized method. Add the Microsoft.Office.Interop.Excel assembly. Go to Project and then Add Reference.

Example

First import the Microsoft.Office.Interop.Excel assembly. Next add the Imports statement. To use Excel, you need to create a new Application instance, and then call Workbooks.Open.

Imports Microsoft.Office.Interop.Excel

Module Module1
    Sub Main()
        ' Create new Application.
        Dim excel As Application = New Application

        ' Open Excel spreadsheet.
        Dim w As Workbook = excel.Workbooks.Open("C:\file.xls")

        ' Loop over all sheets.
        For i As Integer = 1 To w.Sheets.Count

            ' Get sheet.
            Dim sheet As Worksheet = w.Sheets(i)

            ' Get range.
            Dim r As Range = sheet.UsedRange

            ' Load all cells into 2d array.
            Dim array(,) As Object = r.Value(XlRangeValueDataType.xlRangeValueDefault)

            ' Scan the cells.
            If array IsNot Nothing Then
                Console.WriteLine("Length: {0}", array.Length)

                ' Get bounds of the array.
                Dim bound0 As Integer = array.GetUpperBound(0)
                Dim bound1 As Integer = array.GetUpperBound(1)

                Console.WriteLine("Dimension 0: {0}", bound0)
                Console.WriteLine("Dimension 1: {0}", bound1)

                ' Loop over all elements.
                For j As Integer = 1 To bound0
                    For x As Integer = 1 To bound1
                        Dim s1 As String = array(j, x)
                        Console.Write(s1)
                        Console.Write(" "c)
                    Next
                    Console.WriteLine()
                Next
            End If
        Next

        ' Close.
        w.Close()
    End Sub
End Module
Length: 6
Dimension 0: 3
Dimension 1: 2
1 4
2 5
3 6
1  4
2  5
3  6

Exceptions

In a deployed program, exception handling should be used around all file accesses or Excel interoperation calls. These operations are more error-prone than many other procedures.

Discussion

This method minimizes the number of calls to EXCEL.EXE by loading all the cell data in one statement. This results in a speedup over trying to individually read in cells.

Microsoft Office interoperation is tricky but makes some important tasks possible. We read in values from an existing spreadsheet in a fast way.