Code Anyway: Modifying/Creating Excel files using data from CSV

Hello guys, this post is to discuss how to write a bean shell code to generate excel files based upon a template file. I faced this issue when the requirement is like we have to generate multiple excel files with few cell's data modified in it.

Manual method: It requires more time as we have to copy and paste it. This method is suitable if the excel generation is one time and also if the requirement is very few excel files.

Example Scenario:

Generate 2 excel files using the template file "Sample.xlsx". These two excel files should contain data from input.csv file at first cell and third cell in the first row and at the first cell in the second row as shown below.

Before proceeding further we need the POI jar files which can be found here and you will be redirected to Apache website.

Extract the jar files into JMeter's library folder in our case we are adding all POI jar files. It is up to you whether to add all the jars or to choose the required one.

Word of caution:
After adding the jar files we have to restart/reopen JMeter to get the jar files included

Now let us open the JMeter and add the following components as shown in the following snapshot.

Use of each component is stated below,

Loop controller: This component will help us to apply loop. In our case, it will be useful for generating "n" number of files.
Counter: To increment the value and pass it to the bean shell.
CSV Data Set config: To retrieve the values from CSV file and store them in JMeter variables.
Bean shell sampler: Where we can code to write into excel file

Now let us create a CSV file having data in it as shown below,

Specify the path of the input file into CSV data set config along with three variables as we are having three columns having values in it.

In counter specify the limits and give a variable name as shown below,

In this counter, I have given reference name(JMeter's variable) as 'filenum' which will be passed into bean shell code.

Now add the following code into bean shell sampler,

//Importing the jar files

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;

//The following string represents the value from CSV

String v1=vars.get("va1");

String v2=vars.get("va2");

String v3=vars.get("va3");

//extracting the loop count to give the file name

String filenumber=vars.get("filenum");
//Creating an object with the file linked to it
FileInputStream excelFile = new FileInputStream("E:\\blog\\sample.xlsx");
//create a workbook object to excelfile
XSSFWorkbook workbook = new XSSFWorkbook(excelFile);

//Getting the sheet 0

XSSFSheet sheet = workbook.getSheetAt(0);
//accessing first row

XSSFRow row = sheet.getRow(0);
//accessing the first cell

Cell a1 = row.getCell(0);

a1.setCellValue(v1);

Cell a2=row.getCell(2);

a2.setCellValue(v2);

XSSFRow row1 = sheet.getRow(1);

Cell a3 = row1.getCell(0);

a3.setCellValue(v3);

FileOutputStream out = new FileOutputStream(

new File("E:\\blog\\sample"+filenumber+".xlsx"));