Read and write to excel file

[stega]

Hi All,

My problem is to write to an excel file. I can read datas from it, but i want to write to the same file. 

I'm adding new attributes with my script, and the first cell contains the attribute nam, the second the value, and the third the msg, that the adding was successfull or not.



I read the datas with this:

var Driver = DDT.ExcelDriver("path\Book.xls", "Sheet1");

attributeName = DDT.CurrentDriver.Value("Attribute name");

attributeValue = DDT.CurrentDriver.Value("Attribute value");



But how can i write back tho the third cell?



Thanks

[murugans1011]

DDT driver is onlt for reading the data in the excel files. you can' to write data with it



to write to excel files u need to work with Excel via Com

[stega]

I tried it, but i got the following error message:



'Book.xls' cannot be accessed. The file may be corrupted, located ona  server that is not responding, or read-only.

[murugans1011]

hi this error occurs if DDT is not closed make sure u closed the ddt driver before accessing the excel driver



to close the excel driver 



   Call DDT.Closedriver(Driver.name)





Writing data to excel sample

Sub Main

  Dim fileName, sheetName

  fileName = "<Path_To_The_File>"

  sheetName = "Sheet1"

  Call WriteExcelSheet(fileName, sheetName)

End Sub

Sub WriteExcelSheet(fname, sheetName)

  Dim maxcol, maxrow, app, book, sheet, rowCount, row, col

  maxcol = 5

  maxrow = 5

  Set app = Sys.OleObject("Excel.Application")

  Set book = app.Workbooks.Open(fname)

  Set sheet = book.Sheets(sheetName)

  app.DisplayAlerts = False

  ' Write an index of the current row and column to a cell

  rowCount = sheet.UsedRange.Rows.Count + 1

  For row = rowCount To rowCount + maxrow - 1

    For col = 1 To maxcol

      sheet.Cells(row, col) = row & ", " & col

    Next

  Next

  book.Save

  app.Quit

End Sub

[stega]

The problem with this is that i'm using while statement to read the whole excel, and after each iteration i want to write the result.

So in this case i guess, i have to use a counter to iterate the excel records.

[murugans1011]

i too hav faced the same issue when updating result of each test data iteration to pass or fail in excel.so i hav implemented counter foreach iteration and closed the ddt driver before writing the excel file. and after writing again open i hav opened the ddt driver and incremented the counter sometthing lik this



While DDT.CurrentDriver.EOF=False



   'do some operation



CountDDT=CountDDT+1

DDT.CloseDriver(Driver.Name)

 Call WritetoExcel()



     If Not DDT.DriverByName(driver.Name) is Nothing Then

            Call  DDT.CloseDriver(driver.Name)

     else

            Set Driver=DDT.ExcelDriver(filepath2,SheetName1,True)

            for i=1 to CountDDT

              Call DDT.CurrentDriver.Next

            Next

       End if

Wend



I m not sure this is the best approach

[stega]

Thanks for the reply.

I wrote a ReadExcel and a WriteExcel function and declared a counter.

I read the excel to get the rowCount, and than used while to iterate through the rcords.

[manjeetku]

• 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;

}

TC ID	TestDescription	TestMethodName	TestExecutionFlag	Result
1	Verify that the application is launched successfully.	LaunchAndCloseApplication.LaunchAndCloseApplication	Y	Fail
2	Verify PDF data.	ReadPDFData.verifyPDFTextValue	N	Not Run
3	Verify the path of last modified file in a folder.	LastModifiedFileInFolder.getLastModifiedFileName	Y	Pass

Hope this helps. For further query , reply back.