Read and write to excel file

• Since DTD driver won't write back to excel. First we need to read the excel and count all the rows as in below function.
• I have implemented using C#Script but the concept is same. It can be with other scripting languages too.

//Function to read excel file row by row.

function getExcelRowCount(varAny1 , varAny2){

//varAny1 , varAny2 are excel file path and excel sheet name respectively.

    var count = 0;

    Delay(3000);

    driver = DDT["ExcelDriver"](varAny1, varAny2, true);

    var isEndOFFile = driver["EOF"](); 

    while (!driver["EOF"]())

    {

     if((driver["Value"]("TestExecutionFlag") == "Y") || (driver["Value"]("TestExecutionFlag") == "y"))

     {

        count ++;

     }

     else if((driver["Value"]("TestExecutionFlag") == "N") || (driver["Value"]("TestExecutionFlag") == "n"))

     {

        count ++;

     }

     driver["Next"]();

    }

    DDT["CloseDriver"](driver["Name"] );

    Log["Message"](count);

    return count; 

 }

• Once we have all the row count with us. We can iterate through it and execute all the test methods like below.

//Since DTD driver won’t write back the test status to excel file, we have to do a work around. Below method will get the status message from test method and write to excel; whether the test is paa or fail.

// We only have to return success or failure from test methods.

// COMMON_CONSTANTS["testStatusColumnValue"] is an integer value for column number to be updated with test result(Pass, Fail or Not Run). For reusability purpose i am calling it from Constant(Property) file.

 function executeTestCaseFromExcelFile(fname, sheetName)

 {

   count = getRowCount(fname, sheetName);

   var passString = "Pass";

   var failString = "Fail";

   var notRunString = "Not Run";

   var statusColumn = 5;

   var app = Sys.OleObject("Excel.Application");

   var book = app.Workbooks.Open(fname);

   var sheet = book.Sheets(sheetName);

   app.DisplayAlerts = false;

  Delay(3000);

  for(var row = 2; row <= count + 1; row++)

    { 

      if((sheet.Cells(row, COMMON_CONSTANTS["testStatusColumnValue"] -1) == "Y")  || (sheet.Cells(row, COMMON_CONSTANTS["testStatusColumnValue"] -1) == "y"))

        {

          var testMethodName = sheet.Cells(row, 3).Value;

          var runnerResult = Runner["CallMethod"](testMethodName);

          var isSuccess = aqString["Compare"](runnerResult, "Success", false);

          Log["Message"]("status runnerResult type is = " + isSuccess);

          var errCnt = Log["ErrCount"];

          Log["Message"]("errCnt = " + errCnt);

          if(isSuccess == 0)

          {

          sheet.Cells(row, COMMON_CONSTANTS["testStatusColumnValue"]).Interior.ColorIndex = 4; //4 is color index of Green

          sheet.Cells(row, COMMON_CONSTANTS["testStatusColumnValue"]) = passString;

          }

           else

          {

            sheet.Cells(row, COMMON_CONSTANTS["testStatusColumnValue"]).Interior.ColorIndex = 3; //3 is color index of Red

            sheet.Cells(row, COMMON_CONSTANTS["testStatusColumnValue"]) = failString;

          }

        }

      else if((sheet.Cells(row, COMMON_CONSTANTS["testStatusColumnValue"] -1) == "N")  || (sheet.Cells(row, COMMON_CONSTANTS["testStatusColumnValue"] -1) == "n"))

          {

          sheet.Cells(row, COMMON_CONSTANTS["testStatusColumnValue"]).Interior.ColorIndex = 6; //6 is color index of Yellow

          sheet.Cells(row, COMMON_CONSTANTS["testStatusColumnValue"]) = notRunString; 

          }

      }

     book.Save();

     app.Quit();

  }

• Consider below as an example of test method and implement accordingly.

function LaunchAndCloseApplication(){

var result;

TestedApps["calc"]["Run"](1, false, 30000)

var lnkCalc = Aliases["XYZ"];

    Delay(10000);

    if(lnkCalc ["Exists"] == true){

        result = "Success";

      } else {

      result = "Failure";

      }

    lnkCalc ["Click"]();

         return result;

}

Hope this helps. For further query , reply back.