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.
Next You can loop over all the Sheets in the Workbook instance. We use a For-loop here.
Detail We access the Worksheet through the Sheets indexer. We access UsedRange, which tells us the entire range that is used on the sheet.
And In a single statement 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.
1 4
2 5
3 6Imports 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 ModuleLength: 6
Dimension 0: 3
Dimension 1: 2
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.
Performance. 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.
Discussion. 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. Microsoft Office interoperation is tricky but makes some important tasks possible. We read in values from an existing spreadsheet in a fast way.
Dot Net Perls is a collection of tested code examples. Pages are continually updated to stay current, with code correctness a top priority.
Sam Allen is passionate about computer languages. In the past, his work has been recommended by Apple and Microsoft and he has studied computers at a selective university in the United States.
This page was last updated on Apr 5, 2023 (simplify).