Saving responses to an Excel file in Jmeter

In the dynamic landscape of software performance testing, Apache JMeter emerges as a stalwart, allowing engineers to rigorously assess the robustness and efficiency of applications, particularly APIs. This article will discuss the process of skillfully saving API responses to an Excel file using Jmeter.

We will follow below steps to save an API response in an Excel file.

Set Up a Test Plan
Create a Thread Group
Add an HTTP sampler
Add a PostProcessor
Add dependencies to interact with Excel sheets
Save responses in an Excel File

Let’s follow the above steps one by one.

Set Up a Test Plan

Launch JMeter.
Go to the “File” Menu and select “New”.
This will create a new test plan to be used in our example.

Create a Thread Group

Right-click on the Test Plan.
To create a Thread group, simply go to “Add > Threads (Users) > Thread Groups”.

The Thread Group is used to execute a specific number of tests. For simplicity, we will keep it set at 1. Please refer to the image below for configurations.

Add an HTTP sampler

We will execute a dummy API ( “https://jsonplaceholder.typicode.com/posts/1“) using an HTTP sampler. Later in the post, we will extract certain values from the API response and save them in an Excel file.

Create an HTTP sampler by right-clicking on the Thread Group and selecting “Add” > “Sampler” > “HTTP Request”.

We will hit the GET request on the “https://jsonplaceholder.typicode.com/posts/1” API. So, we will add it to the HTTP sampler by following the below steps.

Please include “https” in the input box labeled “Protocol[https]:”.
Enter “jsonplaceholder.typicode.com” in the input box labeled “Server Name or IP”.
By default, the HTTP request value is set to GET. Since we are only executing a GET request, we won’t be changing it.
Set the Path to “/posts/1“.

We will get the below response after executing the API.

{
"userId": 1,
"id": 1,
"title": "sunt aut facere repellat provident occaecati excepturi optio reprehenderit",
"body": "quia et suscipit\nsuscipit recusandae consequuntur expedita et cum\nreprehenderit molestiae ut ut quas totam\nnostrum rerum est autem sunt rem eveniet architecto"
}

We will now extract values from the response and store them in an Excel file.

Add a PostProcessor

PostProcessor in Jmeter is most often used to process the response data. We will use it to extract the values from the response we got from API and store those values in an Excel sheet.

We will use a JSR223 PostProcessor in our example. To add this PostProcessor, right-click on the HTTP sampler and select “Add” > “Post Processors” > “JSR223 PostProcessor”.

Extracting values from Response

We will write code in the PostProcessor to extract response values.

prev.getResponseData() will get us the response that we received from the API.

prev.getResponseData() returns a byte array( byte[] ). We will convert it into a string using new String(prev.getResponseData()).

// Get the response body
String responseBody = new String(prev.getResponseData());

We need to import the below packages to extract values from the response body.

import net.minidev.json.JSONObject;
import net.minidev.json.JSONValue;

Now, we can extract the necessary values with the following code.

// Parse the JSON response
JSONObject jsonResponse = (JSONObject) JSONValue.parse(responseBody);
String userId = jsonResponse.get("userId").toString();
String id = jsonResponse.get("id").toString();
String title = jsonResponse.get("title").toString();
String body = jsonResponse.get("body").toString();

The whole code will look like this –

import net.minidev.json.JSONObject;
import net.minidev.json.JSONValue;

// Get the response body
String responseBody = new String(prev.getResponseData());

// Parse the JSON response
JSONObject jsonResponse = (JSONObject) JSONValue.parse(responseBody);
String userId = jsonResponse.get("userId").toString();
String id = jsonResponse.get("id").toString();
String title = jsonResponse.get("title").toString();
String body = jsonResponse.get("body").toString();

We can add a log to check if the value is properly retrieved.

import net.minidev.json.JSONObject;
import net.minidev.json.JSONValue;

// Get the response body
String responseBody = new String(prev.getResponseData());

// Parse the JSON response
JSONObject jsonResponse = (JSONObject) JSONValue.parse(responseBody);
String userId = jsonResponse.get("userId").toString();
String id = jsonResponse.get("id").toString();
String title = jsonResponse.get("title").toString();
String body = jsonResponse.get("body").toString();

log.info("Title: "+title);

Please click on the highlighted icon to view the logs in the Log Viewer Panel. It will display logs printed with the “log.info()” function.

Now, start the execution by clicking on the “Start” icon and see the logs printed in the Logs View Panel.

We can see that the title is printed in the logs.

During our first run, we executed an API and extracted the necessary values to store in an Excel file.

Add dependencies to interact with Excel sheets

We will use Apache POI to read and write into the Excel file. For this, we would need to add the required jars in jmeter.

Download the jar files by clicking on the below download button.

Download apache poi jar files

Copy the jar files to the “lib/ext” folder inside your apache-jmeter directory.

Save responses in an Excel File

Now, we will store the extracted title in an Excel File. So, let’s make one Excel file.

We created a file named “Demo.xlsx” in the D directory with a column named “Title” to hold API response titles. So, the path of our Excel sheet is “D:\\Demo.xls” and the sheet name is “Sheet1” as shown in the below image.

We will save the title from the API’s response in the “0th cell of 1st row”

Import the packages in PostProcessor

Add below import statements in the PostProcessor to work with the Excel files using Apache POI.

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

And then, we can use the below code to write the title into the Excel sheet.

String excelFilePath = "D:\\Demo.xlsx";

try (FileInputStream inputStream = new FileInputStream(excelFilePath)){
	Workbook workbook = new XSSFWorkbook(inputStream);
Sheet sheet = workbook.getSheet("Sheet1");
int lastRowNum = sheet.getLastRowNum();

// Creating a row
Row row = sheet.createRow(lastRowNum+1);

// Setting the value at 0th cell of the row
cell = row.createCell(0);
cell.setCellValue(title); 

// Write the workbook to the Excel file
try (FileOutputStream outputStream = new FileOutputStream(excelFilePath)) {
    workbook.write(outputStream);
    log.info("Data written to Excel: " + excelFilePath);
} catch (Exception e) {
    log.error("Error writing to Excel: " + e.toString());
}
}
catch (IOException e) {
    log.error("Error reading existing Excel file: " + e.toString());
}

The code sheet.createRow(lastRowNum+1) adds a new row to the Excel sheet.
row.createCell(0) creates a new cell in the row created in Step 1.
cell.setCellValue(title) sets the value in the cell.

Note: Please close the Excel file before running the execution in Jmeter. Otherwise, you might get the below error.

JSR223 PostProcessor: Error writing to Excel: java.io.FileNotFoundException: D:\Book1.xlsx (The process cannot access the file because it is being used by another process)

Below is the whole code –

import net.minidev.json.JSONObject;
import net.minidev.json.JSONValue;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

// Get the response body
String responseBody = new String(prev.getResponseData());

// Parse the JSON response
JSONObject jsonResponse = (JSONObject) JSONValue.parse(responseBody);
String userId = jsonResponse.get("userId").toString();
String id = jsonResponse.get("id").toString();
String title = jsonResponse.get("title").toString();
String body = jsonResponse.get("body").toString();

log.info("Title: "+title);


String excelFilePath = "D:\\Demo.xlsx";

try (FileInputStream inputStream = new FileInputStream(excelFilePath)){
	Workbook workbook = new XSSFWorkbook(inputStream);
Sheet sheet = workbook.getSheet("Sheet1");
int lastRowNum = sheet.getLastRowNum();

// Creating a row
Row row = sheet.createRow(lastRowNum+1);

// Setting the value at 0th cell of the row
cell = row.createCell(0);
cell.setCellValue(title);

// Write the workbook to the Excel file
try (FileOutputStream outputStream = new FileOutputStream(excelFilePath)) {
    workbook.write(outputStream);
    log.info("Data written to Excel: " + excelFilePath);
} catch (Exception e) {
    log.error("Error writing to Excel: " + e.toString());
}
}
catch (IOException e) {
    log.error("Error reading existing Excel file: " + e.toString());
}

The code above saves the title from the API’s response to an Excel sheet. You can also save other attributes similarly.

We hope that you have liked the article. If you have any doubts or concerns, please write to us in the comments or mail us at admin@codekru.com.

Liked the article? Share this on