ReadyAPI - parsing an excel data source by column

Question

In ReadyAPI, an excel data source can be configured and read in a data source test step. However, this does not give an option to read in column order (that I can see).

Is there an option in ReadyAPI for reading in data in column order (instead of row order)?

If not, is there a groovy library for excel/example of this?

Answer 1

@MaxRussell Sorry to be the bearer of bad news. I asked my Account Rep to check with the engineers to see if there is a way to change the direction that ReadyAPI reads EXCEL files. This is the answer I got... "I asked and it looks like right now they do not know a way to change how ReadyAPI reads the sheet. ReadyAPI looks at an excel sheet like a data table, rows as indexes and columns as data within the index."

This answer came back mid-September 2021 and is with ReadyAPI v3.9.2.

HOWEVER... I have worked up some Groovy code that seems to work to do it...

// IMPORT THE LIBRARIES WE NEED

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

Workbook workbook1 = Workbook.getWorkbook(new File("location of excel file.XLS"))  // Had an issue using XLSX, so switched back to XLS

Sheet sheet1 = workbook1.getSheet(0) //Index 0 will read the first sheet from the workbook, you can also specify the sheet name with "Sheet1"
//Sheet sheet1 = workbook1.getSheet("Sheet1")


size = sheet1.getColumns().toInteger()  // Get number of columns with data
length = sheet1.getRows().toInteger()   // get number of rows with data

def fieldArray = new String[length]

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

counter = 1  //counter variable will eventually be the iteration number

next = (counter > size-2? 0: counter+1) //set the next value

// OBTAINING THE DATA from the spreadsheet
int i = 0

while (i < length) {
    Cell f1 = sheet1.getCell(counter,i)
    
    fieldArray[i] = f1.getContents()
    i++
}


workbook1.close() //close the file


// Apply all of the values to the properties in the test case

def numberOfProperties = myTestCase.properties.size()  // get the number of properties
def tempMap = myTestCase.propertyNames as String[];  // put all of the property names into an array

i = 0
while (i < length) {
    myTestCase.setPropertyValue(tempMap[i], fieldArray[i])
    i++
}

Please note that it is not a completed work, but I am loading 17 values from column B into the properties to be used as data. This should at least get you moving the right direction.