Hi,
We have started building our test cases a month ago.
I want to ask about "Ranorex validation", I was trying to validate a registries on Excel Sheet, but without success..!
Another quiestion : is there a faster way to validate all data in a huge table (exp. 13 columns x 200 rows)?
Please take into consideration that I'm a Black box tester (with small background about programming)
I'm waiting for your answer,
Regards,
Yazeed
Quiestion about validation capabilities
Quiestion about validation capabilities
to be ,,, or not to be ...
- Support Team
- Site Admin
- Posts: 12145
- Joined: Fri Jul 07, 2006 4:30 pm
- Location: Houston, Texas, USA
- Contact:
Re: Quiestion about validation capabilities
Hi Yazeed,
it looks like that the Data Sheet in Excel has no MSAA implementation, that means that Ranorex can`t identify the cells,rows. However, if you want to access excel data in combination with testing/validating data-driven(e.g. excel file as input with a table including validation data) there are some useful links:
previous post:
http://www.ranorex.com/forum/post2191.h ... xcel#p2191
data driven testing:
http://www.ranorex.com/support/user-gui ... sting.html
http://www.ranorex.com/blog/data-driven ... with-excel
Best regards,
Christian
Ranorex Support Team
it looks like that the Data Sheet in Excel has no MSAA implementation, that means that Ranorex can`t identify the cells,rows. However, if you want to access excel data in combination with testing/validating data-driven(e.g. excel file as input with a table including validation data) there are some useful links:
previous post:
http://www.ranorex.com/forum/post2191.h ... xcel#p2191
data driven testing:
http://www.ranorex.com/support/user-gui ... sting.html
http://www.ranorex.com/blog/data-driven ... with-excel
Best regards,
Christian
Ranorex Support Team
Re: Quiestion about validation capabilities
Another option for reading Excel data and validation is to use the Microsoft.Office.Interop.Excel COM reference.
With that reference you can open excel and read any set of cells/columns you want to pretty quickly. Once read into an internal variable (array or some other var type) you can then compare that data with anything you want.
You can have methods that read data ranges, named ranges, single cells, specific columns / rows, specific sheets, etc... pretty much you name it there is a way to get at that data.
With that reference you can open excel and read any set of cells/columns you want to pretty quickly. Once read into an internal variable (array or some other var type) you can then compare that data with anything you want.
You can have methods that read data ranges, named ranges, single cells, specific columns / rows, specific sheets, etc... pretty much you name it there is a way to get at that data.
If this or any response has helped you, please reply to the thread stating that it worked so other people with a similar issue will know how you fixed your issue!
Ciege...
Ciege...
Re: Quiestion about validation capabilities
hi Ciege,
thx for posting but i can't find the "Microsoft.Office.Interop.Excel COM reference". Where can i get it?
best regards,
Oemer
thx for posting but i can't find the "Microsoft.Office.Interop.Excel COM reference". Where can i get it?
best regards,
Oemer
Re: Quiestion about validation capabilities
hi Ciege,
and after i get it, what's next? shall i install it? or open as a net assemby browser? please help,
best regards,
oemer
and after i get it, what's next? shall i install it? or open as a net assemby browser? please help,
best regards,
oemer
Re: Quiestion about validation capabilities
I replied to your PM but will reply here also for the potential benefit of others.
You need to have Excel installed on your machine to have access to the Microsoft.Office.Interop.Excel COM reference. Once Excel is installed you need to make a reference to it from your IDE. I use Visual Studio so that can be done from the solution explorer. Right click on the references folder and select Add Reference. Then under the .NET tab search for the Microsoft.Office.Interop.Excel listing. Once it is added to your project you can access all of the abilities within Excel from your code.
Some code examples of using the reference - not all inclusive. You can do many things with your Excel data from the referece to the COM object.
You need to have Excel installed on your machine to have access to the Microsoft.Office.Interop.Excel COM reference. Once Excel is installed you need to make a reference to it from your IDE. I use Visual Studio so that can be done from the solution explorer. Right click on the references folder and select Add Reference. Then under the .NET tab search for the Microsoft.Office.Interop.Excel listing. Once it is added to your project you can access all of the abilities within Excel from your code.
Some code examples of using the reference - not all inclusive. You can do many things with your Excel data from the referece to the COM object.
Code: Select all
public static Microsoft.Office.Interop.Excel.Application OpenExcel()
{
/************************************************************************
* Function : OpenExcel()
*
* Description : This function will open and return an Excel COM object.
*
* Parameters : N/A
*
* Return Type : Microsoft.Office.Interop.Excel.Application
*
* Return Value : Valid Excel Object for success, null for failure
*
* Revision History
* Date : Author : Reason/Change
* ---------- : ------------------------- : ------------------------------
* 03/23/2009 : Chris Gendreau : Initial Creation
************************************************************************/
Microsoft.Office.Interop.Excel.Application objXL;
try
{
//Create the Excel Object
objXL = new Microsoft.Office.Interop.Excel.Application();
//Return the Excel Object
return objXL;
}
catch (Exception ex)
{
Report.Failure("Unable to open Excel: " + ex.ToString());
return null;
}
} //End OpenExcel
Code: Select all
public static Microsoft.Office.Interop.Excel.Workbook OpenExcelWorkbook(Microsoft.Office.Interop.Excel.Application objXL, string strExcelFileName)
{
/************************************************************************
* Function : OpenExcelWorkbook()
*
* Description : This function will open and return an Excel Workbook COM object.
*
* Parameters : objXL - An existing Excel Object
* : strExcelFileName - path & filename of the Excel Workbook
*
* Return Type : Microsoft.Office.Interop.Excel.Workbook
*
* Return Value : Valid Excel Workbook Object for success, null for failure
*
* Revision History
* Date : Author : Reason/Change
* ---------- : ------------------------- : ------------------------------
* 07/10/2009 : Chris Gendreau : Initial Creation
************************************************************************/
Microsoft.Office.Interop.Excel.Workbook objXLWorkBook;
if (objXL != null)
{
try
{
objXLWorkBook = objXL.Workbooks.Open(strExcelFileName, 0, true, System.Reflection.Missing.Value, System.Reflection.Missing.Value,
System.Reflection.Missing.Value, true, System.Reflection.Missing.Value, System.Reflection.Missing.Value, true, false,
System.Reflection.Missing.Value, false, false, false);
//Return the Excel Workbook Object
return objXLWorkBook;
}
catch (Exception ex)
{
Report.Failure("Unable to open Excel: " + ex.ToString());
return null;
}
}
return null;
} //End OpenExcelWorkbook
Code: Select all
public static string ExcelReadSingleCell(string strExcelFileName, string strCell, string strSheet)
{
/************************************************************************
* Function : ExcelReadSingleCell(string strExcelFileName, string strCell, string strSheet)
*
* Description : This function will read and return the data of a single cell
* : in the specified Excel workbook.
*
* Parameters : strExcelFileName - Full path and filename of the Excel spreadsheet to open
* : strCell - Cell to return (i.e. "A1")
* : strSheet - OPTIONAL - Name of the sheet in the spreadsheet the cell is on
* : - If not supplied will default to the 1st sheet.
*
* Return Type : String
*
* Return Value : Data from the cell for success, null for failure
*
* Revision History
* Date : Author : Reason/Change
* ---------- : ------------------------- : ------------------------------
* 03/23/2009 : Chris Gendreau : Initial Creation
************************************************************************/
Microsoft.Office.Interop.Excel.Application objXL;
Microsoft.Office.Interop.Excel.Workbook objXLWorkBook;
Microsoft.Office.Interop.Excel.Sheets objXLSheets;
Microsoft.Office.Interop.Excel.Worksheet objXLWorkSheet;
Microsoft.Office.Interop.Excel.Range objXLRange;
if (strSheet == "")
{
Report.Debug("Reading data from Cell: '" + strCell + "' in spreadsheet: '" + strExcelFileName + "' on sheet: '1'.");
}
else
{
Report.Debug("Reading data from Cell: '" + strCell + "' in spreadsheet: '" + strExcelFileName + "' on sheet: '" + strSheet + "'.");
}
//Open Excel
objXL = OpenExcel();
if (objXL != null)
{
try
{
//Open the Excel Workbook
objXLWorkBook = objXL.Workbooks.Open(strExcelFileName, 0, true, System.Reflection.Missing.Value, System.Reflection.Missing.Value,
System.Reflection.Missing.Value, true, System.Reflection.Missing.Value, System.Reflection.Missing.Value, true, false,
System.Reflection.Missing.Value, false, false, false);
//Get the sheets from the workbook
objXLSheets = objXLWorkBook.Sheets;
if (strSheet == "")
{
//Select the first worksheet
objXLWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)objXLSheets[1];
}
else
{
//Select the requested worksheet
objXLWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)objXLSheets[strSheet];
}
//Select the cell in the workbook
objXLRange = (Microsoft.Office.Interop.Excel.Range)objXLWorkSheet.get_Range(strCell + ":" + strCell, Type.Missing);
//Read the data from the cell
string ExcelString = objXLRange.Cells.Value2.ToString();
////Print the value of the cell for a single cell selection
//Console.WriteLine(" Text in cell " + strCell + " is: " + ExcelString);
//Close Excel
objXL.Quit();
//Return the data
return ExcelString;
}
catch (Exception ex)
{
String errorMessage;
errorMessage = "Error: ";
errorMessage = String.Concat(errorMessage, ex.Message);
errorMessage = String.Concat(errorMessage, " Line: ");
errorMessage = String.Concat(errorMessage, ex.Source);
errorMessage = String.Concat(errorMessage, ex.ToString());
Report.Failure("Error reading a cell from Excel: " + errorMessage);
//Close Excel
objXL.Quit();
return null;
}
}
return null;
} //End ExcelReadSingleCell
If this or any response has helped you, please reply to the thread stating that it worked so other people with a similar issue will know how you fixed your issue!
Ciege...
Ciege...
Re: Quiestion about validation capabilities
hi Ciege,
thx for posting so quickly.
How can I generate the codes with the reference(after I added the reference)? I don't know how to use the reference. Please excuse my lack of knowledge,
regards,
Oemer
thx for posting so quickly.
How can I generate the codes with the reference(after I added the reference)? I don't know how to use the reference. Please excuse my lack of knowledge,
regards,
Oemer
Re: Quiestion about validation capabilities
You should be able to just copy and paste the code I have put into this thread and then compile it. You may need to go out to the internet and read a bit about programming or get a book or even ask your dev team for some help. All the code listed in the thread is in C# and should work fine if you are using Visual Studio or the Ranorex IDE.
If this or any response has helped you, please reply to the thread stating that it worked so other people with a similar issue will know how you fixed your issue!
Ciege...
Ciege...
Re: Quiestion about validation capabilities
hi Ciege,
Thank you very much for your assist.
I was trying to solve my issue by an alternative solution, which is :
> to prepare a comparing Excel file containing Ideal data
> read a real data from my SW
> save this data
> Copy & paste it next to the data on the comparing Excel file,
> (then by some calculation Validate just one register)
I'll try your way, but still there is a barrier on it :
Is there a faster way to validate a huge Excel table (i.e. 13 columns x 200 rows)?
Thank you & best regards,
Yazeed
Thank you very much for your assist.
I was trying to solve my issue by an alternative solution, which is :
> to prepare a comparing Excel file containing Ideal data
> read a real data from my SW
> save this data
> Copy & paste it next to the data on the comparing Excel file,
> (then by some calculation Validate just one register)
I'll try your way, but still there is a barrier on it :
Is there a faster way to validate a huge Excel table (i.e. 13 columns x 200 rows)?
Thank you & best regards,
Yazeed
to be ,,, or not to be ...
Re: Quiestion about validation capabilities
Well, what is faster? How fast are you reading it now? If you have not tried or used the Excel COM Interop yet how do you know if that method is faster or slower? You won't know until you try. I don't use the Excel COM Interop with that specific amount of columns & rows but I do read and write loads of data for my testing purposes and it goes quite well. One suggestion with the Excel COM Interop is to open the Excel file once (create the object once), then do all your data interaction, then close Excel. The opening and closing of Excel and the associated worksheet is the most time consuming single steps.Is there a faster way to validate a huge Excel table (i.e. 13 columns x 200 rows)
If this or any response has helped you, please reply to the thread stating that it worked so other people with a similar issue will know how you fixed your issue!
Ciege...
Ciege...