SoapUi : Read and Write xls (excel)

SOAPUI: Read and Write xls

 


1. Download and jexcel.zip
2. Unzip and copy paste jxl.jar into SoapUI\lib
3. Restart SoapUI .
4. Create a new Rest Project in soapui
5. Create a new testsuite>testcase>Add 2 steps = groovystep , properties step
6. Rename properties as "property
7. Add 2 property: usn , pass
8. Create a new xls file in "D:login.xls" drive like above screenshot
9. Add below script into "Groovy Step"

 Script :

import com.eviware.soapui.support.XmlHolder

import jxl.*

import jxl.write.*

// DECLARE THE VARIABLES

def myTestCase = context.testCase //myTestCase contains the test case

def counter,next,previous,size //Variables used to handle the loop and to move inside the file

propTestStep = myTestCase.getTestStepByName("property") // get the Property TestStep object

Workbook workbook1 = Workbook.getWorkbook(new File("d:\\login.xls")) //file containing the data

Sheet sheet1 = workbook1.getSheet(0) //save the first sheet in sheet1

size= sheet1.getRows().toInteger() //get the number of rows, each row is a data set

for(int i=0;i<=size-1;i++){

Cell uData = sheet1.getCell(0,i) // getCell(column,row) //obtains user

Cell pData = sheet1.getCell(1,i) // obtains password

propTestStep.setPropertyValue("usn", uData.getContents()) //the value is saved in the property

propTestStep.setPropertyValue("pass", pData.getContents()) //the value is saved in the property

Thread.sleep(2000)

}

workbook1.close()

To call the properties into request :

${property#usr}
${property#pass}