Advanced Excel Reader useful for selenium WebDriver_Selenium online Training

In this post I am going to explain how can we read and write data on excel file with the help of Java using Apache POI jar libraries.

Here we can retrieve the data from cell based on row number,column name and data is set into cell based on column name and row number.

 Download Apache POI jar libraries in following link  and configure to your project.
 Download Apache POI jar

Sample Code:
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.*;
import java.io.*;

public class ExcelReader
{
public static String filename = System.getProperty("user.dir");
public  String path;
public  FileInputStream fis = null;
public  FileOutputStream fileOut =null;
private XSSFWorkbook workbook = null;
private XSSFSheet sheet = null;
private XSSFRow row   =null;
private XSSFCell cell = null;
public static String sActionKeyword=null;

//constructor for path setting
public ExcelReader(String path)
{
this.path=path;
try
{
fis = new FileInputStream(path);
workbook = new XSSFWorkbook(fis);
sheet = workbook.getSheetAt(0);
fis.close();
}
catch (Exception e)
{
e.printStackTrace();
}

}

// returns the data from a cell based on row and column name
public String getCellData(String sheetName,String colName,int rowNum)
{
try
{
if(rowNum <=0)
return "";

int index = workbook.getSheetIndex(sheetName);
int col_Num=-1;
if(index==-1)
return "";

sheet = workbook.getSheetAt(index);
row=sheet.getRow(0);
for(int i=0;i<row.getLastCellNum();i++)
{
//System.out.println(row.getCell(i).getStringCellValue().trim());
if(row.getCell(i).getStringCellValue().trim().equals(colName.trim()))
col_Num=i;
}
if(col_Num==-1)
return "";

sheet = workbook.getSheetAt(index);
row = sheet.getRow(rowNum-1);
if(row==null)
return "";
cell = row.getCell(col_Num);

if(cell==null)
return "";
//System.out.println(cell.getCellType());
if(cell.getCellType()==Cell.CELL_TYPE_STRING)
  return cell.getStringCellValue();
else if(cell.getCellType()==Cell.CELL_TYPE_NUMERIC || cell.getCellType()==Cell.CELL_TYPE_FORMULA )
{
 
     String cellText  = String.valueOf(cell.getNumericCellValue());

return cellText;
  }
else if(cell.getCellType()==Cell.CELL_TYPE_BLANK)
      return "";
  else
  return String.valueOf(cell.getBooleanCellValue());

}
catch(Exception e)
{

e.printStackTrace();
return "row "+rowNum+" or column "+colName +" does not exist in xls";
}
}
// returns true if data is set successfully into cell using column name and row number
public boolean setCellData(String sheetName,String colName,int rowNum, String data)
{
try
{
fis = new FileInputStream(path);
workbook = new XSSFWorkbook(fis);

if(rowNum<=0)
return false;

int index = workbook.getSheetIndex(sheetName);
int colNum=-1;
if(index==-1)
return false;
sheet = workbook.getSheetAt(index);

row=sheet.getRow(0);
for(int i=0;i<row.getLastCellNum();i++)
{
//System.out.println(row.getCell(i).getStringCellValue().trim());
if(row.getCell(i).getStringCellValue().trim().equals(colName))
colNum=i;
}
if(colNum==-1)
return false;

sheet.autoSizeColumn(colNum);
row = sheet.getRow(rowNum-1);
if (row == null)
row = sheet.createRow(rowNum-1);

cell = row.getCell(colNum);
if (cell == null)
        cell = row.createCell(colNum);

  
    cell.setCellValue(data);

    fileOut = new FileOutputStream(path);

workbook.write(fileOut);

    fileOut.close();

}
catch(Exception e)
{
e.printStackTrace();
return false;
}
return true;
}
//Usage
   public static void main(String[] args) throws IOException{
  
   ExcelReader ereader = new ExcelReader("C:/Users/Sudharsan/Desktop/TestData.xlsx");
   System.out.println(ereader.getCellData("Sheet1", "Username", 2));
   ereader.setCellData("Sheet1", "Password", 2, "Sgian@1234");
  }

}

Basic Excel Reader useful for selenium WebDriver_Selenium online Training

In this post I am going to explain how can we read and write data on excel file with the help of Java using Apache POI jar libraries.

 Download Apache POI jar libraries in following link  and configure to your project
 Download Apache POI jar

Sample Code:

       package DriverMethods;

       import java.io.FileInputStream;
       import java.io.FileNotFoundException;
       import java.io.FileOutputStream;
       import java.io.IOException;
       import org.apache.poi.ss.usermodel.Cell;
       import org.apache.poi.xssf.usermodel.XSSFCell;
       import org.apache.poi.xssf.usermodel.XSSFRow;
       import org.apache.poi.xssf.usermodel.XSSFSheet;
       import org.apache.poi.xssf.usermodel.XSSFWorkbook;

        public class ExcelReader {
public String path;
public FileInputStream file;
public XSSFWorkbook workbook;
public XSSFSheet sheet;
public XSSFRow row;
public XSSFCell cell;
       public FileOutputStream fos;

       //constructor for path setting
public ExcelReader(String path) throws IOException{

this.path =path;
file = new FileInputStream(path);
workbook= new XSSFWorkbook(file);
}
//Reusable method for reading data from excel
public  String getCellData(String sheetName,int rowNum,int colNum){
int index = workbook.getSheetIndex(sheetName);
sheet =workbook.getSheetAt(index);
row = sheet.getRow(rowNum);
cell = row.getCell(colNum);
String data=cell.getStringCellValue();
return data;
}

//Reusable method for writing data to the excel
    public void setCellData(String sheetName,String data,int rowNum,int colNum) throws                IOException{

   int index = workbook.getSheetIndex(sheetName);
sheet =workbook.getSheetAt(index);
row = sheet.getRow(rowNum);

if(row==null){

row =sheet.createRow(rowNum);
}
cell = row.getCell(colNum);
if(cell==null){
cell= row.createCell(colNum);
}
  cell.setCellValue(data);

fos = new FileOutputStream(path);
workbook.write(fos);
fos.close();
   }
  
     //Usage
public static void main(String[] args) throws IOException{

ExcelReader ereader = new                                          ExcelReader("C:/Users/Sudharsan/Desktop/TestData.xlsx");
System.out.println(ereader.getCellData("Sheet1", 0, 0));
ereader.setCellData("Sheet2", "ShreyaGhosal", 11, 11);
}

}

Data read and write from a properties file useful for selenium_Selenium online Training

Generally in selenium, we used to use properties file for maintaining configuration related data of project like object locators and URL's.
In this post, I will show you how to read and write data from a properties file.

Please find below sample code for the same.

Sample code:
package DriverMethods;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Properties;

public class PropertyFileReader {
Properties prop = new Properties();
FileInputStream file;
FileOutputStream fileOut;

//Reusable method for reading data
public String readingData(String key) throws IOException{

//path of properties file
file = new FileInputStream("path of property file");

// load a properties file
prop.load(file);

//get the value of key
String out=prop.getProperty(key);
return out;

}

//Reusable method for writing data
public void writeData(String key,String data) throws IOException{

//path of properties file
   fileOut = new FileOutputStream("path of property file");

   // set the value to key
   prop.setProperty(key, data);
 
   // save properties
prop.store(fileOut, null);
 
 
}
        //Usage
public static void main(String[] args) throws IOException{
PropertyFileReader Pf = new PropertyFileReader();
System.out.println(Pf.readingData("username"));
Pf.writeData("password", "ShreyaGhosal");
}

}

Email the selenium automation reports through SMTP_Selenium online Training

In this post I am going to explain how can we Email the reports after Selenium test execution
with the help of Java using java Mail libraries.

Download Javax Mail jar libraries in following link  and configure to your project.
Download Javax Mail Jar

In this post we are going to send Email through Gmail. So we have to configure SMTP settings of it.Please find the below SMTP Settings of Gmail.

Gmail SMTP settings

Please find the sample code for the same.

Sample Code:

import java.util.Properties;

import javax.activation.DataHandler;

import javax.activation.DataSource;

import javax.activation.FileDataSource;

import javax.mail.Message;

import javax.mail.Multipart;

import javax.mail.Session;

import javax.mail.Transport;

import javax.mail.internet.InternetAddress;

import javax.mail.internet.MimeBodyPart;

import javax.mail.internet.MimeMessage;

import javax.mail.internet.MimeMultipart;

public class SendMailUtility {

//Send mail

public  boolean sendMail(String userName,

       String passWord,

       String host,

       String port,

       String starttls,

       String auth,

       boolean debug,

       String socketFactoryClass,

       String fallback,

       String[] to,

       String[] cc,

       String[] bcc,

       String subject,

       String text,

       String attachmentPath,

       String attachmentName){

              Properties props = new Properties();

      props.put("mail.smtp.user", userName);

      props.put("mail.smtp.host", host);

             if(!"".equals(port))

      props.put("mail.smtp.port", port);

              if(!"".equals(starttls))

      props.put("mail.smtp.starttls.enable",starttls);

      props.put("mail.smtp.auth", auth);

              if(debug){

              props.put("mail.smtp.debug", "true");

              }else{

              props.put("mail.smtp.debug", "false");         

              }

              if(!"".equals(port))

      props.put("mail.smtp.socketFactory.port", port);

              if(!"".equals(socketFactoryClass))

      props.put("mail.smtp.socketFactory.class",socketFactoryClass);

              if(!"".equals(fallback))

      props.put("mail.smtp.socketFactory.fallback", fallback);

      try

      {

          Session session = Session.getDefaultInstance(props, null);

          session.setDebug(debug);

          //attachment start          

          Multipart multipart = new MimeMultipart();

          MimeBodyPart messageBodyPart = new MimeBodyPart();

          DataSource source = 

          new FileDataSource(attachmentPath);

          messageBodyPart.setDataHandler(

          new DataHandler(source));

          messageBodyPart.setFileName(attachmentName);

          

          //Message body 

          messageBodyPart.addHeaderLine("Please find the attachement");

          multipart.addBodyPart(messageBodyPart);

       

          // Attachment ends

           MimeMessage msg = new MimeMessage(session);

          msg.setSubject(subject);

          msg.setContent(multipart);

        

          //msg.setText(text);

          System.out.println("text================>"+text);

          msg.setFrom(new InternetAddress(userName));

                      for(int i=0;i<to.length;i++){

          msg.addRecipient(Message.RecipientType.TO, new InternetAddress(to[i]));

                      }

                      for(int i=0;i<cc.length;i++){

          msg.addRecipient(Message.RecipientType.CC, new InternetAddress(cc[i]));

                      }

                      for(int i=0;i<bcc.length;i++){

          msg.addRecipient(Message.RecipientType.BCC, new InternetAddress(bcc[i]));

                      }

          msg.saveChanges();

                      Transport transport = session.getTransport("smtp");

                      transport.connect(host, userName, passWord);

                      transport.sendMessage(msg, msg.getAllRecipients());

                      transport.close();

                      return true;

      }

      catch (Exception mex)

      {

          mex.printStackTrace();

          return false;

      }

}   

//usage

public static void main(String[] args){

SendMailUtility utility = new SendMailUtility();

//You can send to many number of members at a time by separating comma

String[] to={"testingteam@gmail.com","tl@gmail.com"};

String[] cc={"manager@gmail.com"};

        String[] bcc={"testingteam@gmail.com@gmail.com"};

utility.sendMail(

"Sampletest@gmail.com",

           "Test@123",

            "smtp.gmail.com",

            "465",

            "true",

            "true",

            true,

            "javax.net.ssl.SSLSocketFactory",

            "false",

            to,   

            cc,

            bcc,

            "Automation Reports",                             

            "Please find the attached reports",

            "E:/WorkSpace/Practice/AutomationReports.zip",

            "AutomationReports.zip");

     System.out.println("Report has been sent through mail Successfully");

    

}

}

How to compress generated selenium automation reports into a zip file_Selenium online Training

In order to share the generated selenium webdriver reports to team or clients ,we have to zip the results folder.
In this post, I will explain how can we compress generated automation reports into a zip file using java.
find the below sample code for the same.
Sample Code:
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

public class ZIPFile {
    
    List<String> filesListInDir = new ArrayList<String>();

    //Method for ZIP folder to specific Directory
    public  void zipDirectory(File dir, String zipDirName) {
        try {
            populateFilesList(dir);
            //now ZIP files one by one
            //create ZipOutputStream to write to the ZIP file
            FileOutputStream fos = new FileOutputStream(zipDirName);
            ZipOutputStream zos = new ZipOutputStream(fos);
          
            for(String filePath : filesListInDir){
                //For ZipEntry we need to keep only relative file path, so we used substring on absolute path
                ZipEntry ze = new ZipEntry(filePath.substring(dir.getAbsolutePath().length()+1, filePath.length()));
                zos.putNextEntry(ze);
                //Read the file and write to ZipOutputStream
                FileInputStream fis = new FileInputStream(filePath);
                byte[] buffer = new byte[1024];
                int len;
                while ((len = fis.read(buffer)) > 0) {
                    zos.write(buffer, 0, len);
                }
              
                zos.closeEntry();
                fis.close();
            }
            zos.close();
            fos.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    
    //This method populates all the files in a directory to a List
    public  void populateFilesList(File dir) throws IOException {
        File[] files = dir.listFiles();
        for(File file : files){
            if(file.isFile()) filesListInDir.add(file.getAbsolutePath());
            else populateFilesList(file);
        }
    }

   //Usage
   public static void main(String[] args) throws InterruptedException{
ZIPFile zip = new ZIPFile();
        File filename = new File("E:/WorkSpace/Practice/test-output");
        zip.zipDirectory(filename, "AutomationReports.zip");
System.out.println("Zipping of AutomationReports successfully");

 }
   
 
  }

How to generate random decimal data with specific format useful for selenium_Selenium online Training

There are many web application functionalities, where we need to pass random decimal test data into input field while designing effective automation script.
In this post will illustrate how to generate random decimal data with specific format.

Generating random decimal data:
           
            //random float data
             float output = min + new Random().nextFloat() * (max - min);
     DecimalFormat decimalf = new DecimalFormat(format);

           //random double data
   double out = min + new Random().nextDouble() * (max - min);
           DecimalFormat decimal = new DecimalFormat(format);

Please find the below sample program for the same.
Sample Code:
import java.text.DecimalFormat;
import java.util.Random;

public class RandomDecimalValues{

//specific formats:
//for two decimal values - #.##
//for three decimal values - #.###
//for four decimal values - #.####
//for five decimal values - #.#####

//Generating random decimal values with specific format
public String generateDecimalData(String format,double min,double max) {
    //random double
    double out = min + new Random().nextDouble() * (max - min);
    DecimalFormat decimal = new DecimalFormat(format);
    return decimal.format(out);

}

//Usage
public static void main(String[] args){

RandomDecimalValues s = new RandomDecimalValues();
System.out.println(s.generateDecimalData("#.##",2.0d,9.0d));

}



}