2

My Problem: I have all my test data inside an excel + multiple sheets. I am trying to read the excel data & store it as a User Defined variable in Jmeter. I plan to use this:

  • As a variable in a Request XML I send via SOAP/XML-RPC Request
  • As a assert variable of the Response XML that I receive.

What have I done so far: I am using a similar example as in Jmeter : upload excel, hard coded values

  • The first sheet has A1 and B1 cells of testfile.xlsx file as "foo" and "bar"
  • I have downloaded tika-app-1.9.jar & added in the /lib folder of Jmeter(v2.13)

  • Added Beanshell PreProcessor & in the Script section added: 

    import org.apache.jmeter.protocol.http.sampler.WebServiceSampler;
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);

When I use the variable ${foo} in my SOAP/XML-RPC Request, the value does not get replaced with the value, but stays as ${foo}.

Any idea on what might be the problem here?

Community
  • 1
  • 1
Nish
  • 31
  • 6

1 Answers1

0

In case of SOAP/XML-RPC Request sampler you cannot use sampler.addArgument().

If you store a1Value and a2Value into JMeter variables "foo" and "bar" as:

vars.put("foo", a1Value);
vars.put("bar", a2Value);

Options are:

Beanshell Way

sampler.setXmlData("<?xml version=\"1.0\"?>\n" +
        "\n" +
        "<maintag>\n" +
        "    <sometag foo=\"" + vars.get("foo") + "\"/>\n" +
        "    <someothertag bar=\"" + vars.get("bar") + "\"/>\n" +
        "\n" +
        "</maintag>");

GUI way

Inject variables into SOAP/XML-RPC Sampler code directly like

SOAP Sampler

In regards do multiple sheets this line:

  • XSSFSheet sheet = workbook.getSheetAt(0); will return the very first sheet
  • XSSFSheet sheet = workbook.getSheetAt(1); will return 2nd sheet
  • XSSFSheet sheet = workbook.getSheetAt("MySheet"); will return sheet named "MySheet"

See How to Extract Data From Files With JMeter guide for more details on how to work with external binary files in JMeter.

Dmitri T
  • 159,985
  • 5
  • 83
  • 133