How can i run correctly a complex JMeter scripts with JSR233 that read Excels?

Question

I'm developing a Java application that run JMeter scripts and then takes from the report the information used to insert into a database. The Jmeter script has got a module configuration and JSR233 sampler used to read from external an Excel in order to drive the tests and API invocation.

If i run the script form JMeter GUI i haven't problems but if i run the script from the Java code i got an error during the execution of JSR233 Sampler used to read the excel.

Following my snippet code to invoke JMeter and scripts from Java code:

package jmeter;
            
import org.apache.commons.lang3.exception.ExceptionUtils;
import org.apache.jmeter.engine.StandardJMeterEngine;
import org.apache.jmeter.report.dashboard.ReportGenerator;
import org.apache.jmeter.reporters.ResultCollector;
import org.apache.jmeter.save.SaveService;
import org.apache.jmeter.util.JMeterUtils;
import org.apache.jorphan.collections.HashTree;
import org.apache.poi.ss.usermodel.Sheet;

import java.io.File;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.Properties;

public class OpenJMX {

    public static String reportPath;

    public static String getReportPath() {
        return reportPath;
    }
    
    @SuppressWarnings("unused")
    public static String RunJmeter(Properties myPropertiesFile) throws Exception {
        try {
            // Jmeter location
            File jmeterHome = new File(System.getProperty("jmeter.home", myPropertiesFile.getProperty("JMETER_PROPERTIES_PATH")));
            String slash = System.getProperty("file.separator");
            
            // Ready to start JMX scenario location
            File testPlan = new File(System.getProperty("testPlan.location", myPropertiesFile.getProperty("SCRIPT_PATH") + myPropertiesFile.getProperty("SCRIPT_NAME")));
            
            if (jmeterHome.exists()) {
                if (testPlan.exists()) {
                    File jmeterProperties = new File(jmeterHome.getPath() + slash + "bin" + slash + "jmeter.properties");
                    if (jmeterProperties.exists()) {
                        // JMeter Engine
                        StandardJMeterEngine jmeter = new StandardJMeterEngine();

                        // Initialize Properties, locale, etc.
                        JMeterUtils.setJMeterHome(jmeterHome.getPath());
                        System.setProperty("javax.net.ssl.keyStore", jmeterHome.getPath() + slash + "bin" + slash +"OracoloCertificate.jks");
                        System.setProperty("javax.net.ssl.keyStorePassword", "changeit");
                        JMeterUtils.loadJMeterProperties(jmeterProperties.getPath());
                        JMeterUtils.findClassesThatExtend(Sheet.class);
                        JMeterUtils.initLocale();

                        // Set directory for HTML report
                        String repDir = jmeterHome.getPath() + slash + "HTMLReport";
                        JMeterUtils.setProperty("jmeter.reportgenerator.exporter.html.property.output_dir", repDir);

                        // Initialize JMeter SaveService
                        SaveService.loadProperties();

                        // Load existing .jmx Test Plan
                        HashTree testPlanTree = SaveService.loadTree(testPlan);

                        String summariserName = JMeterUtils.getPropDefault("summariser.name", "summary");
                        if (summariserName.length() > 0) {
                        }

                        // Store execution results into a .csv file
                        File logFile = setReportPath(myPropertiesFile.getProperty("REPORT_PATH"), jmeterHome);       
                        
                        //Summiraser summer = null;
                        ResultCollector logger = new ResultCollector();
                        ReportGenerator reportGenerator = new ReportGenerator(logFile.getPath(), logger); //creating ReportGenerator for creating HTML report
                        logger.setFilename(logFile.getPath());
                        testPlanTree.add(testPlanTree.getArray()[0], logger);

                        // Run JMeter Test
                        jmeter.configure(testPlanTree);
                        jmeter.run();

                    }
                }
            }
            String executionId = "OK";
            return executionId;
        }
        catch(Exception e) {
            String exc = ExceptionUtils.getStackTrace(e);       
            System.err.println(exc);
            System.exit(-1);
            return null;
        }
    }

    private static File setReportPath(String reportPathProperties, File jmeterHome) {
        try {
            String slash = System.getProperty("file.separator");
            DateTimeFormatter dtf = DateTimeFormatter.ofPattern("yyyy-MM-dd HH-mm");
            LocalDateTime now = CurrentDateTime.getTimeNow();
            File logFile;
            if(reportPathProperties.isEmpty()) {
                logFile = new File(jmeterHome + slash + "Report " + dtf.format(now) + ".jtl");
            }
            else {
                logFile = new File(reportPathProperties + slash + "Report " + dtf.format(now) + ".jtl");
            }
            reportPath = logFile.getPath();
            return logFile;
        }
        catch(Exception e) {
            String exc = ExceptionUtils.getStackTrace(e);       
            System.err.println(exc);
            System.exit(-1);
            return null;
        }

    }
}

There the code used into JSR233 Sampler to read the excel:

import org.apache.jmeter.threads.JMeterVariables;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;

String foglio = "${foglio}";
String webService = "${webService}";
String testCase = "${testCase}";
String path = "${pathProjectFiles}" + "/DATA INPUTS.xlsx";

InputStream in = new FileInputStream(new File(path));
Workbook wb = new XSSFWorkbook(in);
in.close();
Sheet sheet = wb.getSheet(foglio);


int nrRows = sheet.getLastRowNum();
int nrColumns = sheet.getRow(0).getLastCellNum();

for (int i = 1; i <= nrRows; i++) {

    if(sheet.getRow(i).getCell(0).getStringCellValue().equals(testCase)) {

        for (int j = 1; j < nrColumns; j++) {
            vars.put(webService + "_" + sheet.getRow(0).getCell(j).getStringCellValue(), sheet.getRow(i).getCell(j).getStringCellValue());
        }
        
        break;
    }

}

The errore that i got on Java application execution is:

javax.script.ScriptException: Sourced file: inline evaluation of: ``import org.apache.jmeter.threads.JMeterVariables; import org.apache.poi.ss.userm . . . '' : Typed variable declaration : Attempt to resolve method: getLastRowNum() on undefined variable or class name: sheet : at Line: 21 : in file: inline evaluation of: ``import org.apache.jmeter.threads.JMeterVariables; import org.apache.poi.ss.userm . . . '' : sheet .getLastRowNum ( ) 
 in inline evaluation of: ``import org.apache.jmeter.threads.JMeterVariables; import org.apache.poi.ss.userm . . . '' at line number 21
    at bsh.engine.BshScriptEngine.evalSource(BshScriptEngine.java:93) ~[bsh-2.0b6.jar:2.0b6 2016-02-05 05:16:19]
    at bsh.engine.BshScriptEngine.eval(BshScriptEngine.java:46) ~[bsh-2.0b6.jar:2.0b6 2016-02-05 05:16:19]
    at java.scripting/javax.script.AbstractScriptEngine.eval(AbstractScriptEngine.java:231) ~[java.scripting:na]
    at org.apache.jmeter.util.JSR223TestElement.processFileOrScript(JSR223TestElement.java:219) ~[ApacheJMeter_core-5.4.3.jar:5.4.3]
    at org.apache.jmeter.protocol.java.sampler.JSR223Sampler.sample(JSR223Sampler.java:72) ~[ApacheJMeter_java-5.4.3.jar:5.4.3]
    at org.apache.jmeter.threads.JMeterThread.doSampling(JMeterThread.java:638) ~[ApacheJMeter_core-5.4.3.jar:5.4.3]
    at org.apache.jmeter.threads.JMeterThread.executeSamplePackage(JMeterThread.java:558) ~[ApacheJMeter_core-5.4.3.jar:5.4.3]
    at org.apache.jmeter.threads.JMeterThread.processSampler(JMeterThread.java:489) ~[ApacheJMeter_core-5.4.3.jar:5.4.3]
    at org.apache.jmeter.threads.JMeterThread.run(JMeterThread.java:256) ~[ApacheJMeter_core-5.4.3.jar:5.4.3]
    at java.base/java.lang.Thread.run(Thread.java:833) ~[na:na]

It seems that the Sheet object isn't recognized despite the libraries are improted correctly.

Have you got any idea about this error?

Thanks for supporting!

Answer 1

I fail to see where you're calling getLastRowNum() function so the code you've provided is not relevant for the issue, check your other scripts and make sure you properly declare/instantiate the sheet there.

Also be aware that since JMeter 3.1 you should be using Groovy for scripting so I would suggest switching the language to groovy

As per JSR223 Sampler documentation:

The JSR223 test elements have a feature (compilation) that can significantly increase performance. To benefit from this feature:

Use Script files instead of inlining them. This will make JMeter compile them if this feature is available on ScriptEngine and cache them.

Or Use Script Text and check Cache compiled script if available property.

When using this feature, ensure your script code does not use JMeter variables or JMeter function calls directly in script code as caching would only cache first replacement. Instead use script parameters.

So you will need to change String foglio = "${foglio}"; to String foglio = vars.get("foglio"); and so on.