Excel sheet names

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

Excel sheet names

Post by diogogmaio » Thu Jun 30, 2016 5:41 pm

I have one project that compares multiple sheets (sheet1, sheet2 and so on) of different excel files.
I have many files to test and compare.
Not all the files have the same worksheet1, worksheet2 names. Sheet name is random.

How can I change my code in order to assess the different worksheet names?
ExcelDataConnector function only accepts a valid worksheet name...how can i make it random? Like...sheet1, sheet2, sheet3 despite the naming on the sheet's file.

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);
			
			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);
Thanks in advance

Martin
Posts: 152
Joined: Fri Aug 15, 2014 12:24 pm

Re: Excel sheet names

Post by Martin » Fri Jul 01, 2016 12:57 pm

This post seems to cover your needs.

http://stackoverflow.com/questions/1878 ... rp-interop

Basically what you need to do is get the worksheet names beforehand and open the ExcelDataConnector with the given values as needed.

diogogmaio
Posts: 33
Joined: Thu May 19, 2016 4:52 pm

Re: Excel sheet names

Post by diogogmaio » Fri Jul 01, 2016 1:36 pm

Hey, Martin

Thank you very much for your quick answer.
I am not very good in terms of coding.
I understood what you said but i don't know how to start in the order to achieve that.
Can you help me on that?

Code: Select all

/// <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 CSV data connector
			string refConnector = "ExcelConnector";

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

			//get data from ref. Excel
			Ranorex.Core.Data.ExcelDataConnector refExcelConnector = new Ranorex.Core.Data.ExcelDataConnector(refConnector,@refFile,"AR Reconciliation","",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","",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!");
			}
		}
	}
}

diogogmaio
Posts: 33
Joined: Thu May 19, 2016 4:52 pm

Re: Excel sheet names

Post by diogogmaio » Mon Jul 04, 2016 5:58 pm

Martin wrote:This post seems to cover your needs.

http://stackoverflow.com/questions/1878 ... rp-interop

Basically what you need to do is get the worksheet names beforehand and open the ExcelDataConnector with the given values as needed.
Do we had this before ExcelDataConnector?!

Code: Select all

Dictionary<string, Worksheet> dict = new Dictionary<string, Worksheet>();
foreach ( Worksheet worksheet in excelWorkbook.Worksheets )
{
   dict.Add( worksheet.Name, worksheet );
}
// accessing the desired worksheet in the dictionary
MessageBox.Show( dict[ "Sheet1" ].Name );
And then on the Worksheet name argument we put --> MessageBox.Show( dict[ "Sheet1" ].Name ???

diogogmaio
Posts: 33
Joined: Thu May 19, 2016 4:52 pm

Re: Excel sheet names

Post by diogogmaio » Tue Jul 05, 2016 1:31 pm

Martin wrote:This post seems to cover your needs.

http://stackoverflow.com/questions/1878 ... rp-interop

Basically what you need to do is get the worksheet names beforehand and open the ExcelDataConnector with the given values as needed.
Can you help considering my previous post?

jma
Posts: 111
Joined: Fri Jul 03, 2015 9:18 am

Re: Excel sheet names

Post by jma » Tue Jul 05, 2016 3:10 pm

You could try storing the worksheet names to an array. Please have a look at the sample code below. Furthermore, you will need to reference the Microsoft.Office.Tools.Excel DLL to your project and include the namespace in the corresponding class.

Code: Select all

			Excel.Application excelApp = new Excel.ApplicationClass();
			Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(refFile);			
			string[] worksheetnames = new string[3];
			int x=0;
			foreach ( Excel.Worksheet worksheet in excelWorkbook.Worksheets )
			{
				worksheetnames[x]=worksheet.Name;
				x++;
			}
The above-mentioned code needs to be executed at the beginning of the CompareExcelFiles method.
When creating the DataConnector, you could get the corresponding worksheet name from the array ("worksheetnames[0]").

Code: Select all

Ranorex.Core.Data.ExcelDataConnector refExcelConnector = new Ranorex.Core.Data.ExcelDataConnector(refConnector,@refFile,worksheetnames[0],"",System.Windows.Forms.CheckState.Unchecked);
Hope that helps!

diogogmaio
Posts: 33
Joined: Thu May 19, 2016 4:52 pm

Re: Excel sheet names

Post by diogogmaio » Tue Jul 05, 2016 6:06 pm

jma wrote:You could try storing the worksheet names to an array. Please have a look at the sample code below. Furthermore, you will need to reference the Microsoft.Office.Tools.Excel DLL to your project and include the namespace in the corresponding class.

Code: Select all

			Excel.Application excelApp = new Excel.ApplicationClass();
			Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(refFile);			
			string[] worksheetnames = new string[3];
			int x=0;
			foreach ( Excel.Worksheet worksheet in excelWorkbook.Worksheets )
			{
				worksheetnames[x]=worksheet.Name;
				x++;
			}

The above-mentioned code needs to be executed at the beginning of the CompareExcelFiles method.
When creating the DataConnector, you could get the corresponding worksheet name from the array ("worksheetnames[0]").

Code: Select all

Ranorex.Core.Data.ExcelDataConnector refExcelConnector = new Ranorex.Core.Data.ExcelDataConnector(refConnector,@refFile,worksheetnames[0],"",System.Windows.Forms.CheckState.Unchecked);
Hope that helps!
Thanks for your help.
Can you explain me this line

string[] worksheetnames = new string[3];

jma
Posts: 111
Joined: Fri Jul 03, 2015 9:18 am

Re: Excel sheet names

Post by jma » Wed Jul 06, 2016 9:14 am

This line of code creates the array that will be used to store the worksheet names. I would suggest having a look at the Arrays Tutorial from Microsoft in order to develop a general understanding of how arrays work.

diogogmaio
Posts: 33
Joined: Thu May 19, 2016 4:52 pm

Re: Excel sheet names

Post by diogogmaio » Wed Jul 06, 2016 10:31 am

jma wrote:This line of code creates the array that will be used to store the worksheet names. I would suggest having a look at the Arrays Tutorial from Microsoft in order to develop a general understanding of how arrays work.
Ok. I do understand in fact and i am very thankfull.

So if i understood correctly worksheetnames[0] is the first element of the array...and is equal to the first sheet from the excel. Right?

jma
Posts: 111
Joined: Fri Jul 03, 2015 9:18 am

Re: Excel sheet names

Post by jma » Wed Jul 06, 2016 1:11 pm

Yes, you are right. The first element in the array can be accessed by the index "0". In principle, this index refers to the first worksheet because the worksheet names are added in the correct sequence.

diogogmaio
Posts: 33
Joined: Thu May 19, 2016 4:52 pm

Re: Excel sheet names

Post by diogogmaio » Mon Jul 18, 2016 11:26 am

I am facing now a different issue.

How can I compare a merged cell in two different excel files?

When it compares (despite being the same) an error is produced due to the merged cell.

Thanks

Martin
Posts: 152
Joined: Fri Aug 15, 2014 12:24 pm

Re: Excel sheet names

Post by Martin » Mon Jul 18, 2016 2:15 pm

Hey

Sorry, I've been on vacation so haven't been able to respond to your questions. But as I see you have solved the Excel issue.

Related to the last error you get: could you please post the error you are thrown. Thanks