In the process of testing using JMeter, sometimes it is necessary to write the test results to an Excel file. This article will explain the specific steps in detail.
Write the test results into an Excel file and analyze the operation ideas:
(1) Prepare the necessary environment
- Introduce the basic JAR package for operating Excel files:
jxl.jar. - JAR package that encapsulates custom operations in Excel
- There are generally three elements in Excel:
workbook,sheet, andcell.
If you want to store the results in the specified cell, you must determine how to position these three elements in order.
1) First get the file name of Excel.
2) Get the form name.
3) Get the coordinates of the cell. - Get the result and write it into the corresponding cell.
- You need to use BeanShell to write Java code to obtain the corresponding data and write it into Excel.
- There are generally three elements in Excel:
(2) Prepare the required data files.
(3) Write JMeter scripts, add required components, and control the execution order of components to complete the final test.
1. Environment preparation
(1) Introducing the basic JAR package for operating Excel files
- You need to use a third-party jar package:
jxl.jar. - The role of
jxl.jaris to complete reading, writing and modifying Excel files. - After downloading, place it in the
libdirectory under the installation path of JMeter.
(2) Introduce the JAR package that encapsulates the custom operation excel file
We can use Java language to customize the functions of the jxl.jar package and customize them to the functions we need.
Then package the encapsulated code into a JAR package and put it in the lib\ext directory under the JMeter installation directory.
The simple custom packaging code is as follows:
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import jxl.Cell;
import jxl.JXLException;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
public class CWOutputFile {
/**
* wOutputFile function: writes the content into the Excel file.
* wOutputFile writes the result file wOutputFile (file path, use case number, use case title, expected results, actual results, test results)
* @throwsIOException
* @throwsBiffException
* @throws WriteException
*/
public void wOutputFile(String filepath,String caseNo,String testPoint,String testData,String preResult,String fresult) throws BiffException, IOException, WriteException{
File output=new File(filepath);
String result = "";
InputStream instream = new FileInputStream(filepath);
Workbook readwb = Workbook.getWorkbook(instream);
WritableWorkbook wbook = Workbook.createWorkbook(output, readwb); //Create an operation object based on the file
WritableSheet readsheet = wbook.getSheet(0); //Locate the first sheet tab of the file
int rsRows = readsheet.getRows(); //Get the total number of rows in the sheet tab
//Get the total number of rows contained in the sheet
/******************Set font style****************************/
WritableFont font = new WritableFont(WritableFont.createFont("宋体"),10,WritableFont.NO_BOLD);
WritableCellFormat wcf = new WritableCellFormat(font);
/****************************************************** ***/
Cell cell = readsheet.getCell(0,rsRows); //Get the cells of the sheet page
if(cell.getContents().equals("")){
Label labelest1 = new Label(0,rsRows,caseNo); //First column: use case number
Label labetest2 = new Label(1,rsRows,testPoint);//Second column: use case title
Label labelest3 = new Label(2,rsRows,testData); //The third column: test data
Label labetest4 = new Label(3,rsRows,preResult);//Fourth column: expected results
Label labetest5 = new Label(4,rsRows,fresult); //The fifth column: actual results
if(preResult.equals(fresult)){ // Only when the two values are equal at the same time will they be displayed.
result = "pass"; //The expected result is the same as the actual result, the test passes
wcf.setBackground(Colour.BRIGHT_GREEN); //Mark green through the case
}
else{
result = "Failed"; //The expected result is different from the actual result, the test does not pass
wcf.setBackground(Colour.RED); // Do not mark red by case
}
Label labetest6 = new Label(5,rsRows,result,wcf);//The sixth column: test results
readsheet.addCell(labetest1);
readsheet.addCell(labetest2);
readsheet.addCell(labetest3);
readsheet.addCell(labetest4);
readsheet.addCell(labetest5);
readsheet.addCell(labetest6);
}
wbook.write();
wbook.close();
}
/**
* cOutputFile function: Create Excel file,
* tradeType is the file name prefix,
* Return result: file path, as input parameter for wOutputFile to write the result file
* @throwsIOException
* @throws WriteException
* */
public String cOutputFile(String tradeType) throws IOException, WriteException{
String temp_str = "";
Date dt = new Date();
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
temp_str = sdf.format(dt); //Get timestamp
// The relative path defaults to the JMeter_home\bin directory
// Name the result file with the timestamp to ensure uniqueness
// Generate file path
String filepath = "D:\\" + tradeType + "_output_" + "_" + temp_str + ".xls";
File output = new File(filepath);
if(!output.isFile()){
// If the specified file does not exist, create a new file
output.createNewFile();
// write file
// Create a new writeBook and create a new sheet
WritableWorkbook writeBook = Workbook.createWorkbook(output);
//Name sheet // createsheet(sheet name, sheet number)
WritableSheet sheet = writeBook.createSheet("Output result", 0);
//Set the font of the first line to Song Dynasty, size 11, bold
WritableFont headfont = new WritableFont(WritableFont.createFont("宋体"),11,WritableFont.BOLD);
WritableCellFormat headwcf = new WritableCellFormat(headfont);
headwcf.setBackground(Colour.GRAY_25); // Gray color
//Set column width setcolumnview(column number, width)
sheet.setColumnView(0, 11); //Set column width
sheet.setColumnView(1, 20);
sheet.setColumnView(2, 40);
sheet.setColumnView(3, 10);
sheet.setColumnView(4, 10);
sheet.setColumnView(5, 10); //If you need to add a new column, add it down here
headwcf.setAlignment(Alignment.CENTRE); //Set text center alignment; //Center text
headwcf.setVerticalAlignment(VerticalAlignment.CENTRE); // Set vertical centering;
Label label00 = new Label(0,0,"use case number",headwcf); //Write content: Label(column number, row number, content)
Label label10 = new Label(1,0,"Use case title",headwcf);
Label label20 = new Label(2,0,"test data",headwcf);
Label label30 = new Label(3,0,"expected result",headwcf);
Label label40 = new Label(4,0,"actual result",headwcf);
Label label50 = new Label(5,0,"execution result",headwcf); //Add below
sheet.addCell(labe00);
sheet.addCell(labe10);
sheet.addCell(labe20);
sheet.addCell(labe30);
sheet.addCell(labe40);
sheet.addCell(labe50);//Add below
writeBook.write();
writeBook.close();
}
return filepath;
}
}
Jmeter advanced performance testing practice 
https://edu.csdn.net/course/detail/35834
hint:
- Use your own IDE development tool to generate a JAR file from this file.
- When writing Java code, don’t forget to import
jxl.jarinto the project, because it encapsulates the functions injxl.jar.- I use IDEA to edit Java code and create a Maven project. Just create the file directly in the
src/main/javadirectory. If you add the Package yourself, that is, add more paths, it will become troublesome when you quote the code below. You need to import the package or add the path when calling.- The Excel file generated by the above definition is in the root directory of drive D. If it needs to be generated in other locations, modify it in the code.
- The format of the generated Excel file content can be customized and modified.
Of course, you can also write the above code directly in the BeanShell Sampler script without encapsulating it. (You will understand if you have Java basics)
2. Prepare the data needed for the test
After the code is ready, we need to prepare the corresponding test data. There are two parts of the data that need to be prepared:
- Test case data.
- Parametric data.
Note: We all use data files in
.csvformat, and pay attention to encoding UTF-8 to prevent Chinese garbled characters.
(1) Test case data
The content of the test case data file is as follows:
(2) Parameterized data
The content of the parameterized data file is as follows:
Tip: Test case data and Parameterized data can be prepared in one file, or they can be prepared separately according to your own needs.
3. Demonstration of writing test results into Excel
(1) Components included in the test plan
Steps to add components:
- Create a test plan.
- Create a thread group:
Right-click "Test Plan" -> Add -> Thread (User) -> Thread Group. - In the thread group, add the configuration element
CSV Data Set Configcomponent:right-click "Thread Group" -> Add -> Configuration Element -> CSV Data Set Config. - In the thread group, add the sampler “HTTP Request” component:
Right-click on "Thread Group" -> Add -> Sampler -> HTTP Request. - Under the sampler, add the post-processor “Regular Expression Extractor” component:
right-click "Sampler" -> Add -> Post-processor -> Regular Expression Extractor. - In the thread group, add the “Only Once Controller” component of the logical controller:
Right-click on "Thread Group" -> Add -> Logical Controller -> Only Once Controller. - In the thread group, add the sampler “BeanShell Sampler” component:
Right-click on "Thread Group" -> Add -> Sampler -> BeanShell Sampler. - In the thread group, add the listener “View Result Tree” component:
Right-click "Thread Group" -> Add -> Listener -> View Result Tree.
Tip: Components that need to be added repeatedly will not be described again here.
The components in the final test plan are as follows:
Click the Run button and you will be prompted to save the script first. After the script is saved, the script will be automatically run directly.
(2) CSV data file setting interface content
We need to add two CSV Data Set Config components, one for reading the data file of test case and the other for reading test data parameterization file.
CSV component that reads test case data:
CSV component that reads Test Data parameterized files:
Tip: Check whether there is a header line in the data file, whether it is in Chinese, whether the data has quotation marks, and configure accordingly. If you don’t understand, please read the previous parameterization article, which is very detailed.
(3) Login interface request content
For a standard POST request, fill in the basic elements of the request and the required parameters.
As shown below:
4) Regular expression extractor content
Use regular expression extractor to extract the data returned from each request for subsequent operations.
And the regular expression extractor is placed one level below the login request.
As shown in the figure below, extract all the returned content and assign it to the fresult variable.
5) BeanShell sampler (create excel)
Create a BeanShell sampler request for creating an Excel file.
Because there are multiple use cases for the login operation, but there is no need to create multiple Excel files, we need to use a logic controller to control the BeanShell request to be executed only once.
Place this BeanShell sampler request at the next level of the Only Once Controller so that the sampler will only be executed once during the entire loop, that is, only one Excel file will be generated.
As shown below:
Then in the script, write the BeanShell code:
// 1. Create a new object
t = new CWOutputExcelFile();
// 2. Create a new excel file
String filepath = t.cOutputFile("test");
// 3. Save the excel file path as a jmeter variable for later use.
vars.put("filepath",filepath);
BeanShell sampler interface content introduction:
-
Reset bsh.Interpreter before each call: Each BeanShell test element has its own copy of the interpreter (for each thread). If a test element is called repeatedly, such as within a loop, the interpreter will remain between calls unless the Reset bsh.Interpreter before each call option is selected. Some long-running tests may cause the interpreter to use large amounts of memory.
Due to the memory leak in BeanShell’sbsh.Interpreter, conventional methods cannot support long-term stress testing. JMeter official website recommends that when using BeanShell for long-term testing, turn on the optionReset bsh.Interpreterbefore each call, and reset the interpreter before each call to the BeanShell program to release the interpreter. memory occupied. -
Parameters (-> String Parameters and String[]bsh.args): Enter String parameters.String []bsh.argsis the formal parameter of the main function of the main class. It is an array of String objects and can be used to obtain the parameters entered by the command line user. -
Script file: Script file (you can fill in the script file path), you can click Browse at the back to select the script file.
-
Script (see below for variables that are defined): Write a script with reference to the variables defined below (make the script file run with reference to the defined variables)
(6) BeanShell sampler (write back results)
Write the results returned by each request interface into an Excel file. Because it needs to be executed every time it loops, it is placed at the same level as the login request.
The content of the script is as follows:
// 1. Create an object to write back the results
s = new CWOutputExcelFile();
// 2. Parameter preparation
// (1) Test data spliced into json format
String testData="{" + ""mobilephone":"" + "${mobilephone}"," + ""pwd\ ":"" + "${pwd}"" + "}";
//Use the get method to ensure that what you get is a string, and the variable name is passed inside. There is no need to use ${variable name}!
// (2) Get the expected results
// Get the variables in the parameterized file, the return value is string type, otherwise it is directly referenced as json type
String preResult = vars.get("preResult");
// (3) Get the return result
// Get the result file in the regular expression, the return value is string type, otherwise it is directly referenced as json type
String fresult = vars.get("fresult");
// 3. Write the test results to the excel file and call the wOutputFile method
s.wOutputFile("${filepath}","${caseNo}","${testPoint}",testData,preResult,fresult);
Tip: In JMeter, we often use regular expressions to obtain the parameters returned by the interface. When referencing parameters, you can directly use
${parameter name}. If this parameter does not contain any symbols that need to be escaped, then there is no problem with this reference method, otherwise JMeter will not be able to recognize it. At this time, you can usevars.get("parameter name")to change the JMeter variable into a Java variable first, and then operate it, and no error will be reported.
(7) Thread group interface content
Because we have 4 use cases, fill in 4 for the loop number. (Do not modify the number of threads)
Tip: The difference between the number of threads and the number of loops
Once-only controller executes only once for a thread group.
- If you modify the number of threads, such as 10, it will enable 10 threads to run this thread group, so the content in the Only One Controller will be executed 10 times.
- If you modify the number of loops, such as 10, it means that only one thread is opened and this thread group is run 10 times, and the content in the controller is only executed once in these 10 times.
(8) Run and check the results
Run the script to view the results. We can see that an Excel file was created once, login operations were performed 4 times, and content was written to Excel four times. This is the same process as our preview.
As shown below:
Then let’s take a look at the contents of the Excel file, as shown below
We can customize the format and the content that needs to be stored in the file according to our own needs (modify the packaging code).
Jmeter advanced performance testing practice https://edu.csdn.net/course/detail/35834