With the Excel Plug-In (Beta) it is now possible to automate the content of an Excel worksheet. This plug-in is not designed solely for getting data from Excel sheets (please read Data Driven Testing here), but rather it offers you the opportunity to automate the cells, rows and columns of an Excel document. For example, if you want to validate the impacts of your add-on on Excel, then the Excel plug-in is definitely the correct tool for you. In addition to access to the Excel UI elements, there are also new adapters available:

  • ExcelApplication
  • ExcelOleObject
  • ExcelRange
  • ExcelShape
  • ExcelWorkBook
  • ExcelWorkSheet

Through these adapters it is also possible to interact with the objects provided by the Excel object model.

Ranorex 3.X comes with the Excel Plug-In (no set up is needed).

Set up the Excel Plug-In

  1. Please close all Ranorex tools
  2. Download and extract Ranorex-Office-Plug-in.zip to following location
    “C:Documents and SettingsAll UsersApplication DataRanorex2Plugins” (for Windows XP) or “C:ProgramDataRanorex2Plugins” (for Vista, 7)”
  3. Start Ranorex Spy and check if the Plug-in was successfully loaded. Therefore, please open the About Dialog by clicking the Ranorex Logo.

  4. <i>Excel Plug-In successfully loaded
    Excel Plug-In successfully loaded

  • Start your Excel and it should be possible to track the elements of the worksheet.
  • Creating a new Project

    We now create a new project, which will show briefly what’s possible with the new Excel Plug-In. In the example we perform a calculation (implemented in user code) and use one of the formulas (actions recorded by Ranorex Recorder) which should simulate an Add-On.

    1. Please create a new solution called ExcelPlugin_Test
    2. First we have to add a new reference to the project. The reference is necessary to use the adapters of the Excel Plug-In in our project. Please use following location to navigate to the Ranorex.Plugin.Office.dll
      “C:Documents and SettingsAll UsersApplication DataRanorex2Plugins” (for Windows XP) or “C:ProgramDataRanorex2Plugins” (for Vista, 7)“

      Adding the new reference
      Adding the new reference

    3. After we successfully added the new reference, we now create a new class called SimpleCalc
    4. In this class we create two new methods: SetValuesCalc() and CheckResults(). These two methods should look like the following.
      SetValuesCalc

      /// <summary>
      /// Sets the values of the cell and carries out the calculation.
      /// </summary>
      /// <param name="sheet">The worksheet which should be used</param>
      /// <param name="value">The calculate value</param>
      /// <param name="resultCell">Your cell for the result</param>
      /// <param name="cellNames">Your cells are filled with a value. For example '"A1",A2"'</param>
      public static void SetValuesCalc(ExcelWorkSheet sheet, int value, string resultCell, params string[] cellNames)
      {
           int result= 0;
           Cell excelCell;
      
           foreach(string cell in cellNames)
           {
                //Searching for the cell
                excelCell = sheet.FindSingle<Cell>(".//cell[@address='" + cell +"']");
                excelCell.Click();
      
                //Enters the value by using the keyboard
                Keyboard.Press(value.ToString());
                Keyboard.Press(Keys.Enter);
      
                //Calculates the result of all cells
                result= result+ Convert.ToInt32(excelCell.Text);
           }
      
           //Searching for the result cell
           excelCell = sheet.FindSingle(".//cell[@address='" + resultCell+ "']");
           excelCell.Click();
      
           //Uses the SUM Formula or Excel
           Keyboard.Press("=SUM(" + cellNames[0] + ":" + cellNames[cellNames.Length - 1] + ")");
           Keyboard.Press(Keys.Enter);
      
           //Checks if the result of all cells equal the result cell.
           CheckResult(result, excelCell);
      
      }

      CheckResult

      /// <summary>
      /// Checks the result of the calculation
      /// </summary>
      /// <param name="expectedResult">Result of all cells used</param>
      /// <param name="result">The result cell of the calculation by Excel</param>
      private static void CheckResult( int expectedResult, Cell result)
      {
           Validate.Attribute(result, "Text", expectedResult.ToString());
      }
    5. When these two methods are done, we now need to call our calculation from the Program.cs file. For this we need the path to our worksheet. Start the Spy and track the first worksheet of our Excel book.
      Path to Worksheet
      As you can see, the new adapters are also visible in Spy. Now copy the path of the worksheet for our method.

      /*****************************************************
      * Parameter 1: Path or element for excel sheet
      * Parameter 2: Value shall be calculated.
      * Parameter 3: The address of the result cell
      * Parameter 4: The cell addresses which should have a value
      * ***************************************************/
      SimpleCalc.SetValuesCalc("/form[@processname='EXCEL']/element[@class='XLDESK']/workbook/table[@name='Sheet1']", 12, "A5", "A1","A2","A3");
    6. Let’s execute our calculation by compiling and running the whole project. (Press F5 or press the Run button)

    Now we create a recording where we calculate the average value of several numbers and format the result. Thereby we show how easy it is to test an Excel Add-On with the Recorder.

    1. Create a new recording named Formula.
    2. Start recording and click the worksheet.
    3. Enter some values to the worksheet. If you finished select the “Average Formula” from the “Formulas/Recently Used” menu.
    4. Mark the values of which the average should be calculated.
    5. Right click on the result cell and select “Format Cell” of the context menu.
    6. Format your cell now.
    7. When you finished formatting your cell, validate if the result was calculated correctly.
    8. Last but not least, call the recording in the Program.cs file and execute the whole project
      //Execute recording
      Formula.Start();

    You can download the sample project (Excel Project) which includes all the steps we performed in this blog. Please, don’t forget this Plug-In is still a Beta version and there is the possibility that some things might be changed.

    Please send us your feedback and your suggestions to improve the Excel Plug-In.