none
Try to count row with data and specific value from excel in VB.net RRS feed

  • Question

  • So I have 2  Labels (lblTotal) and (lblYes) which I put on form. I want to read excel file and count how many people coming and how many say "yes" like this (picture). so "lblTotal" will count all row with data exclude header and "lblYes" will show the counting of Answer with "Yes" which update in realtime.

    so far I can only open excel file.

    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
            xlWorkSheets = xlWorkBook.Sheets
        End Sub

    I think I might need this code to count the row with data. Credit to nixda's answer in stackoverflow.

    Lastrow =  Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row

    But I am not sure about count with specific value and how to get to it. So far I am stuck after open excel file dont know where to go.

    sample to my excel: https://imgur.com/a/llfgt6h

    Edit: I tried add this but seem it count all of the row instead.

    Dim xx As Integer = xlApp.Application.Rows.Count
            lblTotal.Text = xx.ToString()
    Thursday, September 13, 2018 4:13 PM

Answers

  • Hi,

     Imports Excel1 = Microsoft.Office.Interop.Excel

    Private Function GetDataFromExcelByCom(ByVal Optional hasTitle As Boolean = False) As DataTable Dim excelFilePath = "D:\Users\1.xlsx" Dim app As Excel1.Application = New Excel1.Application() Dim sheets As Excel1.Sheets Dim oMissiong As Object = System.Reflection.Missing.Value Dim workbook As Excel1.Workbook = Nothing Dim dt As DataTable = New DataTable() Try If app Is Nothing Then Return Nothing workbook = app.Workbooks.Open(excelFilePath, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong) sheets = workbook.Worksheets Dim worksheet As Excel1.Worksheet = sheets(1) Dim ji As Integer = CType(12, Integer) If worksheet Is Nothing Then Return Nothing Dim iRowCount As Integer = worksheet.UsedRange.Rows.Count Dim iColCount As Integer = worksheet.UsedRange.Columns.Count For i As Integer = 0 To iColCount - 1 Dim name = "column" & i If hasTitle Then Dim txt = (CType(worksheet.Cells(1, i + 1), Excel1.Range)).Text.ToString() If Not String.IsNullOrWhiteSpace(txt) Then name = txt End If While dt.Columns.Contains(name) name = name & "_1" End While dt.Columns.Add(New DataColumn(name, GetType(String))) Next Dim range As Excel1.Range Dim rowIdx As Integer = If(hasTitle, 2, 1) For iRow As Integer = rowIdx To iRowCount Dim dr As DataRow = dt.NewRow() For iCol As Integer = 1 To iColCount range = CType(worksheet.Cells(iRow, iCol), Excel1.Range) dr(iCol - 1) = If((range.Value2 Is Nothing), "", range.Text.ToString()) Next dt.Rows.Add(dr) Next Return dt Catch Return Nothing Finally workbook.Close(False, oMissiong, oMissiong) System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook) workbook = Nothing app.Workbooks.Close() app.Quit() System.Runtime.InteropServices.Marshal.ReleaseComObject(app) app = Nothing End Try End Function

    This is a Function to convert Excel into a DataTable,then you can manipulate the DataTable.

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by SoNewBie Friday, September 14, 2018 9:37 AM
    Friday, September 14, 2018 5:32 AM

All replies

  • I tested with this

    xlWorkSheet = xlWorkBook.Worksheets("Sheet1")
    
    Dim xx As Integer = xlWorkSheet.UsedRange.Rows.Count - 1
            lblTotal.Text = xx.ToString()
            xlApp.Quit()

    seem to show correctly for total.

    Thursday, September 13, 2018 4:21 PM
  • Hi,

     Imports Excel1 = Microsoft.Office.Interop.Excel

    Private Function GetDataFromExcelByCom(ByVal Optional hasTitle As Boolean = False) As DataTable Dim excelFilePath = "D:\Users\1.xlsx" Dim app As Excel1.Application = New Excel1.Application() Dim sheets As Excel1.Sheets Dim oMissiong As Object = System.Reflection.Missing.Value Dim workbook As Excel1.Workbook = Nothing Dim dt As DataTable = New DataTable() Try If app Is Nothing Then Return Nothing workbook = app.Workbooks.Open(excelFilePath, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong) sheets = workbook.Worksheets Dim worksheet As Excel1.Worksheet = sheets(1) Dim ji As Integer = CType(12, Integer) If worksheet Is Nothing Then Return Nothing Dim iRowCount As Integer = worksheet.UsedRange.Rows.Count Dim iColCount As Integer = worksheet.UsedRange.Columns.Count For i As Integer = 0 To iColCount - 1 Dim name = "column" & i If hasTitle Then Dim txt = (CType(worksheet.Cells(1, i + 1), Excel1.Range)).Text.ToString() If Not String.IsNullOrWhiteSpace(txt) Then name = txt End If While dt.Columns.Contains(name) name = name & "_1" End While dt.Columns.Add(New DataColumn(name, GetType(String))) Next Dim range As Excel1.Range Dim rowIdx As Integer = If(hasTitle, 2, 1) For iRow As Integer = rowIdx To iRowCount Dim dr As DataRow = dt.NewRow() For iCol As Integer = 1 To iColCount range = CType(worksheet.Cells(iRow, iCol), Excel1.Range) dr(iCol - 1) = If((range.Value2 Is Nothing), "", range.Text.ToString()) Next dt.Rows.Add(dr) Next Return dt Catch Return Nothing Finally workbook.Close(False, oMissiong, oMissiong) System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook) workbook = Nothing app.Workbooks.Close() app.Quit() System.Runtime.InteropServices.Marshal.ReleaseComObject(app) app = Nothing End Try End Function

    This is a Function to convert Excel into a DataTable,then you can manipulate the DataTable.

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by SoNewBie Friday, September 14, 2018 9:37 AM
    Friday, September 14, 2018 5:32 AM
  • Wow. Let's check and get back to you. I can only understand 10% of the code.
    Friday, September 14, 2018 6:39 AM
  • Hello,

    So which one I choose to show number for my lblTotal and lblYes? I was like reading 2 or 3 times still dont really understand the code.

    Friday, September 14, 2018 7:36 AM
  • Hi,

    "lblTotal" will count all row with data exclude header and "lblYes" will show the counting of Answer with "Yes"

    lblTotal=GetDataFromExcelByCom(ByVal Optional hasTitle As Boolean = False).rows.count

    lblYes.Text=GetDataFromExcelByCom(ByVal Optional hasTitle As Boolean = False).[Select](Answer='Yes').count


    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, September 14, 2018 7:45 AM

  • lblYes.Text=GetDataFromExcelByCom(ByVal Optional hasTitle As Boolean = False).[Select](Answer='Yes').count

    this one error in many way.
    Friday, September 14, 2018 8:53 AM
  • I believe you are trying to write:
    lblTotal.text=GetDataFromExcelByCom.rows.count
    lblYes.text=GetDataFromExcelByCom.select("Answer = 'Yes'").count

    However, I got error with Answer column not found.
    Friday, September 14, 2018 9:24 AM
  • Hi,

    modify the code:

    lblYes.text=GetDataFromExcelByCom(true).select("Answer = 'Yes'").count

    excel:

    DataGridView1.DataSource = GetDataFromExcelByCom(True):

    DataGridView1.DataSource = GetDataFromExcelByCom(False):

    I hope you can understand my code,then modify my code according to your requirements.

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, September 14, 2018 9:36 AM
  • Oh I got it works.
    lblTotal.text=GetDataFromExcelByCom.rows.count
    lblYes.text=GetDataFromExcelByCom(True).select("Answer = 'Yes'").count

    add True to the function
    Friday, September 14, 2018 9:37 AM