Write Data in Excel Using Column Number in C#





Write data in excel using column number in C#, We will discuss about how to write data in excel using column number 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 feed so much of data to be need 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 write huge number of test data in 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 write data in excel using column number in C#. 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 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 the Excel or writing data to the excel.

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 write the column number:

  1. ExcelApiTest
  2. CheckExcel

Below are the Interfaces will use to Set the cell data using Microsoft.Interop.Excel package:

  1. Xl.Application
  2. Xl.Workbooks
  3. Xl.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 set the cell data using column number:

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

Following is the code, we have used some Interfaces and methods to create connections, setting the cell data using column number 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 bool SetCellData(string sheetName, int colNumber, int rowNumber, string value)
        {
            OpenExcel();

            int sheetValue = 0;

            try
            {
                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;

                    range.Cells[rowNumber, colNumber] = value;

                    workbook.Save();

                    Marshal.FinalReleaseComObject(worksheet);
                    worksheet = null;
                    CloseExcel();
                }
            }
            catch (Exception ex)
            {
                return false;
            }
            return true;
        }
}

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

            eat.SetCellData("Credentials", 5, 5,"FAIL");

        }

    }
}

Please watch the Youtube video for better understanding.




Related Posts