0 
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Iterator;
import java.util.Date;
import java.sql.Timestamp;

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelExample {

    public static void main(String[] args) throws IOException {

        try {

FileInputStream file = new FileInputStream(new File("${filePath}"));

            //HSSFWorkbook workbook = new HSSFWorkbook(file);
            XSSFWorkbook workbook = new XSSFWorkbook(file);
            XSSFSheet sheet = workbook.getSheetAt(0);
            System.out.println("Sheet -- "+sheet);

            Date date= new Date(0);

            long time = date.getTime();


            Timestamp ts = new Timestamp(time);
            for(int i=1;i<=100;i++) {
                Row row = sheet.getRow(i);
                System.out.println("Row --- "+row);
                row.getCell(0).setCellValue("Order"+new Date().getDate()+"_"+System.nanoTime());
            }

            file.close();

            FileOutputStream outFile =new FileOutputStream(new File("${filePath}"));
            workbook.write(outFile);
            outFile.close();


        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}`enter code here`
Improve this question

2 Answers 2

Reset to default
1

Your code is not doing anything because it's not being called at all. JMeter's JSR223 Test Elements don't automatically execute Java main method, you will need to explicitly call it by adding the next line to the end of your script:

ExcelExample.main()

Full code just in case:

import org.apache.poi.ss.usermodel.Row
import org.apache.poi.xssf.usermodel.XSSFSheet
import org.apache.poi.xssf.usermodel.XSSFWorkbook

import java.sql.Timestamp

public class ExcelExample {

    public static void main(String[] args) throws IOException {

        try {

            FileInputStream file = new FileInputStream(new File("${filePath}"));

            //HSSFWorkbook workbook = new HSSFWorkbook(file);
            XSSFWorkbook workbook = new XSSFWorkbook(file);
            XSSFSheet sheet = workbook.getSheetAt(0);
            System.out.println("Sheet -- " + sheet);

            Date date = new Date(0);

            long time = date.getTime();


            Timestamp ts = new Timestamp(time);
            for (int i = 1; i <= 100; i++) {
                Row row = sheet.getRow(i);
                System.out.println("Row --- " + row);
                row.getCell(0).setCellValue("Order" + new Date().getDate() + "_" + System.nanoTime());
            }

            file.close();

            FileOutputStream outFile = new FileOutputStream(new File("${filePath}"));
            workbook.write(outFile);
            outFile.close();


        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

ExcelExample.main()

Another option is to remove Java class/method declarations like:

import org.apache.poi.ss.usermodel.Row
import org.apache.poi.xssf.usermodel.XSSFSheet
import org.apache.poi.xssf.usermodel.XSSFWorkbook

import java.sql.Timestamp

try {

    FileInputStream file = new FileInputStream(new File("${filePath}"));

    //HSSFWorkbook workbook = new HSSFWorkbook(file);
    XSSFWorkbook workbook = new XSSFWorkbook(file);
    XSSFSheet sheet = workbook.getSheetAt(0);
    System.out.println("Sheet -- " + sheet);

    Date date = new Date(0);

    long time = date.getTime();


    Timestamp ts = new Timestamp(time);
    for (int i = 1; i <= 100; i++) {
        Row row = sheet.getRow(i);
        System.out.println("Row --- " + row);
        row.getCell(0).setCellValue("Order" + new Date().getDate() + "_" + System.nanoTime());
    }

    file.close();

    FileOutputStream outFile = new FileOutputStream(new File("${filePath}"));
    workbook.write(outFile);
    outFile.close();


} catch (FileNotFoundException e) {
    e.printStackTrace();
} catch (IOException e) {
    e.printStackTrace();
}

More information: Apache Groovy - Why and How You Should Use It

Improve this answer
3
  • Thanks for your answer. I tried both ways getting below error in logs. 2019-07-01 17:57:14,327 ERROR o.a.j.p.j.s.JSR223Sampler: Problem in JSR223 script JSR223 Sampler, message: javax.script.ScriptException: In file: inline evaluation of: import org.apache.poi.ss.usermodel.Row import org.apache.poi.xssf.usermodel.XSSF . . . '' Encountered "import" at line 2, column 1. in inline evaluation of: import org.apache.poi.ss.usermodel.Row import org.apache.poi.xssf.usermodel.XSSF . . . '' at line number 2 javax.script.ScriptException: In file: inline evaluation of: ``import
    – Abhishek SIngh
    Jul 1, 2019 at 12:29
  • I'm not enough telepathic to tell you how to solve the error without seeing its details, my expectation is that you need to have Apache POI libraries in JMeter Classpath, check out How to Implement Data Driven Testing in your JMeter Test article for comprehensive instructions
    – Dmitri T
    Jul 1, 2019 at 12:32
  • Thanks . Solution worked.
    – Abhishek SIngh
    Jul 3, 2019 at 9:32
0

I did it by adding the below code in JSR223 Sampler.

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Iterator;
import java.util.Date;
import java.sql.Timestamp;

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelExample {

    public static void main(String[] args) throws IOException {

        try {

FileInputStream file = new FileInputStream(new File("${filePath}"));

            //HSSFWorkbook workbook = new HSSFWorkbook(file);
            XSSFWorkbook workbook = new XSSFWorkbook(file);
            XSSFSheet sheet = workbook.getSheetAt(0);
            System.out.println("Sheet -- "+sheet);

            Date date= new Date(0);

            long time = date.getTime();


            Timestamp ts = new Timestamp(time);
            for(int i=1;i<=100;i++) {
                Row row = sheet.getRow(i);
               System.out.println('Row --- '+row);
               def ordStr = "${__time(ddMMyyyymmss,)}_${__Random(1,100)}";
               int threadId = ${__eval(${__threadNum})};
               row.getCell(0).setCellValue("Order"+"_"+threadId+"_"+new Date().getDate()+"_"+System.nanoTime());

            }

            file.close();

            FileOutputStream outFile =new FileOutputStream(new File("${filePath}"));
            workbook.write(outFile);
            outFile.close();

        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}
ExcelExample.main()
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.