Simulazione Esecuzione da Excel

 

 

Requisito utente

 

Aggiornare Quality Center per quanto concerne l'inserimento di Esecuzioni di Test effettuate esternamente al prodotto e registrate in un File Excel.

 

 

Soluzione Proposta

 

Tramite l'utilizzo di uno script esterno creare strategia sotto folder Unattached, inserire i casi di test (questi devono essere presenti in test plan), creare Run e Steps per ciascuna istanza di test e aggiornare i dati prendendo le info da file excel.


 

 

__________________________________________________________________________

 

 

Implementazione FUORI dal Prodotto

 

L'implementazione prevede la creazione di un vbscript (file .vbs) che, tramite l'utilizzo delle OTA API,  effettui le operazioni di:

 

  • collegamento al progetto
  • creazione TestSet fittizio sotto cartella Unattached
  • colleghi i Casi di Test al TestSet (l'indicazione di quali sono i casi di test deve essere data in input indicando il percorso di TestPlan sotto il quale sono presenti i Test)
  • cicli su ogni elemento della lista dei casi di test della strategia e crei l'oggetto Run e ad esso vengano aggiunti gli stessi Step del DesignStep
  • aggiorni i dati degli Step prendendo i risultati dal foglio excel
  • disconnessione dal progetto e da qc.

 

Suppongo che il foglio excel abbia i seguenti dati sotto le relative posizioni:

  • Nome Test                    - Col B
  • Nome Step                    - Col J
  • Risultato Attuale Step     - Col M
  • Stato Esecuzione Step    - Col N
  • Tester                          - Col O

 

Nell'esempio i commenti all'interno del codice sono scritti in lingua Inglese:

 

'Steps to follow:
'***************************************************************************************************************************
'1. Connection to QC and Project
'2. Creation of an empty TestSet (for the moment only under "Unattached" folder)
'3. Link Tests to the empty TestSet
'4. Create a Loop for each TestInstance of the TestSet
' 4.1 For each TestInstance Create the Run Object
'  4.1.1 From the Run Object create Steps, from StepFactory, that are the same of the Designed Test
'  4.1.2 For each Step update all fields from the designed test, the status and the actual result from the excel sheet
'***************************************************************************************************************************

'***************************************************************************************************************************
'         C O N S T A N T S     D E F I N I T I O N
'***************************************************************************************************************************
Const QC_ADDRESS = "http://10.10.10.10/qcbin"
Const DOMAIN = "Default"
Const PROJECT = "MY_PROJECT"
Const QCADMINUSR = "qcadmin"
Const QCADMINPWD = "qcpwd"

 

'Suppose all Tests are under the path below. 
'Tests can be also under subfolders of this path
'Tests MUST be NOT directly under Subject Folder

Const PATH_TESTPLAN = "Subject\MyFolder1\MyFolder2"                   


Const EXCEL_FILE = "C:\temp\myFile.xls"
'***************************************************************************************************************************


'***************************************************************************************************************************
'         V A R I A B L E S     D E F I N I T I O N
'***************************************************************************************************************************
Dim tdc, objTestSet, objTestInstanceList, objTestInstance

Dim objRun, objStep, objTest
Dim arrPath_TP, idPrevFold, idLastFolder, myComm, RecSet, strAbsPAth

Dim strAllFolderID, strStepData
Dim XLS, wkb, wks
Dim myShell
'***************************************************************************************************************************

set myShell = CreateObject("WScript.Shell")

'***********************************************************************
'   1. Connection to QC and Project
'***********************************************************************
set tdc = createobject("tdapiole80.tdconnection.1")

tdc.InitConnectionEx QC_ADDRESS
tdc.login     QCADMINUSR, QCADMINPWD
tdc.Connect    DOMAIN,     PROJECT

myShell.Popup "I'm connected to the Project " & PROJECT, 2, "QC Project Connection", 64
'***********************************************************************


'**********************************************************************************************
'   2. Creation of an empty TestSet (it will be stored under "Unattached" folder
'**********************************************************************************************
on error resume next
err.clear
set objTestSet = tdc.TestSetFactory.AddItem("TestSet_FakeExecution")
if err.number = 0 then
 myShell.Popup "Creation of TestSet under Unattached folder has been done", 2, "QC TestSet Creation", 64
end if
'**********************************************************************************************


'**********************************************************************************************
'       3. Link Test to the Empty TestSet
'**********************************************************************************************

'In this case I need to retrieve all the Tests under the PATH_TESTPLAN.
'To do this I need to do a query to retrieve the AL_ABSOLUTE_PATH of the
'PATH_TESTPLAN and then retrieve all the folder that begin with that path.
'After this I can retrieve all the Tests under those folders.

strAllFolderID = ""
set myComm = tdc.Command

arrPath_TP = split(PATH_TESTPLAN,"\")
'I suppose the 1st element of the array is "Subject" so I analyze from the 2nd
'AL_ITEM_ID of the "Subject" Folder is 2
idPrevFold = 2
for i=1 to ubound(arrPath_TP)
 'I do a query to retrieve the correct ID of the i-element folder.
 myComm.CommandText = "SELECT AL_ITEM_ID FROM ALL_LISTS " & _
       "WHERE " & _
       "AL_ITEM_DESCRIPTION = '" & arrPath_TP(i) & "' AND " & _
       "AL_FATHER_ID = " & idPrevFold
 set RecSet = myComm.Execute   
 RecSet.First
 idPrevFold = RecSet.FieldValue(0)   
 set RecSet = Nothing
next 
 
idLastFolder = idPrevFold
 
strAllFolderID = CStr(idLastFolder)
'Now idPrevFold is the correct ID of the last folder of PATH_TESTPLAN and

'from it I can retrieve the AL_ABSOLUTE_PATH
myComm.CommandText = "SELECT AL_ABSOLUTE_PATH FROM ALL_LISTS " & _
      "WHERE " & _
      "AL_ITEM_ID = " & idLastFolder
     
set RecSet = myComm.Execute
RecSet.First
strAbsPath = RecSet.FieldValue(0)
set RecSet = Nothing
 
'Query to retrieve all the Folders and Subfolders under the last folder of PATH_TESTPLAN
myComm.CommandText = "SELECT AL_ITEM_ID FROM ALL_LISTS " & _
      "WHERE AL_ABSOLUTE_PATH LIKE '" & strAbsPath & "%'"
     
set RecSet = myComm.Execute
if RecSet.RecordCount > 0 then
 RecSet.First
 Do While Not(RecSet.EOR)
  strAllFolderID = strAllFolderID & "," & CStr(RecSet.FieldValue(0))
  RecSet.Next
 Loop
end if
set RecSet = Nothing

'strAllFolderID is a string variable contains all the ID of the Folders
'starting from the last folder of PATH_TESTPLAN constant

'for each element of this array I have to retrieve the list of Tests under it.
'I will do a query again. This time on TEST table.
myComm.CommandText = "SELECT TS_TEST_ID FROM TEST " & _

                                   "WHERE TS_SUBJECT IN (" & strAllFolderID & ")"

set RecSet = myComm.Execute
myShell.Popup "I'm going to link Tests to the TestSet " objTestSet.Name, _

                    2, "QC Link Tests To TestSet", 64
if RecSet.RecordCount > 0 then
 RecSet.First
 Do While Not(RecSet.EOR)
  '******************************
  ' Add Test to the TestSet
  '******************************  
  objTestSet.TSTestFactory.AddItem RecSet.FieldValue(0)    
 Loop
end if
set RecSet = Nothing

'**********************************************************************************************

 

'**********************************************************************************************
'    4. Create a Loop for each TestInstance of the TestSet
'**********************************************************************************************


'4.0 Open the Excel File
set XLS = CreateObject("Excel.Application")
XLS.Visible = False
set Wkb = XLS.Workbooks.Open(EXCEL_FILE)
set Wks = Wkb.WorkSheets(1)


'Create the List of TestInstance
set objTestInstanceList = objTestSet.TSTestFactory.NewList("")

myShell.Popup "I'm going to create Fake Run and Steps for each TestInstance", _

                    2, "QC Creation of Fake Executions", 64

'4.1 For each TestInstance Create the Run Object
For each TestInstance in objTestInstanceList
 set objTestInstance = objTestSet.TSTestFactory.Item(TestInstance.ID)  
 'Add the Run Object. Run Name will be

 '"Run_Fake_IDOftheTestInstance_yyyymmdd".
  '    (if month and day are less than 10 it will consist on 1 digit char)
  set objRun = objTestInstance.RunFactory.AddItem("Run_Fake_" & objTestInstance.ID & year(date) & month(date) & day(date))
   objRun.Field("RN_EXECUTION_DATE") = Date
   objRun.Field("RN_EXECUTION_TIME") = Time
   'Now I will create for the Run the same number of Steps according to the Design Test
   'Retrieve Design Test Informations
   set objTest = tdc.TestFactory.Item(objTestInstance.TestID)
    'retrieve the DesignStepList
    set objDesStepList = objTest.DesignStepFactory.NewList("")
     'I create a loop to retrieve DesignStep Informations
     for each DesStep in objDesStepList
      'Create the Step for the Run object with the same info of the DesignStep
      set objStep = objRun.StepFactory.AddItem(DesStep.Name)
       objStep.Field("ST_DESCRIPTION") = DesStep.StepDescription
       objStep.Field("ST_EXPECTED") = DesStep.StepExpectedResult
       objStep.Field("ST_DESSTEP_ID") = DesStep.ID
       objStep.Field("ST_TEST_ID") = objTest.ID       
       objStep.Field("ST_EXECUTION_DATE") = Date
       objStep.Field("ST_EXECUTION_TIME") = Time
       'Here put the value of Status and Actual from excel
       'call a function that return Status and Actual result in a string variable
       'I pass the info of TestName, StepName to the function
       strStepData = strDataFromExcel(wks, objTest.Name, DeStep.Name)
       
       'strStepData is a string consist of 3 values, Status, ActualResult and Tester, separated from " || " chars.
       if len(strStepData) > 0 then
        objStep.Status = split(strStepData, " || ")(0)
        objStep.Field("ST_ACTUAL") = split(strStepData, " || ")(1)       
       end if
              
       objStep.Post
       objStep.Refresh
      set objStep = Nothing
     next
    set objDesStepList = Nothing
   set objTest = Nothing
   if len(strStepData) > 0 then
    objRun.Field("RN_TESTER_NAME") = split(strStepData, " || ")(2))  
   end if
   objRun.Post
   objRun.Refresh
  set objRun = Nothing
  if len(strStepData) > 0 then
   objTestInstance.Field("TC_TESTER_NAME") = split(strStepData, " || ")(2))
  end if
  objTestInstance.Field("TC_EXEC_DATE") = Date
  objTestInstance.Field("TC_EXEC_TIME") = Time
  objTestInstance.Post
  objTestInstance.Refresh
 set objTestInstance = Nothing
Next


'**********************************************************************************************
'Destroy all the objects
set objTestInstanceList = Nothing
set Wks = Nothing

wkb.close
XLS.quit

set wkb = Nothing
set XLS = Nothing

set objTestSet = Nothing

tdc.Disconnect
set tdc = Nothing

myShell.Popup "End Of Program!!!", 2, "QC Fake Execution Creation", 64

set myShell = Nothing

wscript.quit
'**********************************************************************************************

 

'**********************************************************************************************
'    Function to Retrieve Information from Excel Sheet
'**********************************************************************************************
Public Function strDataFromExcel(theSheet, strTestName, strStepName)
Dim Res, bolFound, c, r
Res = ""
bolFound = False
r = 2 'start from 2nd row
'Suppose TestName is under B Column (2nd position) and StepName is under J Column (10th position)

do while Not(bolFound)
 if theSheet.Cells(r,2).Value  = strTestName And _
    theSheet.Cells(r,10).Value = strStepName then
   
    bolFound = True
 
  else
 
    r = r + 1
   
 end if
loop

if bolFound then
 'Step is found, retrieve information about Status, Actual Result and also Tester (Responsible Tester)
 'Suppose Actual Result is on M Column, Status is on N Column and Tester is on O column
 strStatusExcel  = wks.Cells(r,14).Value
 strActualResult = wks.Cells(r,13).Value
 strTesterName   = wks.Cells(r,15).Value
 
 Res = strStatusExcel & " || " & strActualResult & " || " & strTesterName
 
end if

strDataFromExcel = Res

End Function
'**********************************************************************************************

 

Pag: <    >