none
counting the number of rows in excel sheet using C# RRS feed

  • Question

  • Hi, I have an excel sheet and I want to count the number of filled row on it. I use the below code, but it is wrong and I think it counts all the rows in sheet(empty and full). what should I do?

      number= TargetWorksheet.Rows.Count;

    the other question is about labeling excel sheet. after I count the number of rows I want to put a message that contains this number on excel sheet as a label but I do not know how should I do it? I work on windows form application.

    Thank you.


    • Edited by nadianaji Tuesday, December 26, 2017 6:54 AM
    Tuesday, December 26, 2017 6:39 AM

Answers

  • Hi, I use this and work for me

    number1 = TargetWorksheet1.UsedRange.Rows.Count - 1;

    • Marked as answer by nadianaji Tuesday, January 2, 2018 7:50 AM
    Tuesday, January 2, 2018 7:50 AM

All replies

  • Hi nadianaji,

    you can first try to find last used row in column then you can loop through all the cells till last used row and check it's value.

    if the value is not null then you can increment the counter variable to get number of used rows in column.

    Data in a sheet:

    Code:

    private void button1_Click(object sender, EventArgs e)
            {
                Excel.Application xlApp;
                Excel.Workbook xlWorkBook;
                Excel.Worksheet xlWorkSheet;
                object misValue = System.Reflection.Missing.Value;
                int cnt = 0;
                xlApp = new Excel.Application();
                xlWorkBook = xlApp.Workbooks.Open(@"C:\Users\v-padee\Desktop\Book2.xlsx", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
                xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
    
                int last = xlWorkSheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing).Row;
                Excel.Range range = xlWorkSheet.get_Range("A1:A" + last);
              
    
                foreach (Excel.Range element in range.Cells)
    
                {
                    if (element.Value2!=null)
                    {
                        cnt = cnt + 1;
                    }
                    System.Console.WriteLine(cnt);
                  
                }
                MessageBox.Show(cnt.ToString());
              
    
                xlWorkBook.Close(true, misValue, misValue);
                xlApp.Quit();
    
    
            }

    Output:

    you had asked another question that,"after I count the number of rows I want to put a message that contains this number on excel sheet as a label "

    do you mean comment which looks like label.

    if yes you need to add code below.

      xlWorkSheet.Cells[14,2].Cells.AddComment("Total number of Used Rows in Column 1 is " + cnt);

    Output when you click on cell.

    if you don't want this kind of label then you can directly add the count to the cell in the sheet.

    Regards

    Deepak


    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.

    Wednesday, December 27, 2017 12:44 AM
    Moderator
  • Hello,

    Check out the following solution, I already tried it and it works! Note this solution is based on a free 3rd party library called free spire.xls (which you can get from NuGet Package Manager), you can give it a try.

    Workbook workbook = new Workbook();
    workbook.LoadFromFile(@"Input.xlsx");
    
    Worksheet sheet = workbook.Worksheets[0];
    
    int rowCount = sheet.Rows.Count();

    Wednesday, December 27, 2017 8:40 AM
  • Hi nadianaji,

    is your issue solved?

    I find that you did not follow up this thread after posting the issue.

    if your issue is solved then I suggest you to post your solution and mark it as an answer.

    if your issue is still exist then try to refer the solution given by the me.

    if then also you have any further questions then let me know about it.

    I will try to provide further suggestions to solve the issue.

    Thanks for your understanding.

    Regards

    Deepak


    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.

    Tuesday, January 2, 2018 7:42 AM
    Moderator
  • Hi, I use this and work for me

    number1 = TargetWorksheet1.UsedRange.Rows.Count - 1;

    • Marked as answer by nadianaji Tuesday, January 2, 2018 7:50 AM
    Tuesday, January 2, 2018 7:50 AM