capture response xml fields into excel work sheet & Sample to Upload to QC

import org.apache.poi.hssf.usermodel.HSSFSheet;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.hssf.*;

import com.eviware.soapui.support.XmlHolder

import com.smartbear.soapui.support.*;

import com.eviware.soapui.support.*

 

// def ThisStepName = "doInquiry"

// context.setProperty("totalSteps",3)

def groovyUtils = new GroovyUtils(context)

def projpath = groovyUtils.projectPath

def tcName = context.testCase.getLabel()

def path = projpath.substring(0,projpath.length()-7)+"testData\\"+tcName+".xlsx"

def rep_path = projpath.substring(0,projpath.length()-7)+"reports\\ExcelReport_"+tcName+".xlsx"

def testCase_path = projpath.substring(0,projpath.length()-7)+"testCase\\"+tcName+".xlsx"

def flag

tCase = testRunner.testCase

tSuite = testRunner.testCase.testSuite

myproj = testRunner.testCase.testSuite.project

step0=context.testCase.getTestStepAt(context.getCurrentStepIndex()).getLabel()

//log.info step0

if (step0=="init")

{

def startime = System.currentTimeMillis()

 

def rowcount, colcount, i=0, j=0, count = 0, x

def status = "", comment = "", fieldvalue = "" 

String[][] fieldname = new String[100][100]

String[][] xpath = new String[100][100]

String[][] parameter = new String[100][100]

String stepName = context.getProperty("ThisStepName")

String[] stepNames = new String[20]

int[] colcountRespo = new int[20]

int[] colcountReq = new int[20]

 

 

Xls_Reader Datasheet = new Xls_Reader(path);

Xls_Reader Report = new Xls_Reader(rep_path)

Xls_Reader TestCase = new Xls_Reader(testCase_path)

 

 

          step1=context.testCase.getTestStepAt(context.getCurrentStepIndex()+1).getLabel()

    rowcount = Datasheet.getRowCount(step1+"_request")

colcount = Datasheet.getColumnCount(step1+"_request")

rcnt = TestCase.getRowCount("Sheet1")

    rowcountRes = rowcount

colcountRes = Datasheet.getColumnCount(step1+"_response")

Tstps = context.getProperty("totalSteps")

 

log.info "Test Execution STARTED"

 

for(x=1;x<=Tstps;x++)

{

stepNM=context.testCase.getTestStepAt(context.getCurrentStepIndex()+x).getLabel()

sheetNM = stepNM+"_response"

sheetNMreq = stepNM+"_request"

colcountRespo[x] = Datasheet.getColumnCount(sheetNM)

colcountReq[x] = Datasheet.getColumnCount(sheetNMreq)

}

for(x=1;x<=Tstps;x++)

{

stepName=context.testCase.getTestStepAt(context.getCurrentStepIndex()+x).getLabel()

colcountRes = colcountRespo[x]

colcount = colcountReq[x]

for (a=0;a<colcountRes;a++)

{

   ResponseField = Datasheet.getCellData(stepName+"_response",a,1)

   parameter[x][a] = ResponseField

             xpath[x][a] = "//"+ResponseField

     }

for (b=0;b<colcount;b++)

{

   fieldname[x][b] = Datasheet.getCellData(stepName+"_request",b,1)

}

}

 

for (i=2;i<=rowcount;i++)

{

    def TC_ID = Datasheet.getCellData(step1+"_request",0,i)

Report.setCellData("Details","TestCase_ID",i,TC_ID)

flag = 0

comment = ""

 

 

for(x=1;x<=Tstps;x++)

{

stepName=context.testCase.getTestStepAt(context.getCurrentStepIndex()+x).getLabel()

 context.setProperty("stepName1",stepName)

context.setProperty("ThisStepName",stepName)

  def request_sheet = stepName+"_request"

  def response_sheet = stepName+"_response"

  colcount = colcountReq[x]

//   log.info request_sheet

//   log.info response_sheet

  for (c=0;c<colcount;c++)

  {

   def fieldname_temp = fieldname[x][c]

 

  fieldvalue = Datasheet.getCellData(request_sheet,c,i)

  context.setProperty(fieldname_temp,fieldvalue)

  }

testRunner.runTestStepByName(context.getProperty("ThisStepName"))

def requestXml = context.expand('${${ThisStepName}#request}')

def responseXml = context.expand('${${ThisStepName}#response}')

//log.info requestXml

//log.info responseXml

Report.setCellData("Details",request_sheet,i,requestXml+"\r\n")  

Report.setCellData("Details",response_sheet,i,responseXml+"\r\n") 

fieldname[x][c] = Datasheet.getCellData(request_sheet,c,1)

colcountRes = colcountRespo[x]

  def xml = new XmlHolder(responseXml)

for(k=0;k<colcountRes;k++)

{

def ExpectedValue = Datasheet.getCellData(response_sheet,k,i)

def ActualValue = xml.getNodeValue(xpath[x][k])

context.setProperty(parameter[x][k],ActualValue)

if (ExpectedValue != "NA")

{

if (ExpectedValue != ActualValue )

{ 

flag = 1;

def sname = context.getProperty("ThisStepName") 

   status = "Failed"

   comment = comment+"\r\n\r\n Values not matching \r\n Step: "+sname +"\r\n Node : "+xpath[x][k] +"\r\n Expected value: "+ExpectedValue +"\r\n Actual value: "+ActualValue;

   Report.setCellData("Details","Actual Result",i,comment) 

  

}}

 

 }

 

}

 

for (y=2;y<=(TestCase.getRowCount("Sheet1"));y++)

{

if (TC_ID==(TestCase.getCellData("Sheet1",2,y)))

{

Report.setCellData("Details","#",i,(i-1).toString())

Report.setCellData("Details","TestCase_Name",i,TestCase.getCellData("Sheet1",3,y))

Report.setCellData("Details","Description",i,TestCase.getCellData("Sheet1",4,y))

Report.setCellData("Details","Expected Result",i,TestCase.getCellData("Sheet1",7,y))

}

}

 

  if (flag == 0)

{

status = "Passed"

comment = "As Expected"

count++

}

Report.setCellData("Details","Actual Result",i,comment) 

Report.setCellData("Details","Status",i,status) 

 

 

}

Report.setCellData("Summary","B",5,myproj.toString())

 

Report.setCellData("Summary","B",6,tSuite.toString())

Report.setCellData("Summary","B",7,tCase.toString())

 

Report.setCellData("Summary","B",10,(rowcount-1).toString())

Report.setCellData("Summary","B",11,count.toString())

Report.setCellData("Summary","B",12,((rowcount-1)-count).toString())

Report.setCellData("Summary","B",13,startime.toString())

Report.setCellData("Summary","B",16,projpath)

Report.setCellData("Summary","B",17,testCase_path)

Report.setCellData("Summary","B",18,projpath.substring(0,projpath.length()-7)+"reports\\Request_Response")

 

log.info "Execution Report:"

log.info " Number of test cases executed : "+(rowcount-1)

log.info " Number of test cases passed   : "+count

log.info " Number of test cases failed : "+((rowcount-1)-count)

log.info " Please see the report at the location:  "+rep_path

}

log.info "Done"

 

===================================================================

 

import org.codehaus.groovy.scriptom.*
import com.smartbear.soapui.support.*;
import com.jacob.activeX.ActiveXComponent
import com.jacob.com.*
import com.eviware.soapui.model.project.ProjectFactoryRegistry
import com.eviware.soapui.impl.wsdl.WsdlProjectFactory
import com.eviware.soapui.impl.WorkspaceImpl;
import com.eviware.soapui.model.project.ProjectFactoryRegistry;
import com.eviware.soapui.impl.wsdl.WsdlProjectFactory;
import com.eviware.soapui.support.types.StringToStringMap;
def groovyUtils = new com.eviware.soapui.support.GroovyUtils(context);
import com.eviware.soapui.impl.wsdl.WsdlProject

//Initialize browser for QC login
def explorer = new ActiveXObject("InternetExplorer.Application")
explorer.Navigate('http://hpqc:8080/qcbin')
def tdc = new ActiveXObject ('TDApiOle80.TDConnection')
tdc.InitConnectionEx('http://hpqc.:8080/qcbin')

// tdc.Login('USER_NAME', 'PASSWORD')
// tdc.Connect('DOMAIN_NAME','PROJECT_NAME')

tdc.Login('ub1245', 'xyz')
tdc.Connect('project name,'domain')
log.info 'QC Connection Established successfully'

//tcCount = testRunner.getTestCase().getTestSuite().getTestCaseCount()

tcCount = testRunner.testCase.getTestStepCount();
//tcCount=tcCount1-1
//tcCount=2

def i
//i iterates as per the test case count in QC

for(i=0;i<tcCount;i++)
{
//tsFolder = tdc.TestSetTreeManager.NodeByPath('\\Services\\Xyx_Services\\DistributionServices_Email') // Test case in QC
tsFolder = tdc.TestSetTreeManager.NodeByPath('Root\\CARE') \\QA test lab
log.info 'tsFolder'

tsList = tsFolder.FindTestSets('TCname')
tsObject = tsList.Item(1)


TSTestFact = tsObject.TSTestFactory
tstSetFilter = TSTestFact.Filter

// tstSetFilter.Filter["TS_NAME"] =tcCount

TestSetTestsList = TSTestFact.NewList(tstSetFilter.Text)
tsInstance = TestSetTestsList.Item(i+1)

// Create a new Test Run
newRun= tsInstance.RunFactory.AddItem('Run_Auto')
newRun.Status = 'Not Completed'


//Steps are added to identify the test case
//project = testRunner.getTestCase().getTestSuite().getProject().getWorkspace().getProjectByName('TCTestsuiteProjectName')
//path to project file as argument


//testRunner.getTestCase().getTestStepAt(i).run(testRunner,context).getError()

testStatus = testRunner.getTestCase().getTestStepAt(i).run(testRunner,context).getStatus()
log.info 'testStatus=%testStatus'

def tcName = context.testCase.getLabel() //Get Test Case Name

log.info "tcName=$tcName"

//def stepName=context.testCase.getTestStepAt(context.getCurrentStepIndex()-1).getLabel() // Get TestStep Name
//log.info "stepName=$stepName"
//def responseXml = context.expand('${${stepName}#response}')
//End of Junk added for test case identification

// Populate Auto Run Test step Data
def tsStepData = new Object[3]

tsStepData[0]='SOAP UI RUN'
def String str = testStatus
def str2 = "OK"
if( str2 == str )
{
tsStepData[1]="Passed"
}
else{
tsStepData[1]="Failed"
}
tsStepData[2]='Test Results from Automated Tests. If the status of the test case is failed then assertion value is failed.Analyze the assertion failure reason. Otherwise test case is passed'


if (tsStepData[1]=="Passed")
{
// Create new Test Step in the run with our Auto Run Data
tsSteps = newRun.StepFactory.AddItem(tsStepData)
//tsSteps.Field['ST_ACTUAL'] = testRunner.getTestCase().getTestStepAt(0).run(testRunner,context).getError()
tsSteps.Field['ST_ACTUAL'] = context.testCase.getTestStepAt(i).getProperty("Response").getValue()
//tsSteps.Field['ST_EXPECTED'] = 'These are the actual results from my test!!'
tsSteps.Field['ST_EXPECTED'] = context.testCase.getTestStepAt(i).getProperty("Response").getValue()
}
else
{
// Create new Test Step in the run with our Auto Run Data
tsSteps = newRun.StepFactory.AddItem(tsStepData)
tsSteps.Field['ST_EXPECTED'] = "Validate the Assertion value in the script"
tsSteps.Field['ST_ACTUAL'] = "Assertion Validation is Failed"
}
tsSteps.post()
newRun.Status = tsStepData[1]
newRun.post()
log.info'Updated QC TestLab Successfully'
log.info "TestStatus=$tsStepData[1]"
log.info "TotalTestCaseCount=$tcCount"
log.info "TestExecuted=${i+1}"
}
log.info'successfully executed the script from Jenkins'