CODEWITHSUNDEEP
excelvb.net
Tendo Sai
Tendo Sai

Reputation: 29

Count row with data and specific value from excel in VB.net

I have 2 Labels (lblTotal) and (lblYes) which I put on my form. I want to read an excel file and count how many people come and how many say "yes", like this (picture). So "lblTotal" will count all row with data (exclude table header) and "lblYes" will show the counting of Answer with "Yes" which updates in real time. So far I got the lblTotal to work:

Dim xlApp As Excel.Application = Nothing
Dim xlWorkBooks As Excel.Workbooks = Nothing
Dim xlWorkBook As Excel.Workbook = Nothing
Dim xlWorkSheet As Excel.Worksheet = Nothing
Dim xlWorkSheets As Excel.Sheets = Nothing
Dim xlCells As Excel.Range = Nothing

Public Sub Total()
        xlApp = New Excel.Application
        xlApp.DisplayAlerts = False
        xlWorkBooks = xlApp.Workbooks
        xlWorkBook = xlWorkBooks.Open("D:\Question\Data.xlsx")
        xlApp.Visible = False
        xlWorkSheet = xlWorkBook.Worksheets("Sheet1")

Dim xx As Integer = xlWorkSheet.UsedRange.Rows.Count - 1
        lblTotal.Text = xx.ToString()
        xlApp.Quit()
End Sub

However, I have no idea how to count row that has the Yes answer. Also this doesn't allow real time update. Even though I use a timer to do the job (update on each tick) but it takes it too much to load. For example, in the code above, I used Button to refresh by calling the Total() function in the button click event.

I was thinking about the Button Save event that triggered this update as well. But my biggest question is how can I make it count "Yes" answer only?

Upvotes: 0

Views: 2347

Answers (1)

Gabriel Stancu
Gabriel Stancu

Reputation: 4380

I would do something like this, inside the Total() function:

    Dim WorkSheetPath As String = "D:\Question\Data.xlsx"
    Dim xlApp As Excel.Application
    Dim xlWorkbook As Excel.Workbook
    Dim xlWorksheet As Excel.Worksheet
    Dim Range As Excel.Range
    Dim rCnt As Integer
    Dim Obj As Object

    xlApp = New Excel.Application
    xlWorkbook = xlApp.Workbooks.Open(WorkSheetPath)
    xlWorksheet = xlWorkbook.Worksheets("sheet1")

    Range = xlWorksheet.UsedRange
    Dim count As Integer = 0
    For rCnt = 1 To Range.Rows.Count
        Obj = CType(Range.Cells(rCnt, 4), Excel.Range) 'if you change the index of the answer column in your excel file, change the index here as well (it is 4 right now)
        Dim answer As String = Obj.Value.ToString()
        If answer = "Yes" Then
            count += 1
        End If
    Next
    xlWorkbook.Close()
    xlApp.Quit()
    lblTotal.Text = count.ToString()

Hope this helps. ^^

Upvotes: 1

Related Questions