VB.NET Excel

Green arrow, pointing down

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.

Tip:Add the Microsoft.Office.Interop.Excel assembly.
Go to Project -> Add Reference...

Example

Note

This program requires the Microsoft.Office.Interop.Excel assembly. You should add the Imports statement after adding that assembly. To use Excel, you need to create a new Application instance, and then call Workbooks.Open.

Next:You can loop over all the Sheets in the Workbook instance. We use a For-loop here.

For Loops
Contents of Excel spreadsheet: file.xls

1  4
2  5
3  6

Program that reads Excel spreadsheet: VB.NET

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

Output

Length: 6
Dimension 0: 3
Dimension 1: 2
1 4
2 5
3 6
Squares

Inside the For-loop, we access the Worksheet through the Sheets indexer. We access UsedRange, which tells us the entire range that is used on the sheet. In a single statement, then, we load all the cell data by calling the Value method.

Finally:We loop over the two-dimensional array returned by that method, which is the original cell data.

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. Exception handling is not demonstrated here.

Performance

Performance optimization

Unfortunately, Excel interoperation can be slow. The method here is not. It 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.

Note:The original performance testing is available on the C# language version of this tutorial.

Excel Interop: Microsoft.Office.Interop.Excel

Discussion

Programming tip

The example code in this article is not well factored. It would be better to pull out the code inside Sub Main and use a formal parameter of the file name. Also, it would be better to extract the code inside the For-loop into a function.

Summary

The VB.NET programming language

Microsoft Office interoperation is tricky but makes some important tasks possible. With the VB.NET example code here, we read in values from an existing spreadsheet in a fast way.

Note:The Object instances could be cast to more derived types as well and used in the rest of your program.


VB.NET: File