locked
How can I compare the first row of my excel file to the values stored in a variable to see if they are matched or not? RRS feed

  • Question

  • I need to open pre-existing excel file and compare the first row of my excel file under columns name to the some test values stored in variables (how to store those values too) to see if they are matched or not. What i did is open excel. Here is the code:

    using Excel = Microsoft.Office.Interop.Excel;

    using Xceed.Wpf.Toolkit;

    namespace First_Program

    {

        public class Program

        {

            public void exceldownload()

            {

                string str;

                int rCnt;

                int cCnt;

                int rw = 0;

                int cl = 0;

    // Create a new excel application instance

                Excel.Application xlApp = new Excel.Application();

                xlApp.Visible = true;

                xlApp.DisplayAlerts = false;

    //Open demo sheet

                Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"C:\Users\TestData.xlsx" ,0, true, 5, "", "", true,     Excel.XlPlatform.xlWindows, "", false, false, 0, true, false, false);

                        xlApp.WindowState = Microsoft.Office.Interop.Excel.XlWindowState.xlMaximized;

    //select the first sheet in demo.xlsx

                Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];

    //Find the range of cells used

                Excel.Range xlRange = xlWorksheet.UsedRange;

                rw = xlRange.Rows.Count;

                cl = xlRange.Columns.Count;

    for (rCnt = 1; rCnt <= 2; rCnt++)

                {

                    for (cCnt = 1; cCnt <= cl; cCnt++)

                    {

                        str = (string)(xlRange.Cells[rCnt, cCnt] as Excel.Range).Value2;

                        MessageBox.Show(str);

                    }

                }

    xlWorkbook.Close(0);

                xlApp.Quit();

            }

        }

    }

    Desired input is : My input in excel's first row with column name is

    Column Name : name, class, rollno Values : Abc, 10th, 88990

    1) I want to compare each cell in first row( that are 3) against 3 different variables. 2) Desired output will be Match Abc to the first Variable value and if they are matches then compare the second value that is 10th to the second variable and so on until complete first row is verified 

    Thursday, February 27, 2020 6:10 AM

Answers

  • Hi Amna Khalid 001,

    Thank you for posting here.

    You can use list<T> to store data. Here's code of my test you can refer to:

            bool control = true;
            public void exceldownload(List<string> lst)
            {
                string str;
                int rw = 0;
                int cl = 0;
    
                Excel.Application xlApp = new Excel.Application();
                xlApp.Visible = true;
                xlApp.DisplayAlerts = false;
    
                Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"your path", 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "", false, false, 0, true, false, false);
                xlApp.WindowState = Microsoft.Office.Interop.Excel.XlWindowState.xlMaximized;
    
                Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
    
                Excel.Range xlRange = xlWorksheet.UsedRange;
                rw = xlRange.Rows.Count;
                cl = xlRange.Columns.Count;
    
                for (int i=1;i<= cl;i++)
                {
                    str = Convert.ToString((xlRange.Cells[2, i] as Excel.Range).Value2);
                    if (!lst[i - 1].Equals(str))
                    {
                        MessageBox.Show($"Not match in column: {i}");
                        control = false;
                    }
                }
                if (control)
                {
                    MessageBox.Show($"All match");
                }
                xlWorkbook.Close(0);
                xlApp.Quit();
            }
            private void button1_Click(object sender, EventArgs e)
            {
                List<string> lst1 = new List<string>() {"Abc", "10t", "88990" };
                exceldownload(lst1);
            }

    Data in my excel:

    Result:

    Hope it can help you.

    Best Regards,

    Xingyu Zhao


    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.


    Thursday, February 27, 2020 9:34 AM

All replies

  • Hi Amna Khalid 001,

    Thank you for posting here.

    You can use list<T> to store data. Here's code of my test you can refer to:

            bool control = true;
            public void exceldownload(List<string> lst)
            {
                string str;
                int rw = 0;
                int cl = 0;
    
                Excel.Application xlApp = new Excel.Application();
                xlApp.Visible = true;
                xlApp.DisplayAlerts = false;
    
                Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"your path", 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "", false, false, 0, true, false, false);
                xlApp.WindowState = Microsoft.Office.Interop.Excel.XlWindowState.xlMaximized;
    
                Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
    
                Excel.Range xlRange = xlWorksheet.UsedRange;
                rw = xlRange.Rows.Count;
                cl = xlRange.Columns.Count;
    
                for (int i=1;i<= cl;i++)
                {
                    str = Convert.ToString((xlRange.Cells[2, i] as Excel.Range).Value2);
                    if (!lst[i - 1].Equals(str))
                    {
                        MessageBox.Show($"Not match in column: {i}");
                        control = false;
                    }
                }
                if (control)
                {
                    MessageBox.Show($"All match");
                }
                xlWorkbook.Close(0);
                xlApp.Quit();
            }
            private void button1_Click(object sender, EventArgs e)
            {
                List<string> lst1 = new List<string>() {"Abc", "10t", "88990" };
                exceldownload(lst1);
            }

    Data in my excel:

    Result:

    Hope it can help you.

    Best Regards,

    Xingyu Zhao


    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.


    Thursday, February 27, 2020 9:34 AM
  • I have done this : Below code is present in Program.cs file that is present under a folder name ProgramTesting that is again present again under a Folder name AppObj. The heirarchy is

    1. Test01--> AppObj --> ProgramTesting --> Program.cs (Test01 is .csproj file)
    2. Test01 --> MainProjectFile.cs (It is Solution File)
    using Excel = Microsoft.Office.Interop.Excel;
    using Xceed.Wpf.Toolkit;
    using System;
    using System.Collections.Generic;
    namespace MainProjectFile.AppObj.ProgramTesting
    {
        public class Program
        {
            bool control = true;
            public void exceldownload(List<string> lst)
            {
                string str;
                int rw = 0;
                int cl = 0;
                Excel.Application xlApp = new Excel.Application();
                xlApp.Visible = true;
                xlApp.DisplayAlerts = false;
                Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"C:\Users\TestData.xsls", 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "", false, false, 0, true, false, false);
                xlApp.WindowState = Microsoft.Office.Interop.Excel.XlWindowState.xlMaximized;
                Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
                Excel.Range xlRange = xlWorksheet.UsedRange;
                rw = xlRange.Rows.Count;
                cl = xlRange.Columns.Count;
                for (int i = 1; i <= cl; i++)
                {
                    str = Convert.ToString((xlRange.Cells[2, i] as Excel.Range).Value2);
                    if (!lst[i - 1].Equals(str))
                    {
                        MessageBox.Show($"Not match in column: {i}");
                        control = false;
                    }
                }
                if (control)
                {
                    MessageBox.Show($"All match");
                }
                xlWorkbook.Close(0);
                xlApp.Quit();
            }
            private void button1_Click(object sender, EventArgs e)
            {
                List<string> lst1 = new List<string>() { "Abc", "10t", "88990" };
                exceldownload(lst1);
            }
        }
    }
    In MainProjectFile.cs, to run above test case I have written the code is :
    namespace Test01.AppObj
    {
        [TestClass]
        public class MainProjectFile
        {
            [TestInitialize]
            //[Ignore]
            [Priority(1)]
            [TestMethod]
            public void Excel()
            {
                Program a = new Program();
                a.exceldownload(new List<string>());
    
            }
         }
    }

    But I am getting error is : 


      Message: 
        Initialization method Test01.AppObj.MainProjectFile.Excel threw exception. System.ArgumentOutOfRangeException: System.ArgumentOutOfRangeException: Index was out of range. Must be non-negative and less than the size of the collection.
        Parameter name: index.
      Stack Trace: 
        ThrowHelper.ThrowArgumentOutOfRangeException(ExceptionArgument argument, ExceptionResource resource)
        List`1.get_Item(Int32 index)




    Monday, March 2, 2020 6:06 AM
  • Could you please guide me how can I export report using a button click in C# MStest Selenium webdriver in chrome?
    Monday, April 20, 2020 12:37 PM