Column Count in Excel Using C#




Column Count in Excel Using C#, We will discuss about how to find column 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 need so much of data to be feed 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 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 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 Column 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 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 the Excel or writing data to the 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 Column count:

  1. ExcelApiTest
  2. CheckExcel

Below are the Interfaces will use to read the Column count using Microsoft.Interop.Excel package:

  1. Application
  2. Workbooks
  3. Workbook

Below collection is needed to create a Hash table:

  1. Using System.Collections

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

  1. ExcelApiTest

Below are the Methods will use to read the Column count:

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

Following is the code, we have used some Interfaces and methods to create connections, getting the column 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 GetColumnCount(string sheetName)
        {
            OpenExcel();

            int columnCount = 0;
            int sheetValue = 0;

            if (sheets.ContainsValue(sheetName))
            {
                foreach (DictionaryEntry sheet in sheets)
                {
                    if (sheet.Value.Equals(sheetName))
                    {
                        sheetValue = (int)sheet.Key;
                    }
                }
                xl.Worksheet worksheet = workbook.Worksheets[sheetValue] as xl.Worksheet;
                xl.Range range = worksheet.UsedRange;
                columnCount = range.Columns.Count;
            }
            CloseExcel();

            return columnCount;
        }
    }
}

Below is a sample program which will call the methods from the above program to get the column 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 colCount = eat.GetColumnCount("Sheet1");
            Console.WriteLine("Total Number of Columns: " + colCount);

            Console.Read();
        }

    }
}

Please watch the Youtube video for better understanding.




Related Posts