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);
}
}
}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.
