VB.NET - Reading ENTIRE content of an excel file

Question

I already know the basic procedure of how to read specific cells of an .xls file using VB.NET but I can't figure out how to automatically get all the data from such a file.

Basically, I'm obtaining the data like this:

Dim xlApp As Excel.Application
Dim wb As Workbook
Dim ws As Worksheet
xlApp = New Excel.Application
wb = xlApp.Workbooks.Open("myfile.xls")
ws = wb.Worksheets("worksheet1")
For Each cell In ws.Range("A1", "C10")
     Console.WriteLine(cell.value)
Next

In this case, I know that there will be content in the first 10 rows of the columns A, B and C but what would I have to do if I had a huge document whose size and content might even change from time to time? I know that there is also an attribute ws.rows.count but this always returns a big value like 60000 even if only a few rows are occupied.

So basically, I'm looking for a simple way to loop through all the used rows of an Excel file with the possibility of accessing each cell in that row.

Answer 1

in VBA - as distinct from VB.NEt (so I may be off target here) you would either use

ActiveSheet.UsedRange
MsgBox ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row

(the first line as needed to "reset" the UsedRange, SpecialCells(xlCellTypeLastCell) will often give a larger range than is actually present), or more commonly

or

Dim rng1 As Range
Set rng1 = ActiveSheet.Cells.Find("*", [a1], xlValues, , xlByRows, xlPrevious)
If Not rng1 Is Nothing Then MsgBox rng1.Row

Answer 2

Dim aDataTemp = xl.Worksheets(sSheetName).UsedRange.value

Answer 3

Still in VBA, we often use the End statement for this kind of issue:

ws.Cells(Rows.Count, "A").End(xlUp).Row

Change the column name or number to fit your needs (or find it in the same way as the last row with End(xlLeft)

Answer 4

Here's an example that I have used in the past.

Imports Excel = Microsoft.Office.Interop.Excel
Public Class Form1
    Private Sub Button1_Click(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles Button1.Click

        Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet

        xlApp = New Excel.ApplicationClass
        xlWorkBook = xlApp.Workbooks.Open("c:\test1.xlsx")
        xlWorkSheet = xlWorkBook.Worksheets("sheet1")
        'display the cells value B2
        MsgBox(xlWorkSheet.Cells(2, 2).value)
        'edit the cell with new value
        xlWorkSheet.Cells(2, 2) = "http://vb.net-informations.com"
        xlWorkBook.Close()
        xlApp.Quit()

        releaseObject(xlApp)
        releaseObject(xlWorkBook)
        releaseObject(xlWorkSheet)

    End Sub

    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub

End Class

Answer 5

It is easy to use Spire.XLS to read all available cells, for example:

Dim workbook As New Workbook
workbook.LoadFromFile("your-excel-file.xls")
Dim worksheet As Worksheet = workbook.Worksheets(0)
Dim range As CellRange = worksheet.AllocatedRange
Console.WriteLine("LastRow Index: {0}, LastColumn Index:{1}", range.LastRow, range.LastColumn)
For Each cell As CellRange In range.Cells
    If cell.IsBlank Then
        Continue For
    End If
    Console.WriteLine("{0} = {1}", cell.RangeAddressLocal, cell.Value2)    
Next