Row Count in Excel Using C#





Row Count in Excel Using C#, we will discuss about how to find row count in excel using C#. Usually we will use the excel sheet to maintain the test data. We might get doubt that why we need to put the test data/results in excel. If an application needs so much of data to be fed to test; then we will manage those data using external sources like text file, xml file and excel file etc…. Among these; Excel is very user friendly that we can organize the data very easily and we can read and write the data how we want. So, we mostly use excel to maintain the test data and results.

So, if you want to read a huge number of test data from the excel sheet to feed to the application then we need to know how many rows and columns are there in the excel. If you know the exact information about the test data then only we can get the information from the excel in proper way. In this blog we will use to get the row count from the excel sheet. To do this, we need to create a console application in Visual studio and Install Microsoft.Office.Interop.Excel Nuget package.

For this we need to have the following prerequisites:

  1. Create a Console Application in Visual Studio.
  2. Install Microsoft.Office.Interop.Excel Nuget Package by following below steps.
    • Under your Console Application, Right Click on reference, Click on Manage Nuget Package
    • Go to Browse
    • Under Browse search for “Microsoft.Office.Interop.Excel”
    • click on “Microsoft.Office.Interop.Excel” and then Click Install
    • Make sure that your package got installed and appears under References folder under your Console App.

What is Microsoft.Office.Interop.Excel Nuget package?
By using this Nuget package we can interact with the excel like reading data from Excel or writing data to excel and you can find row count/column count.





To Interact with our Nuget, we need to add the below using statement:

using xl = Microsoft.Office.Interop.Excel;

Below are the classes will use to read the Row count:

  1. ExcelApiTest
  2. CheckExcel

Following are the Interfaces will use to read the Row count using Microsoft.Office.Interop.Excel package:

  1. Application
  2. Workbooks
  3. Workbook

Following collection is needed to create a Hash table:

  1. Using System.Collections

Following Constructors are called to Interact with the User defined Excel sheet:

  1. ExcelApiTest

Following are the Methods will write to read the row count:

  1. OpenExcel() //Establish the connection
  2. CloseExcel()//Closes all the connections
  3. GetRowCount()

Following is the code, we have used some Interfaces and methods to create connections, getting the row count and closing the connections.

using System;
using System.Collections;
using System.Runtime.InteropServices;
using xl = Microsoft.Office.Interop.Excel;

namespace CSharpPractice
{
    class ExcelApiTest
    {
        xl.Application xlApp = null;
        xl.Workbooks workbooks = null;
        xl.Workbook workbook = null;
        Hashtable sheets;
        public string xlFilePath;

        public ExcelApiTest(string xlFilePath)
        {
            this.xlFilePath = xlFilePath;
        }

        public void OpenExcel()
        {
            xlApp = new xl.Application();
            workbooks = xlApp.Workbooks;
            workbook = workbooks.Open(xlFilePath);
            sheets = new Hashtable();
            int count = 1;
            // Storing worksheet names in Hashtable.
            foreach (xl.Worksheet sheet in workbook.Sheets)
            {
                sheets[count] = sheet.Name;
                count++;
            }
        }

        public void CloseExcel()
        {
            workbook.Close(false, xlFilePath, null); // Close the connection to workbook
            Marshal.FinalReleaseComObject(workbook); // Release unmanaged object references.
            workbook = null;

            workbooks.Close();
            Marshal.FinalReleaseComObject(workbooks);
            workbooks = null;

            xlApp.Quit();
            Marshal.FinalReleaseComObject(xlApp);
            xlApp = null;
        }

        public int GetRowCount(string sheetName)
        {
            OpenExcel();
            
            int rowCount = 0;
            int sheetValue = 0;

            if (sheets.ContainsValue(sheetName))
            {
                foreach (DictionaryEntry sheet in sheets) // Iterate over Hashtable
                {
                    if (sheet.Value.Equals(sheetName))
                    {
                        sheetValue = (int)sheet.Key;
                    }
                }

                // Getting particular worksheet using index/key from workbook
                xl.Worksheet worksheet = workbook.Worksheets[sheetValue] as xl.Worksheet;
                xl.Range range = worksheet.UsedRange; // Range of cells which is having content.
                rowCount = range.Rows.Count;
            }

            CloseExcel();

            return rowCount;
        }
    }
}

Below is a sample program which will call the methods from the above program to get the row count from any excel sheet.

using System;

namespace CSharpPractice
{
    class CheckExcel
    {
        static void Main(string[] args)
        {
            string xlFilePath = "D:/ExcelFiles/TestData.xlsx";

            ExcelApiTest eat = new ExcelApiTest(xlFilePath);
            int rowCount = eat.GetRowCount("Sheet1");

            Console.WriteLine("Total Number of Rows : " + rowCount);

            Console.Read();
        }
    }
}

Please watch the Youtube video for better understanding.




Related Posts