Modifying/Creating Excel files using data from CSV - JMETER

                          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,

1. Loop controller: This component will help us to apply loop. In our case, it will be useful for generating "n" number of files.
2. Counter: To increment the value and pass it to the bean shell.
3. CSV Data Set config: To retrieve the values from CSV file and store them in JMeter variables.
4. 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"));

workbook.write(out);

out.close();

Word of caution:
             Before executing close the excel files which are open so that execution will not fail.

Now after executing it we get two files generated into the folder,

 Now we can see that the excel file is contains data from CSV at specified cells.
                 -file: Sample1

                 -file: Sample2

What's Your Name?

You are given the firstname and lastname of a person on two different lines. Your task is to read them and print the following:

Hello firstname lastname! You just delved into python.

Input Format

The first line contains the first name, and the second line contains the last name.

Constraints

The length of the first and last name ≤ .

Output Format

Print the output as mentioned above.

Sample Input

Guido
Rossum

Sample Output

Hello Guido Rossum! You just delved into python.

Explanation

The input read by the program is stored as a string data type. A string is a collection of characters.

My Python 2.0 Code:

s=raw_input()
s2=raw_input()
print ("Hello ")+ s,s2+("! You just delved into python." )

Converting String into List-Python

     In this tutorial I will share you how to convert a input string into list containing each character.
In python 3 we have combined function that is input() for reading any type of input.Consider the following example
                       >>> invalue=input()
                       1234567789
                       >>> print(type(invalue))
                       <class 'str'>
                        >>> print(invalue)
                       1234567789
              Here we are using the input() function to read the string and store the value into the variable invalue. Now in order to convert this string into list we use the list() as given below

                        >>> inlist=list(invalue)
                        >>> print(type(inlist))

                        <class 'list'>
                        >>> print(inlist)
                       ['1', '2', '3', '4', '5', '6', '7', '7', '8', '9']
             From the above example we can see that the string can be converted into list using list().

Code To Convert Excel to Base64 -Jmeter

 Consider a scenario as follow like
                                1.Click on Upload button
                                2.Select the excel file to upload
                                3.Click upload
              We can record it and can correlate, but what if the excel file which is getting uploaded is converting into base64 and then getting uploaded. If this is the case we can write a simple bean shell code to convert that file into base64 string and save it into Jmeter Variable.

Below is the code for this conversion:
import java.io.*;
import org.apache.commons.codec.binary.Base64;

BufferedReader infile = new BufferedReader(new FileReader("--FileName with Extension--"));
String br;
String v1="";

/*Looping till the end of the file*/
while((br=infile.readLine())!=null)
{
/*Converting each byte to relevant base64 format */
byte[] encryptedUid = Base64.encodeBase64(br.getBytes());

/*Appending the converted base64 string */
v1=v1+new String(encryptedUid);
}

/*saving the string v1 into Jmeter variable genStringValue */
vars.put("genStringValue",v1);

Below are snapshots of the working code in Jmeter:

1.Excel file which has to be converted into base64 format

2. Jmeter Script having Bean shell code for converting the excel file into base64

3.Below picture shows the Jmeter variables in which genStringValue consists of base64 format of the excel file