Quiestion about validation capabilities

[Yazeed]

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

[Support Team]

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

[Ciege]

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.

[Oemer]

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

[Oemer]

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

[Ciege]

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.

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

[Oemer]

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

[Ciege]

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.

[Yazeed]

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

[Ciege]

Is there a faster way to validate a huge Excel table (i.e. 13 columns x 200 rows)

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.