Code to update excel rows, is working in eclipse but not working in JSR223 PreProcessor/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);
                    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`
    


  • 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


Log in to reply
 

Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2