Get Data from Excel Using Column Name in C#





Get Data from excel using column name in c#, We will discuss about how to get data from excel using column name in 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 how to read the data from 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 below pre-requisites:

  1. Create a Console Application in Visual Studio
  2. Install Office.Interop.Excel Nuget Package by following below steps
    1. Under your Console Application, Right Click on reference, Click on Manage Nuget Package
    2. Go to Browse
    3. Under Browse search for “Microsoft.Office.Interop.Excel”
    4. click on “Microsoft.Office.Interop.Excel” and then Click Install
    5. 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 get the CellData using Column name:

  1. ExcelApiTest
  2. CheckExcel

Below are the Interfaces will use to get the CellData 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 get the CellData using Column name:

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

Following is the code, we have used some Interfaces and methods to create connections, getting the cell data using column name 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 string GetCellData(string sheetName, string colName, int rowNumber)
        {
            OpenExcel();

            string value = string.Empty;
            int sheetValue = 0;
            int colNumber = 0;

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

                for (int i = 1; i <= range.Columns.Count; i++)
                {
                    string colNameValue = Convert.ToString((range.Cells[1,i] as xl.Range).Value2);

                    if (colNameValue.ToLower() == colName.ToLower())
                    {
                        colNumber = i;
                        break;
                    }
                }

                value = Convert.ToString((range.Cells[rowNumber, colNumber] as xl.Range).Value2);
                Marshal.FinalReleaseComObject(worksheet);
                worksheet = null;
            }
            CloseExcel();
            return value;
        }

Below is a sample program which will call the methods from the above program to get data from excel using column name 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);

            cellValue = eat.GetCellData("Sheet1", "FirstName", 4);
            Console.WriteLine("Cell Value using Column Name: " + cellValue);


            Console.Read();
        }

    }
}

Please watch the Youtube video for better understanding.



Related Posts