Forum Discussion

stega's avatar
stega
Contributor
11 years ago

Read and write to excel file

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's avatar
    murugans1011
    Regular Contributor
    11 years ago
    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
  • murugans1011's avatar
    murugans1011
    Regular Contributor
    11 years ago
    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
  • murugans1011's avatar
    murugans1011
    Regular Contributor
    11 years ago
    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's avatar
    stega
    Contributor
    11 years ago
    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.



  • stega's avatar
    stega
    Contributor
    11 years ago
    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.
  • stega's avatar
    stega
    Contributor
    11 years ago
    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.
  • manjeetku's avatar
    manjeetku
    New Contributor
    8 years ago
    • 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.