In this example, we are going to see how can we fetch the data from Excel sheet using Apache POI and use those data to search on google.com using Selenium WebDriver. We will be also using Maven to configure dependencies for Selenium and Apache POI.
1. Introduction
Selenium is a browser automation tool. It consists of Selenium-IDE, Selenium Web Driver and Selenium Grid. We shall be using Selenium Web driver, an interface consisting of methods that can be implemented by the WebDriver class of the Browser. In this example set, we are going to use Chrome Driver API.
Apache POI is a popular open source API for reading, writing and displaying MS office documents. There are different methods available in POI API. In this example, we will be using XSSF (XML Spreadsheet Format) which is used to read xlsx. However, to read xls format you need to use HSSF class.
Environment
This example was built using following components.
- Selenium Web driver 2.5.0
- Apache POI 3.15
- Chrome Driver 2.25
- Maven 4.0
- Eclipse Version: Neon Release (4.6.0)
- JDK 1.6
- Mac OS Sierra
The Project Structure looks like below
2. Maven Dependencies
Create a Maven project and select the Group Id and Artifact Id for Selenium as shown in the picture below. Simply click on New > Maven > Maven Project and then type “Selenium”. You can also use classic way by adding the following dependency in pom.xml.
To use Default Selenium or use one of the component of Web Driver API, we can simply add following dependencies.
<dependency> <groupId>org.seleniumhq.selenium</groupId> <artifactId>selenium-java</artifactId> <version>2.40.0</version> </dependency>
Whereas for Apache POI, add the Group ID or artifact ID as below in pom.xml or as in image.
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.15</version> </dependency>
I recommend using Archetype method to add those jar as it will download all other jars, automatically which might be required, into the project folder.
3. Apache POI API
We shall be using XSSF Workbook since we will be working with .xlsx file format which is compatible with office2007 and above. The Workbook is the super-interface that belongs to org.apache.poi.ss.usermodel package and hence is used to create and write to excel workbooks. Further more, we will be also using XSS Sheet Interface and different methods available such as getRow,getCell() etc.
ExcelConfig.java
package com.javacodegeeks.seleniumexample.seleniumwithApachePOI;
import java.io.FileInputStream;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ExcelConfig {
public static XSSFWorkbook wb;
public static XSSFSheet sheet;
public ExcelConfig(String excelPath)
{
try {
FileInputStream fis = new FileInputStream(excelPath);
wb = new XSSFWorkbook(fis);
}
catch (Exception e) {
System.out.println(e.getMessage());
}
}
public String readData (int row, int column ){
sheet = wb.getSheet("data");
String data = sheet.getRow(row).getCell(column).getStringCellValue();
return data;
}
}
The path of the file has been passed as parameter to the constructor ExcelConfig and thus the file is loaded using FileInputStream. The class is surrounded with try/catch block so that if there is any exception, it will be caught in catch block and thus message will be displayed. The method readData takes row and column as its parameter whereas sheet invokes getSheet() method to load the “data” spreadsheet. The row and column methods with row and column arguments is passed to data variable and thus the result is returned.
4. Selenium Web driver
Web driver interface of Selenium provides various methods that can be implemented by instance of browser class such as Firefox, Chrome, IE etc. We will be using Chrome driver for our example set. Thus, we need to download chrome driver and then load it prior using it. It can be downloaded from the link below.
SeleniumConfig.java
package com.javacodegeeks.seleniumexample.seleniumwithApachePOI;
import org.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.WebElement;
import org.openqa.selenium.chrome.ChromeDriver;
public class SeleniumConfig {
public static void main(String [] args) throws InterruptedException{
ExcelConfig excel = new ExcelConfig("//Users//saraddhungel//desktop//Testdata.xlsx");
String exePath = "/Users/saraddhungel/Downloads/chromedriver";
System.setProperty("webdriver.chrome.driver", exePath);
WebDriver driver = new ChromeDriver();
driver.get("http://www.google.com/");
WebElement hello = driver.findElement(By.className("gsfi"));
hello.sendKeys(excel.readData(0,1));
hello.submit();
Thread.sleep(5000);
driver.close();
}
}In this java class, the chrome driver has been implemented by providing the path using “exepath” and thus property is set. After that the methods available in WebDriver interface such as get() is invoked by driver object to load the URL. WebElement interface is also implemented to invoke its method such as findElement(),sendkeys() and submit(). The browser is loaded and thus the data stored in row and column of the spreadsheet is passed using sendKeys() method which passes row and column parameter from readDatamethod. The Browser loads for 5 second and thus is closed by invokingclose() method.
After you run the project, console shows the following message and thus loads the chrome browser.
5. Conclusion
This example set is a simple attempt to demonstrate the functionality of popular APIs like Selenium web driver and Apache POI, which are built on java. Thus, using these API we can automate more data from the spreadsheets.
6. Download the Source Code
You can download the full source code of this example here:
You can download the full source code of this example here: Selenium with Maven and Apache POI
