Data Driven Testing using RestAssured(Read and write data to excel)

Data-driven testing (DDT), also known as table-driven testing or parameterized testing, is a software testing method in which test data is stored in table or spreadsheet format.

We are going to do data driven testing using rest assured. Creating a post request to an api by reading the data from the excel and write the response to an excel using rest assured.

Step 1 : Create a maven project

Step 2: Add dependencies in pom.xml

We can download the below dependencies from maven central repository as we need to add to pom.xml

1. io.rest-assured

2. testng

3. json-simple

4. poi

5. poi-ooxml

6. poi-ooxml-schemas

7. jackson-databind

Step 3 : Create a new file

Create a Folder and create a package and name it. Then create a new testng class(DataDrivenTesting.java) and start coding.

Now we are going to see how we are going to read the data from the excel and send it to the post request.

For this, we need the following,

1. url to send the request.

2. Excel to read data.

For testing we are going to use this url, https://reqres.in/api/users . In the code we will be having a variable and the url assigned to it.

private static final String BASE_URL = "https://reqres.in/api/users";

Testing the url in posman by sending post request and response will looks below. This is for our reference.

Step 4: Create a excel file

Create a excel file inside the data folder in your project folder.

Below is the excel file from which data will be read and sent in the request.

We are going to declare it in our test method as below,

String excelPath = "./data/DataForTesting.xlsx";

String sheetName ="Sheet1";

Step 5 : Create ExcelUtils.java

We are now going to create a file for accessing the excel file and name it as ExcelUtils.

In the constructor, we will create these variables and assign values to it as below,

XSSFWorkbook workbook = new XSSFWorkbook(excelPath);

XSSFSheet sheet = workbook.getSheet(sheetName);

We need methods to get the cell data from the sheet and we can get it from the below method,

public static Object getCellData(int rowNum, int colNum) {

DataFormatter formatter = new DataFormatter();

Object value = formatter.formatCellValue(sheet.getRow(rowNum).getCell(colNum));

return value;

}

We will be using this method in our main code to get data from the excel sheet.

So the ExcelUtils.java file will be like this,

Step 6: Create a bean class with getters and setters

Create a java file and name it as UserInfo.java.

This class mainly has getters and setters in it for the variables which we are getting in our response.

In our case, in response we will be getting name,id,job and createdAt.

So create the private variables in these name in this class. We can generate the getters and setter by selecting all the variables and right click and select source. In that it will show an option to generate getter and setters methods and we can use it.

UserInfo class will looks like below,

Step 6: DataDrivenTesting.java

· We would already created a class. Now we are going to access the excel sheet and get the celldata by passing the excelpath and sheetname to ExcelUtils.java.

String excelPath = "./data/DataForTesting.xlsx";

String sheetName ="Sheet1";

ExcelUtils excel = new ExcelUtils(excelPath,sheetName);

· Create a JSON object for request and add the data from excel to it,

JSONObject request = new JSONObject();

request.put("name", excel.getCellData(1,0));

request.put("job", excel.getCellData(1,1));

· Now we are going to make a request by having the request in the body and response will be assigned to a Response object.

response = given().header("Content-type", "application/json").and().body(request).when().post(BASE_URL).then().extract().response();

· Get the response as string and store it in string variable

String stringToParse = response.getBody().asString();

· Using ObjectMapper, we are going to use readValue Method to deserialize JSON content from given JSON content String as below,

ObjectMapper objectMapper = new ObjectMapper();

UserInfo userInfo = objectMapper.readValue(stringToParse, UserInfo.class);

· Pass this userinfo object to the method writeResponse. In this method we are going to write our response to an excel sheet.

writeResponse(userInfo);

· Create a new workbook and a worksheet as below,

XSSFWorkbook workbook = new XSSFWorkbook();

XSSFSheet sheet = workbook.createSheet("Sheet1");

· Create a two dimensional object array,

Object[][] respData = {

{"User ID", "User Name", "Job", "CreatedAt"},

{userInfo.getId(), userInfo.getName(), userInfo.getJob(),userInfo.getCreatedAt() }

};

Create a rowcount variable and assign 0 as initial value,

int rowCount = 0;

· Using foreach loop, we are iterating through the object array and creating row and then column and setting the cellvalue

for (Object[] rData : respData) {

Row row = sheet.createRow(++rowCount);

int columnCount = 0;

for (Object field : rData) {

Cell cell = row.createCell(++columnCount);

if (field instanceof String) {

cell.setCellValue((String) field);

} else if (field instanceof Integer) {

cell.setCellValue((Integer) field);

}

}

}

· Finally we are creating a FileOutputStream object providing the excel file path and writing to the workbook.

FileOutputStream outputStream = new FileOutputStream("./data/Response.xlsx");

workbook.write(outputStream);

workbook.close();

Below are the screenshots of the DataDrivenTesting File,

Now the response will be written in the excel sheet as below.

Conclusion

We are successfully able to read data from excel file and write the response to the excel file. By following the above steps we can do data driven testing for LMS and Jobs API just by replacing the base url.

Below are the main changes, we need to do for testing any api. I will just show some changes which I did for LMS API,

· Change the base url

private static final String BASE_URL = "https://lms-program-rest-service.herokuapp.com";

· Change the request body.

response = given().auth().basic(USERNAME, PASSWORD).header("Content-type", "application/json").and().body(request).when().post(BASE_URL + "/programs").then().extract().response();

· Change the bean file and customize according to the response.

· Change the object array to the specific needs.

Object[][] respData = {{"Program ID", "Program Name", "Program Description", "Online"}, {lmsInfo.getProgramId(), lmsInfo.getProgramName(), lmsInfo.getProgramDescription(),lmsInfo.getOnline() }};

In this way we can do data driven testing for other apis.

Hope this blog helps.

Thanks!