Search code examples
excelfile-uploadjmeterparameterization

Jmeter : upload excel, hard coded values

I have recorded a scenario in which an excel is uploaded, in the next subsequent request those records in excel are passed as a parameters.

But suppose i need to change the excel , how that request will take new values?

Parameterization seems not the answer because of large number of values.

Pls help.

Solution

  • If you need to extract some values from Excel file and add them as HTTP Request parameters you can use the following approach.

    1. Download Apache Tika binary (tika-app-*.jar) and drop it to JMeter's /lib folder. Restart JMeter if it's running
    2. Add a Beanshell PreProcessor as a child of the request which parameters you want to modify basing on Excel file values

    3. In the "Script" are develop code which reads Excel file and adds values from it as HTTP Request parameters. Example below code extracts values from A1 and B1 cells of testfile.xlsx file and sends them as "foo" and "bar" HTTP Request parameters. 

      import org.apache.jmeter.protocol.http.sampler.HTTPSamplerProxy;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;

FileInputStream excelFile = new FileInputStream(new File("/path/to/excel/testfile.xlsx"));
XSSFWorkbook workbook = new XSSFWorkbook(excelFile);
XSSFSheet sheet = workbook.getSheetAt(0);
XSSFRow row = sheet.getRow(0);
Cell a1 = row.getCell(0);
String a1Value = a1.getStringCellValue();
Cell a2 = row.getCell(1);
String a2Value = a2.getStringCellValue();

excelFile.close();

sampler.addArgument("foo",a1Value);
sampler.addArgument("bar",a2Value);

    References: