Compare excel files fail because Excel app changed

Best practices, code snippets for common functionality, examples, and guidelines.
diogogmaio
Posts: 34
Joined: Thu May 19, 2016 4:52 pm

Compare excel files fail because Excel app changed

Post by diogogmaio » Mon Jan 18, 2021 10:43 pm

Hello everyone,

1)
My code:

Code: Select all

/*
 * Created by Ranorex
 * User: ****
 * Date: 12/23/2020
 * Time: 4:14 PM
 * 
 * To change this template use Tools > Options > Coding > Edit standard headers.
 */
using System;
using System.Collections.Generic;
using System.Text;
using System.Text.RegularExpressions;
using System.Drawing;
using System.Threading;
using WinForms = System.Windows.Forms;

using Ranorex;
using Ranorex.Core;
using Ranorex.Core.Testing;

namespace Ranorex_Automation_Helpers.Modules
{
    /// <summary>
    /// Description of ExcelCompare2.
    /// </summary>
    [TestModule("55CA6A30-4018-40C2-8C44-D6C1E233B2C2", ModuleType.UserCode, 1)]
    public class ExcelCompare2 : ITestModule
    {
        /// <summary>
        /// Constructs a new instance.
        /// </summary>
        public ExcelCompare2()
        {
            // 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 lol
			ExcelFileExists(refFile);
			//create Excel1 data connector
			string refConnector = "ExcelConnector";

			ExcelFileExists(cmpFile);
			//create Excel2 data connector
			string cmpConnector = "ExcelConnector";
		
			Excel.Application excelApp = new Excel.ApplicationClass();
			Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(refFile);
			Excel.Workbook excelWorkbook1 = excelApp.Workbooks.Open(cmpFile);
			string[] worksheetnames = new string[9];
			string[] worksheetnames1 = new string[9];
			int x=0;
			int b=0;
			
			foreach ( Excel.Worksheet worksheet in excelWorkbook.Worksheets )
			{
				worksheetnames[x]=worksheet.Name;
				x++;
			}
			
			
			foreach ( Excel.Worksheet worksheet1 in excelWorkbook1.Worksheets )
			{
				worksheetnames1[b]=worksheet1.Name;
				b++;
			}


			//get data from ref. Excel
//			Ranorex.Core.Data.ExcelDataConnector refExcelConnector = new Ranorex.Core.Data.ExcelDataConnector(refConnector,@refFile,"AR Reconciliation","A:XX",System.Windows.Forms.CheckState.Unchecked);
			Ranorex.Core.Data.ExcelDataConnector refExcelConnector = new Ranorex.Core.Data.ExcelDataConnector(refConnector,@refFile,worksheetnames[0],"A:AX",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,"AR Reconciliation","A:XX",System.Windows.Forms.CheckState.Unchecked);
			Ranorex.Core.Data.ExcelDataConnector cmpExcelConnector = new Ranorex.Core.Data.ExcelDataConnector(cmpConnector,@cmpFile,worksheetnames1[0],"A:AX",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);
			
			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, "Comparison value different than reference value...", "Reference value: " + refExcelValue + "\n" + "Comparison value: " + cmpExcelValue);
							differenceFound = true;
						}
					}
				}
				if (!differenceFound)
				{
					Report.Log(ReportLevel.Success, "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!");
			}
			{
				if (excelWorkbook != null)
				{
					excelWorkbook.Close(false);
				}
			}
			{
				if (excelWorkbook1 != null)
				{
					excelWorkbook1.Close(false);
				}
			}
		}
This currently doesn't run.
It stopped working a few versions ago.
The error that appears is related to the Excel app call


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

Excel.Application excelApp = new Excel.ApplicationClass();
Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(refFile);
Excel.Workbook excelWorkbook1 = excelApp.Workbooks.Open(cmpFile);

string[] worksheetnames = new string[9];
string[] worksheetnames1 = new string[9];
int x=0;
int b=0;

Did something changed in the last versions of ranorex?
I am not able to make it work again.
Can this be fixed? > Excel.Application excelApp = new Excel.ApplicationClass();

Also:

2) I've tried to add a nuget package to my solution (in order to replace this compare excel code) > diffExcel. I'm not able to call it or run it.
What are the steps to be able to use the nuget packages in ranorex?
User methods are not available. Should i go to diffexcel repository and try to make it work, copying the .cs file?

Thanks for the assistance.
I've had the first part of this support request as more relevant, important to my project. that Excel compare script worked flawlessly and i would prefer to have it prepared to run in the current Ranorex environment. version.

Thanks in advance.