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`
2 Answers
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
-
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 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 TJul 1, 2019 at 12:32
-
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()