Compare Excel structure and content

diogogmaio · Post by **diogogmaio** » Tue Jun 14, 2016 5:18 pm

Hello

I want to compare two excel files with (for example) two sheets.
So the two files has the same structure and number of sheets and number of elements (rows and columns). Everything must be the same but the code must be able to compare all the sheets.

So Sheet 1 from Excel 1 must be the same of Sheet 1 from Excel 2

And

So Sheet 2 from Excel 1 must be the same of Sheet 2 from Excel 2

Number of elements on the sheets are the same.

How can this be done?

I can already compare 2 csv files and so on. But excel i am struggling with the multiple sheets.

Thanks in advance

odklizec · Post by **odklizec** » Wed Jun 15, 2016 7:43 am

Hi,

I think you can use the same approach as for CSV files, i.e. using ExcelDataConnector. If you check the ExcelDataConnector method description, it has some parameters, where you can define worksheet and range. http://www.ranorex.com/Documentation/Ra ... ctor_1.htm
You can find many ExcelDataConnector samples around this forum. Check for example this post:
http://www.ranorex.com/forum/how-to-acc ... tml#p23324

diogogmaio · Post by **diogogmaio** » Thu Jun 16, 2016 10:52 am

SO if I have a Excel file with 10 sheets what should i put in the "worksheetrange"?

public ExcelDataConnector(
string name,
string fileName,
string worksheetName,
string worksheetRange,
CheckState includeFile

odklizec · Post by **odklizec** » Thu Jun 16, 2016 11:29 am

Hi,

I believe you can leave it empty (to load entire worksheet) or specify a worksheet range with sting like 'A1:Z10'?

krstcs · Post by **krstcs** » Thu Jun 16, 2016 1:22 pm

The 'worksheetrange' variable is the range of columns/rows that you want to use as data from the given 'worksheetname', as Pavel suggested.

But, only ONE worksheet/page/tab can be used in any one Excel data connector.

If you need to check more than that you either need to make more data connectors (one for each sheet) or do it programmatically in code, if you want to do them all at one time.

diogogmaio · Post by **diogogmaio** » Fri Jun 17, 2016 5:33 pm

Yes, i need to make a large amount of worksheets and not only one.
So for each sheet i have to load a different excel connector...
That will be hard to code, correct?

Thanks in advance

diogogmaio · Post by **diogogmaio** » Thu Jun 23, 2016 2:31 pm

krstcs wrote:The 'worksheetrange' variable is the range of columns/rows that you want to use as data from the given 'worksheetname', as Pavel suggested.

But, only ONE worksheet/page/tab can be used in any one Excel data connector.

If you need to check more than that you either need to make more data connectors (one for each sheet) or do it programmatically in code, if you want to do them all at one time.

If my Excel file has for example 5 sheets what is the best/simple way to do it?
If I do a second excel connector how can i choose sheet 2 and then in another connector a sheet 3?

Thanks

asdf · Post by **asdf** » Thu Jun 23, 2016 3:28 pm

Hi diogogmaio,

Since Ranorex is based on the .NET Framework, you could use the whole C# functionality in order to achieve your intention.

The following link might be useful.
Compare Excel files

I hope i could help.

Kind regards,
asdf

diogogmaio · Post by **diogogmaio** » Mon Jun 27, 2016 11:21 am

I successfully managed to create a code to compare multiple worksheets in an Excel File. One sheet at a time.
I want to create in the same project a new function to compare only one sheet.
SO when the test is executed i want that the code is able to understand if there is one or more sheets in the file before comparing.

Can someone help me on this?

Code: Select all

TestModule("B3D418ED-A92D-4477-B32D-6D69275E55A5", ModuleType.UserCode, 1)]
    public class Common : ITestModule
    {
        /// <summary>
        /// Constructs a new instance.
        /// </summary>
        public Common()
        {
            // Do not delete - a parameterless constructor is required!
        }

        /// <summary>
        /// Performs the playback of actions in this module.
        /// </summary>
        /// <remarks>You should not call this method directly, instead pass the module
        /// instance to the <see cref="TestModuleRunner.Run(ITestModule)"/> method
        /// that will in turn invoke this method.</remarks>
        void ITestModule.Run()
        {
            Mouse.DefaultMoveTime = 300;
            Keyboard.DefaultKeyPressTime = 100;
            Delay.SpeedFactor = 1.0;
        }
        
      /// <summary>
      /// This method tests the file availability
      /// </summary>
      /// <param name="FilePath">path to file </param>
      public static void ExcelFileExists(string FilePath)
      {
         if (! System.IO.File.Exists(@FilePath))
         {
            
            // skip the iteration in case of missing file
            throw new RanorexException("Excel Does not exist! File: " + @FilePath);
            
         }
         else
            Report.Log(ReportLevel.Success, "Validation", "File " + FilePath + " exists.");
      }

      /// <summary>
      /// method to compare two excel files
      /// </summary>
      /// <param name="refFile"></param>
      /// <param name="cmpFile"></param>
        public static void CompareExcelFiles(string refFile, string cmpFile)
        {           
         //validate path to configuration file
         ExcelFileExists(refFile);
         
         //create excel data connector
         string refConnector = "ExcelConnector";

         ExcelFileExists(cmpFile);
         //create excel data connector
         string cmpConnector = "ExcelConnector";

         //get data from ref. excel
         Ranorex.Core.Data.ExcelDataConnector refExcelConnector = new Ranorex.Core.Data.ExcelDataConnector(refConnector,@refFile,"WorkSheet1","A1:X20",System.Windows.Forms.CheckState.Unchecked);
         Ranorex.Core.Data.ColumnCollection refExcelColumns = new Ranorex.Core.Data.ColumnCollection();
         Ranorex.Core.Data.RowCollection refExcelRows = new Ranorex.Core.Data.RowCollection(refExcelColumns);
         
         Ranorex.Core.Data.ExcelDataConnector refExcelConnector1 = new Ranorex.Core.Data.ExcelDataConnector(refConnector,@refFile,"WorkSheet2","A1:X20",System.Windows.Forms.CheckState.Unchecked);
         Ranorex.Core.Data.ColumnCollection refExcelColumns1 = new Ranorex.Core.Data.ColumnCollection();
         Ranorex.Core.Data.RowCollection refExcelRows1 = new Ranorex.Core.Data.RowCollection(refExcelColumns1);
         
         //load excel connector
         refExcelConnector.LoadData(out refExcelColumns, out refExcelRows);
         refExcelConnector1.LoadData(out refExcelColumns1, out refExcelRows1);

         //get data from cmp. excel
         Ranorex.Core.Data.ExcelDataConnector cmpExcelConnector = new Ranorex.Core.Data.ExcelDataConnector(cmpConnector,@cmpFile,"WorkSheet1","A1:X20",System.Windows.Forms.CheckState.Unchecked);
         Ranorex.Core.Data.ColumnCollection cmpExcelColumns = new Ranorex.Core.Data.ColumnCollection();
         Ranorex.Core.Data.RowCollection cmpExcelRows = new Ranorex.Core.Data.RowCollection(cmpExcelColumns);
         
         Ranorex.Core.Data.ExcelDataConnector cmpExcelConnector1 = new Ranorex.Core.Data.ExcelDataConnector(cmpConnector,@cmpFile,"WorkSheet2","A1:X20",System.Windows.Forms.CheckState.Unchecked);
         Ranorex.Core.Data.ColumnCollection cmpExcelColumns1 = new Ranorex.Core.Data.ColumnCollection();
         Ranorex.Core.Data.RowCollection cmpExcelRows1 = new Ranorex.Core.Data.RowCollection(cmpExcelColumns1);
         
         //load excel connector
         cmpExcelConnector.LoadData(out cmpExcelColumns, out cmpExcelRows);
         cmpExcelConnector1.LoadData(out cmpExcelColumns1, out cmpExcelRows1);
         
         //1º Sheet Comparison
         
         Ranorex.Core.Data.Row refRowExcel;
         Ranorex.Core.Data.Row cmpRowExcel;
         if (refExcelRows.Count == cmpExcelRows.Count)
         {
            //go through ref/cmp Excel files and compare individual elements
            string refExcelValue = "";
            string cmpExcelValue = "";
            bool differenceFound = false;
            for (int i=0; i<=refExcelRows.Count-1; i++)
            {
               refRowExcel = refExcelRows[i];
               cmpRowExcel = cmpExcelRows[i];
               for (int j=0; j<=refExcelColumns.Count-1; j++)
               {
                  refExcelValue = refRowExcel[j].ToString();
                  cmpExcelValue = cmpRowExcel[j].ToString();
                  if (refExcelValue != cmpExcelValue)
                  {
                     Report.Log(ReportLevel.Failure, "Sheet1 : Comparison value different than reference value...", "Reference value: " + refExcelValue + "\n" + "Comparison value: " + cmpExcelValue);                     
                     differenceFound = true;
                  }                        
               }
            }
            if (!differenceFound)
            {
               Report.Log(ReportLevel.Success, "Sheet 1 : Validation", "Validation OK! Reference and compare Excel files the same!");
            }
         }
         else
         {
            // skip the iteration in case the number of ref and cmp rows differ
            throw new RanorexException("Sheet1 : Number of rows in cmp. Excel file is not equal to number of rows in ref. Excel file!");
         }
         
         //2º Sheet Comparison
         
         Ranorex.Core.Data.Row refRowExcel1;
         Ranorex.Core.Data.Row cmpRowExcel1;
         if (refExcelRows1.Count == cmpExcelRows1.Count)
         {
            //go through ref/cmp Excel files and compare individual elements
            string refExcelValue1 = "";
            string cmpExcelValue1 = "";
            bool differenceFound = false;
            for (int i=0; i<=refExcelRows1.Count-1; i++)
            {
               refRowExcel1 = refExcelRows1[i];
               cmpRowExcel1 = cmpExcelRows1[i];
               for (int j=0; j<=refExcelColumns1.Count-1; j++)
               {
                  refExcelValue1 = refRowExcel1[j].ToString();
                  cmpExcelValue1 = cmpRowExcel1[j].ToString();
                  if (refExcelValue1 != cmpExcelValue1)
                  {
                     Report.Log(ReportLevel.Failure, "Sheet2 : Comparison value different than reference value...", "Reference value: " + refExcelValue1 + "\n" + "Comparison value: " + cmpExcelValue1);                     
                     differenceFound = true;
                  }                        
               }
            }
            if (!differenceFound)
            {
               Report.Log(ReportLevel.Success, "Sheet 2 : Validation", "Validation OK! Reference and compare Excel files the same!");
            }
         }
         else
         {
            // skip the iteration in case the number of ref and cmp rows differ
            throw new RanorexException("Number of rows in cmp. Excel file is not equal to number of rows in ref. Excel file!");
         }
      }       
    }
}

diogogmaio · Post by **diogogmaio** » Mon Jun 27, 2016 11:53 am

How do you select entire worksheet range?

diogogmaio · Post by **diogogmaio** » Mon Jun 27, 2016 4:27 pm

Last question:

I have one solution created for one excel file with only one sheet.
How can I make this solution "universal" for every excel file?
In the test i have to specify always the worksheet name.
In this case, despite being only one sheet, i have to always rename to be able to run the test.
How can I do it differently? Make it universal for every excel with one sheet?

Thanks

Code: Select all

//get data from ref. Excel
			Ranorex.Core.Data.ExcelDataConnector refExcelConnector = new Ranorex.Core.Data.ExcelDataConnector(refConnector,@refFile,"WorkSheet1","A1:X20",System.Windows.Forms.CheckState.Unchecked);
			Ranorex.Core.Data.ColumnCollection refExcelColumns = new Ranorex.Core.Data.ColumnCollection();
			Ranorex.Core.Data.RowCollection refExcelRows = new Ranorex.Core.Data.RowCollection(refExcelColumns);
			
			//load Excel connector
			refExcelConnector.LoadData(out refExcelColumns, out refExcelRows);

			//get data from cmp. Excel
			Ranorex.Core.Data.ExcelDataConnector cmpExcelConnector = new Ranorex.Core.Data.ExcelDataConnector(cmpConnector,@cmpFile,"WorkSheet1","A1:X20",System.Windows.Forms.CheckState.Unchecked);
			Ranorex.Core.Data.ColumnCollection cmpExcelColumns = new Ranorex.Core.Data.ColumnCollection();
			Ranorex.Core.Data.RowCollection cmpExcelRows = new Ranorex.Core.Data.RowCollection(cmpExcelColumns);
			
			//load Excel connector
			cmpExcelConnector.LoadData(out cmpExcelColumns, out cmpExcelRows);

diogogmaio · Post by **diogogmaio** » Tue Jun 28, 2016 3:58 pm

I have 2 problems that need help from you guys...

First - In the excelconnector who do you select the entire worksheet range?

Second - I have one solution that compares two files considering one sheet. How can I apply it to every file? I am saying this because you have to specify a worksheet name and I have for different comparisons different worksheet names...how can I do it?
How can I put like a general "sheet1" in the function to consider every first sheet despite her names?

Thanks

diogogmaio · Post by **diogogmaio** » Wed Jun 29, 2016 5:30 pm

To select the entire worksheet range I followed a previously advice and leave it "". It worked.

I only have now that last problem:

I have 4 files excel with one sheet each.
2 are comparison A
2 are comparison B

Comparison A files have a different worksheet name than comparison B files.
How can I make my code standard and not specific for each sheet1 name?

The only it works is putting the proper name but I want to put a general name...otherwise i will have to create multiple projects and solutions to do this comparison.

Ranorex Forum

Compare Excel structure and content

Compare Excel structure and content

Re: Compare Excel structure and content

Re: Compare Excel structure and content

Re: Compare Excel structure and content

Re: Compare Excel structure and content

Re: Compare Excel structure and content

Re: Compare Excel structure and content

Re: Compare Excel structure and content

Re: Compare Excel structure and content

Re: Compare Excel structure and content

Re: Compare Excel structure and content

Re: Compare Excel structure and content

Re: Compare Excel structure and content