How to read data from excel sheet using Apache POI in Java?

Here is the complete Java code to read and write from the excel sheet in Java. Please note that you will have to download and add POI library to current project from url http://poi.apache.org/download.html

The package org.apache.poi.hssf contains the xls implementations
The package org.apache.poi.xssf contains the xlsx implementations

package framework;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.Iterator;

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;

public class Excel {

 /**
  * @param args
  */
 public static void main(String[] args) {
    
FileInputStream file = null;
HSSFWorkbook workbook;

Futil.createHtmlHead(1);


try {
 
 
 
   
 file = new FileInputStream(new File("F:\\selenium\\batch.xls"));
 workbook = new HSSFWorkbook(file);
 HSSFSheet sheet = workbook.getSheetAt(0);
 Iterator<Row> rowIterator = sheet.iterator();
 int k=0;
 while (rowIterator.hasNext())  
    {
    rowIterator.next();
    k++;
    }
 
 System.out.println("Total rows in the sheet " + k);
 int intRowCounter = 1;
 Row row =  sheet.getRow(intRowCounter);
 System.out.println("Data in the excel " +  readcell(row,2));
 
 row.getCell(1).setCellValue("salunke");
 FileOutputStream fos = new FileOutputStream("F:\\selenium\\batch.xls");
    workbook.write(fos);
    fos.close();

} 
catch(Exception ex){
System.out.println("Exception occured" + ex.toString());
}

finally{
 
 try{
 file.close();
 }catch(Exception ex){
  
  System.out.println(ex.toString());
 }
 
 
}

 }//main method ends
 
 
 
//to read the data
 
 public static String readcell(Row rowObj,int colNum)
 {
  String x="";
 try{
  if  (rowObj.getCell(colNum).getCellType() == 1)
   x = rowObj.getCell(colNum).getStringCellValue();
  else if  (rowObj.getCell(colNum).getCellType() == 0)
   x = "" + (int)rowObj.getCell(colNum).getNumericCellValue();
 }
 catch(Exception e){
  x = "";
 //System.out.println(e.toString() + " while reading a cell");
  }
  
  return x;
 }
 
 
 //to write the data
 public static void writeCell(Row rowObj,int colNum, String data)
 {
  
  try{
    
    rowObj.getCell(colNum).setCellValue((String) data);
   
  }
  catch(Exception e){
  
   System.out.println(e.toString() + " while writing a cell");
  }
  
  
 }
 
 

} //class ends

You may also like to read below topics.

1. Press key in Selenium Webdriver in Java
2. Read Table Data in Selenium Webdriver in Java
3. Take the screenshot in Selenium Webdriver in Java
4. Execute JavaScript in Selenium Webdriver in Java
5. Handle multiple browser windows in Selenium Webdriver in Java
6. Double-Click on the Element in Selenium Webdriver in Java
7. Exceptions in Selenium Webdriver in Java
8. Synchronization in Selenium Webdriver in Java
9. Frameworks in Selenium

What do you think on above selenium topic. Please provide your inputs and comments. You can write to me at reply2sagar@gmail.com

Selenium Webdriver Tutorial in Java

In this tutorial on Selenium in Java, we are going to look at below topics.

1. Introduction
2. Selenium Architecture
3. Webdriver JSON Wire protocol
4. Installation - Selenium + Eclipse + Jar ,  Maven + Selenium , IntelliJ + selenium + gradle
5. Browsers - Launching various browsers like IE, Chrome, Firefox, Microsoft Edge, Safari, HtmlUnit
6. Capabilities - Selenium Webdriver Capabilities Significance
7. Basic operations - Closing browser window, Navigating to url, Getting window title, Maximizing browser window, Navigate back and forward, How to get current url, How to refresh page, How to get html source code of current page, Resizing and moving browser window
8. Element identification - Element Identification methods, Check if element really exists on page , Check if pop up really exists on page
9. Element operations - Clicking buttons, links or any other web elements , Entering data in text boxes , Selecting the value from the web list , Reading all items or values from the drop down box , Selecting the check box , Reading the value from the list box , Uploading file , Verify if button is disabled, Verify if button is enabled , Verify if check box is selected, Verify if radio button is selected, Verify if element is displayed on web page, Reading any attribute value , Reading CSS value , Press Tab key using Selenium,  Press Enter F1 key in Selenium, Press control, shift and delete keys in Selenium, Clearing data from text box, Verify if any text is present
10. Synchronization - Adding synchronization points - (implicit wait, explicit wait, wait until page loads), How to avoid Thread.Sleep in Selenium , wait until element is displayed, Wait Conditions,   wait until element disappears, wait until page title changes, check if element exists, Fluent Wait
11. Advanced XPATH and CSS expressions - Advanced XPATH expressions to identify the elements , Finding relative elements using XPATH,  Advanced CSS expressions
12. Working with tables - Reading table cell value , Reading data from web tables , Finding total number of rows and columns
13. Window related things - Uploading a file, Downloading file
14. Actions - Performing drag and drop action , Double clicking the web element, Performing mouse hover action , Opening context menu (right clicking) on webpage
15. Exceptions - Exceptions in Selenium, Common errors and exceptions 
16. Switching context - Handling multiple browser windows (tabs) , Handling alerts (modal dialogs) , Switching to frames, Handling window dialog in Selenium , Switching to default content
17. JavascriptExecutor - Executing custom Javascript , How to scroll to element in Selenium , Click not working Selenium, Enter data in text box using JavaScript, Selecting drop down value using JavaScript
18. Frameworks with selenium - Keyword driven frameworks in Selenium , Reading and writing data in excel file, Page object and factory models, JUnit and Selenium, TestNG with Selenium , Data driven, Hybrid, Framework Utilities (Killing processes, date and time operations, String conversions, Random numbers, Generating uniue random numbers), Taking a screenshot in Selenium , Logging framework in Selenium, Advanced reporting using screenshots and videos
19. BDD frameworks - JBehave, Cucumber, Gauge
20. Miscellaneous - xvfb and vnc in selenium, Setting Proxy, Selenium IDE, Chrome plugin to create page object models, Hide and Minimize the browser, EventListeners in Selenium
21. Integration with CI servers - TeamCity, Jenkins, Bamboo
22. Selenium in Cloud - SauceLabs and BrowserStack
23. Mobile application testing  - Appium (Android and iOS web/native/hybrid applications), Mobile emulation using chrome, Selendroid
24. Selenium grid - RemoteWebdriver in Selenium, Selenium Grid
25. Comparing selenium with other tools - Difference between Selenium Webdriver and UFT (QTP) , Difference between Selenium and UFT, Difference between Selenium and Lean FT,  Difference between Selenium and Appium
26. Challenges in Selenium automation - Limitations of Selenium, Handling windows dialog using AutoIT

Selenium Interview questions and answers in Java

1. Basic Selenium Interview Questions
2. Advanced Selenium Interview Questions

What do you think on above selenium topic. Please provide your inputs and comments. You can write to me at reply2sagar@gmail.com

Keyword driven automation framework in Selenium Webdriver in Java

Keyword driven Automation Framework is very popular framework used in Selenium Webdriver with Java.
In this article I will explain you all details about how we can design and use keyword driven automation framework in Selenium Webdriver with Java along with example.

Keyword driven automation framework in Selenium Webdriver with Java - Introduction

In keyword driven automation framework we create the methods in Java that are mapped to the functionality of the application.

For example -
Suppose you have a bus ticket booking application which provides many features like

1. Login to the bus booking website 
2. Search Buses with given source and destination and time
3. Select the bus tickets
4. Book bus tickets
5. Cancel bus tickets
6. View the booking history 
7. Make the payment.

To automate the test cases for such web applications, We usually write the methods that perform specific task. For example we may write the searchBus method in Java which will search the buses for given source city, Destination city and Date.

Similarly we will create the methods for each functionality. The advantage of creating methods is that we can re-use these methods in multiple test cases with different input test data.
This speeds up the automation process with increased productivity.

The Components of keyword driven automation framework in Selenium Webdriver with Java

Each keyword driven automation framework has some common components as mentioned below.

1. Java Class Library with functionality specific methods.
2. Test Data Sheet (generally in excel format)
3. Selenium Webdriver with Java.
4. Reports in HTML format)
5. Main Java driver script

1. Java Class Library with functionality specific methods.

As explained earlier, we can create methods for each functionality in the application like bookTicket, makePayment etc.

Sample Java method is shown below to login to the web application.

public static boolean Login()

{

   

driver.navigate().to("http://www.abc.com");

WebElement e1 = driver.findElement(By.id("UserName"));

e1.sendKeys("userid");

WebElement e2 = driver.findElement(By.id("Password"));

e2.sendKeys("password");

WebElement e3 = driver.findElement(By.name("submit"));

e3.click();

Boolean isPresent = driver.findElements(By.className("logoutlink")).size()>0;

if (isPresent == true)

{

//System.out.println("Login was successful");

       return true;

}

else

{

       //System.out.println("Login was not successful");

       return false;

}

}

//Please note that you can write the methods to perform more complex operations in similar //fashion

2. Test Data Sheet in Selenium Webdriver framework

As displayed in below figure, the data sheet contains below columns.

1. ID -Manual test case ID
2. Test_Case_Name - Name of the test case
3. Exec_Flag - Execution flag to tell if you want to execute this test case. Y means that test will be executed
4. Test_Step_Id - Step Id of the Test case
5. Test_Case_Steps - Step name of the test case
6. Keyword - The name of method in function library you want to call.
7. objectTypes - type of the web elements like webedit, weblist, webcheckbox etc
8. objectNames - Web element identification method and expression like xpath://td
9. objectValues - actual test data you want to enter in the webElement 
10. parameter1 - extra parameter to drive the method control
11. parameter2 - one more parameter to drive the method control

Sample test Datasheet in selenium webdriver in Java

3. Selenium Webdriver in Java

This is the driver instance you will create to execute the test cases.

Sample code to create a web driver instance is given below.

System.setProperty("webdriver.chrome.driver", "F:\\selenium\\chromedriver.exe");

WebDriver driver =  new ChromeDriver();

4. Creating html Reports in Selenium Webdriver automation framework

You can create the html reports using file handling mechanism in Java.

Below code will delete the existing report file

if ((new File(c:\\reportfile.html)).exists() )

   (new File(c:\\reportfile.html)).delete();

Below code will append the data to the existing report file stored at given filePath

public static void appendToFile(String filePath,String data) {

       //This function will be used to append text data to filepath

       try{

       File temp = new File(filePath);

       FileWriter fw = new FileWriter(temp,true);

       fw.append(data);

       fw.close();

       }catch(Exception e){}

}

Please note that you will need to import the classes in the package  java.io to work with files.

5. Main driver script in Selenium webdriver automation framework

This is the main method and starting point for the framework code. The main responsibilities of this method are given below.

1. Read the test case steps from the datasheet one row at a time
2. Execute the method corresponding to the current step in the test case
3. Log the verification points in the html report file
4. Report the overall execution status like total failed/passed test cases, execution time
5. Send an email to all stakeholders regarding the execution status.

You will need to import the excel library provided by org.apache.poi

You may also like to read below topics.

1. Press key in Selenium Webdriver in Java
2. Read Table Data in Selenium Webdriver in Java
3. Take the screenshot in Selenium Webdriver in Java
4. Execute JavaScript in Selenium Webdriver in Java
5. Handle multiple browser windows in Selenium Webdriver in Java
6. Double-Click on the Element in Selenium Webdriver in Java
7. Exceptions in Selenium Webdriver in Java
8. Synchronization in Selenium Webdriver in Java

If you want to download the full source code, data sheets, sample scripts and examples, Please buy an e-book on selenium webdriver in Java at the url - Selenium Webdriver Book in Java

What do you think on above selenium topic. Please provide your inputs and comments. You can write to me at reply2sagar@gmail.com

How to perform drag and drop operation in selenium webdriver in Java?

We can drag and drop the elements using Actions class in selenium webdriver in Java.

Below class/Interface must be imported before performing drag and drop in Selenium Webdriver.

import org.openqa.selenium.interactions.Action;

import org.openqa.selenium.interactions.Actions;

//create Actions object

Actions builder = new Actions(driver);

//create a chain of actions to move element e1 to e2

Action dragAndDropAction = builder

  .clickAndHold(e1)

  .moveToElement(e2)

  .release(e2)

  .build();

//perform drag and drop action

 dragAndDropAction.perform();

What do you think on above selenium topic. Please provide your inputs and comments. You can write to me at reply2sagar@gmail.com

How to double click on the web element in Selenium Webdriver in Java?

We can perform all kinds of mouse and keyboard events in selenium webdriver using Actions interface.

Please note that we need to import below classes/interfaces to perform below operations.

import org.openqa.selenium.interactions.Action;

import org.openqa.selenium.interactions.Actions;

Java Example to double click on Webelement in Selenium Webdriver

package temp;

import java.io.File;

import java.util.concurrent.TimeUnit;

import org.apache.commons.io.FileUtils;

import org.openqa.selenium.By;

import org.openqa.selenium.Keys;

import org.openqa.selenium.OutputType;

import org.openqa.selenium.TakesScreenshot;

import org.openqa.selenium.WebDriver;

import org.openqa.selenium.WebElement;

import org.openqa.selenium.chrome.ChromeDriver;

import org.openqa.selenium.firefox.FirefoxDriver;

import org.openqa.selenium.interactions.Action;

import org.openqa.selenium.interactions.Actions;

@SuppressWarnings("unused")

public class OpenGoogle {

   

public static void main(String [] arg)

{

System.setProperty("webdriver.chrome.driver", "C:\\SelenuimProject\\chromedriver2.8.exe");

WebDriver driver =  new ChromeDriver();

try{

driver.manage().timeouts().implicitlyWait(20, TimeUnit.SECONDS);

driver.manage().timeouts().pageLoadTimeout(50,TimeUnit.SECONDS);

driver.get("http://www.google.com/");

Thread.sleep(3000);

WebElement element = driver.findElement(By.id("hplogo"));

Actions builder = new Actions(driver);

//build the action chain.

Action doubleclick = builder.doubleClick(element).build();

//perform the double click action

doubleclick.perform();

Thread.sleep(8000);

}

catch(Exception e){

       System.out.println("Exception - > " + e.toString());

       }

       finally{

              driver.close();

              driver.quit();

       }

}      //main function ends

      

}//class ends

What do you think on above selenium topic. Please provide your inputs and comments. You can write to me at reply2sagar@gmail.com

How to open the context menu in selenium webdriver in Java?

We can perform all kinds of mouse and keyboard events in selenium webdriver using Actions interface.

Please note that we need to import below classes to perform below operations.

import org.openqa.selenium.interactions.Action;

import org.openqa.selenium.interactions.Actions;

Java Example to right click (opening Context menu) on Webelement in Selenium Webdriver

package temp;

import java.io.File;

import java.util.concurrent.TimeUnit;

import org.apache.commons.io.FileUtils;

import org.openqa.selenium.By;

import org.openqa.selenium.Keys;

import org.openqa.selenium.OutputType;

import org.openqa.selenium.TakesScreenshot;

import org.openqa.selenium.WebDriver;

import org.openqa.selenium.WebElement;

import org.openqa.selenium.chrome.ChromeDriver;

import org.openqa.selenium.firefox.FirefoxDriver;

import org.openqa.selenium.interactions.Action;

import org.openqa.selenium.interactions.Actions;

@SuppressWarnings("unused")

public class OpenGoogle {

   

public static void main(String [] arg)

{

System.setProperty("webdriver.chrome.driver", "C:\\SelenuimProject\\chromedriver2.8.exe");

WebDriver driver =  new ChromeDriver();

try{

driver.manage().timeouts().implicitlyWait(20, TimeUnit.SECONDS);

driver.manage().timeouts().pageLoadTimeout(50,TimeUnit.SECONDS);

driver.get("http://www.google.com/");

Thread.sleep(3000);

WebElement element = driver.findElement(By.id("hplogo"));

Actions builder = new Actions(driver);

//build the action chain.

Action rightclick = builder.contextClick(element).build();

//perform the action

rightclick.perform();

Thread.sleep(8000);

}

catch(Exception e){

       System.out.println("Exception - > " + e.toString());

       }

       finally{

              driver.close();

              driver.quit();

       }

}      //main function ends

      

}//class ends

What do you think on above selenium topic. Please provide your inputs and comments. You can write to me at reply2sagar@gmail.com