How to write data into Excel in Selenium

Write data to any external source in selenium is very important when we are working with framework.
Data can be write in any format like excel, csv, text etc.

There are many methods for write data into excel files. easiest way to read data from excel file is using apache poi.

first of all you have to import below packages for apache poi.

import org.apache.poi.ss.usermodel.DataFormatter;
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;

Now you need to create separate function for write data into excel. I have created below two functions for writing data into excel file.
First function is use for write whole table (two dimensional array) into excel.
Second function is use for write single row into excel.

Both functions can be used for write data into excel at the end of file. If file is not there at specific path then code will create first file the write data into it.

First function : 

public void mWriteDataInExcel(String vXlPath, String vSheetName, String[][] vData)
 {    
  try
  {
   File XLfile = new File(vXlPath);
   
   if(!XLfile.exists())
   {
    XLfile.createNewFile();
    FileOutputStream vOutputStream = new FileOutputStream(XLfile);
    XSSFWorkbook vWb = new XSSFWorkbook();
    XSSFSheet vSheet = vWb.createSheet(vSheetName);   
    vWb.write(vOutputStream);
    vOutputStream.close();
   }
   
   FileInputStream vInputStream = new FileInputStream(XLfile);   
   XSSFWorkbook vWorkbook = new XSSFWorkbook(vInputStream);
      
   XSSFSheet vSheet = vWorkbook.getSheetAt(0);
   Boolean bSheet = false;
      
   if(vWorkbook.getNumberOfSheets() != 0)
   {
    for(int i=0; i<vWorkbook.getNumberOfSheets(); i++)
    {
     if(vWorkbook.getSheetName(i).equals(vSheetName))
     {
      System.out.println(vSheetName + " is exists. No need to create sheet.");
      bSheet = true;      
     }     
    }
    
    if(bSheet)
    {
     vSheet = vWorkbook.getSheet(vSheetName);
    }
    else
    {
     vSheet = vWorkbook.createSheet(vSheetName);
    }
   }
   else
   {
    vSheet = vWorkbook.createSheet(vSheetName);
   }
      
   // Get the current count of rows in excel file   
   int vRowCount = 0;      
   
   if(vSheet.getLastRowNum()==0)
   {
    vRowCount = vSheet.getLastRowNum();
    System.out.println("Before add Row Number : "+vSheet.getLastRowNum());
   }
   else
   {
    vRowCount = vSheet.getLastRowNum()+1;
    System.out.println("After add Row Count : "+vRowCount);
   }
   
   for(int i=0;i<vData.length;i++)
   {       
    // Create a new row and append it at last of sheet
    Row vNewRow = vSheet.createRow(vRowCount++);
    for(int j=0;j<vData[0].length;j++)
    {
     Cell vCell2 = vNewRow.createCell(j);
     vCell2.setCellValue(vData[i][j]);
     System.out.println(vCell2);
    }    
   }   
   System.out.println("After add Row Count : "+(vSheet.getLastRowNum() + 1));
   
   vInputStream.close();
   
   FileOutputStream vOutputStream = new FileOutputStream(XLfile);   
   vWorkbook.write(vOutputStream);   
   vOutputStream.close();     
   
  }
  catch(Exception E)
  {
   System.out.println("Error in write data into excel file.");
   System.out.println(E.getMessage());
  }
     
 }

Second Function :

public void mWriteDataInExcel(String vXlPath, String vSheetName, String[] vData)
 {  
  try
  {
   File XLfile = new File(vXlPath);
   
   if(!XLfile.exists())
   {
    XLfile.createNewFile();
    FileOutputStream vOutputStream = new FileOutputStream(XLfile);
    XSSFWorkbook vWb = new XSSFWorkbook();
    XSSFSheet vSheet = vWb.createSheet(vSheetName);   
    vWb.write(vOutputStream);
    vOutputStream.close();
   }
   
   FileInputStream vInputStream = new FileInputStream(XLfile);   
   XSSFWorkbook vWorkbook = new XSSFWorkbook(vInputStream);
      
   XSSFSheet vSheet = vWorkbook.getSheetAt(0);
   Boolean bSheet = false;
      
   if(vWorkbook.getNumberOfSheets() != 0)
   {
    for(int i=0; i<vWorkbook.getNumberOfSheets(); i++)
    {
     if(vWorkbook.getSheetName(i).equals(vSheetName))
     {
      System.out.println(vSheetName + " is exists. No need to create sheet.");
      bSheet = true;      
     }     
    }
    
    if(bSheet)
    {
     vSheet = vWorkbook.getSheet(vSheetName);
    }
    else
    {
     System.out.println(vSheetName + " is not exists. So need to first create sheet.");
     vSheet = vWorkbook.createSheet(vSheetName);
    }
   }
   else
   {
    System.out.println(vSheetName + " is not exists. So need to first create sheet.");
    vSheet = vWorkbook.createSheet(vSheetName);
   }
   
   // Get the current count of rows in excel file   
   int vRowCount = 0;   
   
   if(vSheet.getLastRowNum()==0)
   {
    vRowCount = vSheet.getLastRowNum();
    System.out.println("Before add Row Count : "+vSheet.getLastRowNum());
   }
   else
   {
    vRowCount = vSheet.getLastRowNum()+1;
    System.out.println("Before add Row Count : "+vRowCount);
   }   
   
   Row vNewRow = vSheet.createRow(vRowCount);
   
   for(int i=0;i<vData.length;i++)
   {    
    Cell vCell2 = vNewRow.createCell(i);
    vCell2.setCellValue(vData[i]);
    System.out.println(vCell2);    
   }   
   System.out.println("After add Row Count : "+(vSheet.getLastRowNum() + 1));
   
   vInputStream.close();
   
   FileOutputStream vOutputStream = new FileOutputStream(XLfile);   
   vWorkbook.write(vOutputStream);   
   vOutputStream.close();     
   
  }
  catch(Exception E)
  {
   System.out.println("Error in write data into excel file.");
   System.out.println(E.getMessage());
  }
 }

Please add comment if you have any question regarding this post.

How to read data from excel in selenium

Read test data from any external source in selenium is very important when we are working with framework.
Test data can be store in any format like excel, csv, text etc.

If you are using C# in selenium then go to below URL.
How to Read Data from Excel in Selenium C#

There are many methods for reading data from excel files. easiest way to read data from excel file is using apache poi.

first of all you have to import below packages for apache poi.

import org.apache.poi.ss.usermodel.DataFormatter;
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;

Now you need to create separate function for getting data from excel. I have created below function for getting data from excel file. 

public String[][] ReadDataFromExcel(String vXlPath, int vSheetNum){
 
  File myxl = new File(vXlPath);

  FileInputStream myStream = null;

  try {

   myStream = new FileInputStream(myxl);

  } catch (FileNotFoundException e) {

   System.out.println("File not found exception in class GetDataFromExcel");

   e.printStackTrace();

  }

  

  XSSFWorkbook myWB = null;

  try {

   myWB = new XSSFWorkbook(myStream);

  } catch (IOException e) {

   System.out.println("Exception during new workbook creation in class GetDataFromExcel");

   e.printStackTrace();

  }

  XSSFSheet mySheet = myWB.getSheetAt(vSheetNum); // Referring to work sheet 

  

  int xRows = mySheet.getLastRowNum()+1; // getting the no.of rows

  int xCols = mySheet.getRow(0).getLastCellNum(); //getting the no.of columns


   System.out.println("No.of rows in excel sheet is "+xRows);

  System.out.println("No.of columns in excel sheet is "+xCols);

  

  String[][] xData = new String[xRows][xCols];

  for (int i = 0; i < xRows; i++) {

   XSSFRow row = mySheet.getRow(i);

         for (int j = 0; j < xCols; j++) {

          XSSFCell cell = row.getCell(j); // To read value from each col in each row

             String value = cellToString(cell);

             xData[i][j] = value;

         }

  }
   
  return xData;

 }

This function return two dimensional String array and we can use that array into our selenium code.

Here, you can see one another function 'cellToString' in highlighted part. This function is for covert cell value to string.

public static String cellToString(XSSFCell cell) {
  // This function will convert an object of type excel cell to a string value
  Object result;
  try{
          int type = cell.getCellType();
          DataFormatter df = new DataFormatter();
          switch (type) {
              case XSSFCell.CELL_TYPE_NUMERIC: //0
               //result = cell.getNumericCellValue();
               result = df.formatCellValue(cell);
                  break;
              case XSSFCell.CELL_TYPE_STRING: //1
                  result = cell.getStringCellValue();
                  break;
              case XSSFCell.CELL_TYPE_FORMULA: //2
                  throw new RuntimeException("We can't evaluate formulas in Java");
              case XSSFCell.CELL_TYPE_BLANK: //3
                  result = "-";
                  break;
              case XSSFCell.CELL_TYPE_BOOLEAN: //4
                  result = cell.getBooleanCellValue();
                  break;
              case XSSFCell.CELL_TYPE_ERROR: //5
                  throw new RuntimeException ("This cell has an error");
              default:
                  throw new RuntimeException("We don't support this cell type: " + type);
                  
          }
          return result.toString();
  }catch (Exception e)
  {
   System.out.println(" Exception during cell to string conversion in class GetDataFromExcel" + cell);
  }
  return null;  
   }

I have created one more function if we want data of only one particular row in excel then we can use below function.

public  String[] mGetDataFromExcel(String vXlPath, int vSheetNum, int nRow){
  
  File myxl = new File(vXlPath);
  FileInputStream myStream = null;
  try {
   myStream = new FileInputStream(myxl);
  } catch (FileNotFoundException e) {
   // TODO Auto-generated catch block
   System.out.println("File not found exception in class GetDataFromExcel");
   e.printStackTrace();
  }
  
  XSSFWorkbook myWB = null;
  try {
   myWB = new XSSFWorkbook(myStream);
  } catch (IOException e) {
   // TODO Auto-generated catch block
   System.out.println("Exception during new workbook creation in class GetDataFromExcel");
   e.printStackTrace();
  }
  XSSFSheet mySheet = myWB.getSheetAt(vSheetNum); // Referring to 1st sheet 
  int xCols = mySheet.getRow(0).getLastCellNum(); //getting the no.of columns
   
    
  String[] xData = new String[xCols];
  DataFormatter df = new DataFormatter();
  
             XSSFRow row = mySheet.getRow(nRow);
             for (int j = 0; j < xCols; j++) {
                XSSFCell cell = row.getCell(j); // To read value from each col in each row
                
                if (j >= 6 && j <= 11){
                 xData[j]  = df.formatCellValue(cell); // this gives the value of string 60 as 60, instead of 60.00
                }
                else {
                 String value = cellToString(cell);
                xData[j] = value;
              }
    }
    
    return xData;    
              
 }

Please add comment if you have any question.

If you want to read data from csv file then go to below post.
How to Read data from CSV file in Selenium

How to setup parallel execution in selenium webdriver

Parallel execution in automation testing is very important and useful for fast execution. We can do with help of selenium grid.

I will walk you through how to setup parallel execution in selenium webdriver.

First of all we have to start Hub.

Please open command prompt and navigate to directory where you have stored all selenium jar files.
type below command and press enter

java -jar selenium-server-standalone-2.53.1.jar -role hub -port 4444

Once you press enter below message should be displayed.

now we have to start node and regiter that node on Hub

Please open another command prompt and navigate to directory where you have stored all selenium jar files.
type below command and press enter

java -jar selenium-server-standalone-2.53.1.jar -role node -hub http://localhost:4444/grid/register

Once you press enter below message should be displayed.

The node is registered to the hub and ready to use

Now you all set to run parallel execution.

If you want to run execution on other than Firefox than you have to start Node with below command. Hub is same for all browser

For Chrome :

java -jar selenium-server-standalone-2.53.1.jar -role hub -port 4444 -browser browserName=chrome -Dwebdriver.chrome.driver=path of chrome driver

For IE:

java -jar selenium-server-standalone-2.53.1.jar -role hub -port 4444 -browser browserName=iexplore -Dwebdriver.ie.driver=path of ie driver

Now you have to initiate driver.

For Firefox

DesiredCapabilities capability = DesiredCapabilities.firefox();

capability.setBrowserName("firefox");

Webdriver driver = new RemoteWebDriver(new URL("http://localhost:4444/wd/hub"),capability);

For Chrome

System.setProperty("webdriver.chrome.driver","path of chrome driver");

DesiredCapabilities capability = DesiredCapabilities.chrome();
capability.setBrowserName("chrome");

Webdriver driver = new RemoteWebDriver(new URL("http://localhost:4444/wd/hub"),capability);

For IE

System.setProperty("webdriver.ie.driver","path of iedriver");

DesiredCapabilities capability = DesiredCapabilities.internetExplorer();
capability.setBrowserName("internet explorer");

Webdriver driver = new RemoteWebDriver(new URL("http://localhost:4444/wd/hub"),capability);

Selenium Basics for beginner

What is Selenium?

Selenium is very popular open-source web based automation tool now a days. Selenium is used for automation of web based application. It can not used for desktop application. Selenium is capable for cross browser testing.

Basically selenium is java based framework. It has four components. As per requirement we can use that component individually. 

1. Selenium IDE (Integrated Development Environment)
2. Selenium RC (Remote Control)
3. Webdriver
4. Selenium Grid

Selenium IDE :

Selenium IDE is the simplest framework in the Selenium. It is a Firefox plugin that you can install as easily as you can with other plugins. Selenium IDE is record-and-playback plugin. If you want to create complex and advanced test cases then you can use Webdriver and Selenium Grid. 

Selenium RC (Remote Control) :

Selenium RC is first automated web testing tool that allowed users to use a programming language they prefer. Selenium RC is supports below different programming languages.

• Java
• C#
• PHP
• Python
• Perl
• Ruby

Webdriver :

Webdriver is better than Selenium IDE and RC and it is widely used as framework for complex test cases. Webdriver implements stable approach in terms of automating different browser actions. It communicate directly with browser.

Webdriver also support many programming languages same as Selenium RC.

• Java
• C#
• PHP
• Python
• Perl
• Ruby

Selenium Grid :

Selenium Grid is tools for supporting parallel execution and testing on different browser and environments. Parallel execution means we can run multiple test cases simultaneous. This feature is achieved through Hub and Node.

This is very basic idea of selenium.