C-Sharp | Java | Python | Swift | GO | WPF | Ruby | Scala | F# | JavaScript | SQL | PHP | Angular | HTML
Tip: Add the Microsoft.Office.Interop.Excel assembly. Go to Project -> Add Reference.
Next: You can loop over all the Sheets in the Workbook instance. We use a For-loop here.
For Each, ForWorksheet: 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.
ArrayContents of Excel spreadsheet: file.xls
1 4
2 5
3 6
VB.NET program that reads Excel spreadsheet
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
Note: The original performance testing is available on the C# language version of this tutorial.
ExcelNote: The Object instances could be cast to more derived types as well and used in the rest of your program.