looping read excel data

Question

I need to loop the read data from excel to vb.net and when I reach the last row/column "!@#$%^&*()" the excel data will stop read. How can I do that?

Imports Excel = Microsoft.Office.Interop.Excel
Public Class Form1
    Dim xlApp As Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim xlWorkSheet As Excel.Worksheet
    Dim xRange As Excel.Range
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    End Sub
    Private Sub cmdGenerate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdGenerate.Click
        'Dim row As String
        Dim empty_cell_ctr As Integer = 0 '5
        Dim end_of_xlsheet As Boolean = False
        Dim sRow As Integer 'start row
        Dim col_end As Integer 'col W
        '
        'loading excel(open and read)
        xlApp = New Excel.ApplicationClass
        xlWorkBook = xlApp.Workbooks.Open("c:\sample.xls")
        xlWorkSheet = xlWorkBook.Worksheets("Timesheet")
        xlApp.Visible = True

        While Not end_of_xlsheet

           If sRow = "'!@#$%^&*()_+" Then
                xRange = xRange.Cells(sRow, col_end)
                end_of_xlsheet = False 'end of sheet
                Continue While
           End If

           sRow += 1

        End While

        MessageBox.Show(sRow)
    End Sub
End Class

Solution 1

You seem to be overthinking this, you can get all of the data in the spreadsheet by accessing the UsedRange property, and then load this into a 2D array variable by accessing the Value2 property of this object like so:

Dim application = New Excel.Application()
Dim workbook As Excel.Workbook = application.Workbooks.Open("C:\aaa\bbb.xlsx")
Dim worksheet As Excel.Worksheet = workbook.Sheets(1)

Dim usedRange = worksheet.UsedRange
Dim usedRangeAs2DArray As Object(,) = usedRange.Value2

workbook.Save()
workbook.Close()
application.Quit()

Marshal.ReleaseComObject(application)

Solution 2

You may use LIKE operator :)

'Not sure why you are trying to check Row number for set of characters...

Excel.Range range = sheet.UsedRange;
Int rows_count = range.Rows.Count;

For (int sRow = 1; sRow <= rows_count; sRow++)               
    If (sheet.Cells[sRow, col_end].value Like "*'!@#$%^&*") Then
        '-- input the data to where ever you want. It is best to store it into an array first..
        xRange = sheet.Cells[i, col_end].value; 
        Break;
    Else 
        sRow++;           
    End If

Please take a look the following reference for better understanding your options.

• import data from Excel to VB.Net

• read data from Excel to VB.Net

Comments

[bonCodigo]

Re-reading your question couple of times, now I have doubts of what you really need... :( do you want to stop when it hits the last row or not? What those characters doing there?

[bonCodigo]

Janine please accept the answer and help to update the system if this answers your question :-)

[Minus]

Excellent, simple and clear response. Could event be marked as the answer. But I just need to add one thing: Excel is 1-based index instead of any other 0-base index Enumarable objects (and in thid case Array type). Keep that in mind while you itterate through 'usedRangeAs2DArray'. This means that the first accesable object in XLusedRangeAs2DArray is XLusedRangeAs2DArray(1, 1).

[Hannington Mambo]

The 2 links provided are very useful!