Page 1 of 1

does Ranorex support fetching data from Excel document

Posted: Tue Nov 10, 2009 11:46 am
by Laxmi
Hi

We are looking to automate our application.
It is an excel based application.
When we login into our application, we have the abitlity to open some excel documents from with in the application. These documents are not stored in local machine in some path. They are fetched from the DB.
We place some data into the excel document which we call project and then calculate it and see if the out put cells in the project are getting populated with correct values.

I want to know if Ranorex has the abitlity to identify the excel sheets with cell names and does it identify the named regions in excel?

Also within excel, we have some custom controls created by us. Clicking on which would open new windows.
Can you please tell me if Ranorex would identify these controls.

These are our major requirements.
If these are supported, we would look at buying licenses.

Please reply ASAP.

Thanks,
Laxmi,
[email protected]

Re: does Ranorex support fetching data from Excel document

Posted: Tue Nov 10, 2009 4:03 pm
by Ciege
Using standard Excel Interop you can do most anything with Ranorex in Excel.
Here is a method I use for reading named ranges in from any sheet within Excel with Ranorex:

Code: Select all

        public static string[] ExcelReadNamedRange(Microsoft.Office.Interop.Excel.Application objXL, Microsoft.Office.Interop.Excel.Workbook objXLWorkBook, string strExcelFileName, string RangeName, string strSheet)
        {
            /************************************************************************
             * Function         : ExcelReadNamedRange(string strExcelFileName, string RangeName, string strSheet)
             *
             * Description      : This function will read and return the data of a named range of cells 
             *                  :  in the specified Excel workbook.
             *                         
             * Parameters       : objXL             - An existing Excel Object. Pass in NULL if you want to open & close excel within this function
             *                  : objXLWorkbook     - An existing Excel Workbook Object. Pass in NULL if you want to open a workbook/spreadsheet within this function
             *                  : strExcelFileName  - Full path and filename of the Excel spreadsheet to open
             *                  : RangeName         - Name of range of data to return (i.e. "MyRange")
             *                  : 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 array
             * 
             * Return Value     : Data from the cells for success, null for failure
             * 
             * Revision History
             * Date       : Author                    : Reason/Change
             * ---------- : ------------------------- : ------------------------------
             * 03/24/2009 : Chris Gendreau            : Initial Creation 
             * 07/10/2009 : Chris Gendreau            : Added pass in ObjXL & ObjXLWoorkbook
             * 08/18/2009 : Chris Gendreau            : Added check if Named Range is only 1 cell in length
             ************************************************************************/

            //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;
            bool boolCloseExcel = false;

            if (strSheet == "")
            {
                Report.Debug("Reading Named Range: '" + RangeName + "' from spreadsheet: '" + strExcelFileName + "' on sheet: '1'.");
            }
            else
            {
                Report.Debug("Reading Named Range: '" + RangeName + "' from spreadsheet: '" + strExcelFileName + "' on sheet: '" + strSheet + "'.");
            }

            //Only open Excel if an existing Excel object was not passed in 
            if (objXL == null)
            {
                //Open Excel
                objXL = OpenExcel();
                boolCloseExcel = true;
            }

            if (objXL != null)
            {
                try
                {
                    //Only open the work book if an existing work book object was not passed in 
                    if (objXLWorkBook == null)
                    {
                        //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];
                    }

                    try
                    {
                        //Select the Named Range in the workbook
                        objXLRange = (Microsoft.Office.Interop.Excel.Range)objXLWorkSheet.get_Range(RangeName, Type.Missing);
                    }
                    catch (Exception ex)
                    {
                        //If this fails then the requested named range does not exist.
                        Report.Failure("Requested Named Range: '" + RangeName + "' does not exist.");

                        //Close Excel
                        objXL.Quit();

                        return null;
                    }

                    ////Read the data from the Named Range
                    //Report.Warn(objXLRange.Count.ToString());
                    string[] strArray = null;
                    //check if the named range is more than 1 cell in length
                    if (objXLRange.Count > 1)
                    {
                        System.Array myvalues = (System.Array)objXLRange.Cells.Value2;
                        //Convert the array into a String array
                        strArray = ConvertToStringArray(myvalues);
                    }
                    else
                    {
                        strArray = new string[1];
                        //Check if the singe named range cell is empty or not
                        if (objXLRange.Cells.Value2 != null)
                        {
                            strArray[0] = objXLRange.Cells.Value2.ToString();
                        }
                        else
                        {
                            strArray[0] = "";
                        }
                    }

                    if (boolCloseExcel == true)
                    {
                        //Close Excel
                        objXL.Quit();
                    }

                    //Return the data
                    return strArray;
                }

                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 named range values from Excel: " + errorMessage);

                    //Close Excel
                    objXL.Quit();

                    return null;
                }
            }
            return null;
        } //End ExcelReadNamedRange

As for your custom controls, what I suggest is download and install the Ranorex trial version. Use RanorexSpy to spy on your controls and see what information is available to you. Assuming your dev team follows standard operating procedure you should have no (or very few) immediate issues interacting with your custom controls.