• Selenium Video Tutorials

Selenium WebDriver - Excel Data File

Previous
Quiz
Next

Selenium Webdriver can be used to interact with the excel data file. Often in an automation test, there are requirements which has large amount of data to be fed through an excel file for a test case. This is done to verify a specific scenario or to create a data driven framework.

Java gives a large option of classes and methods to carry read and write data operations on an excel file using the Apache POI libraries. An Apache POI API is a group of free, and open source Java libraries.

What is Apache POI?

Apache POI is used to work with Microsoft files. It can be used to carry on read, write, update, and other operations on files of various formats including excels. Java cannot be used directly to work with excel files, hence Apache POI (a third party API) is used along with Java, to create Selenium tests which require data to be fed from excel.

The excel workbooks can be handled with Apache POI using the Workbook interface. This interface utilizes the WorkBookFactory class to produce the specific workbooks. The HSSFWorkbook class (implements the Workbook interface) has methods which help to carry out read and write operations to Microsoft Excel files having the .xls format. The XSSFWorkbook class (implements the Workbook interface) has methods which help to carry out read and write operations to Microsoft Excel and OpenOffice XML files having the .xlsx or .xls formats.

Similarly, the excel worksheets can be handled with Apache POI using the Sheet interface. The HSSFSheet class (implements the Sheet interface) has methods to create a worksheet in HSSFWorkbook workbooks (Microsoft Excel files having the .xls format). The XSSFSheet class(implements the Sheet interface) has methods to create a worksheet in XSSFWorkbook workbooks (Microsoft Excel and OpenOffice XML files having the .xlsx or .xls formats).

The excel rows can be handled with Apache POI using the Row interface. The HSSFRow class (implements the Row interface) has methods which signify rows in the HSSFSheet. The XSSFRow class (implements the Row interface) has methods which signify rows in the XSSFSheet.

The excel cells can be handled with Apache POI using the Cell interface. The HSSFCell class (implements the Row interface) has methods which handle cells in the HSSFRow. The XSSFCell class (implements the Row interface) has methods which handle cells in the XSSFRow.

How to Install the Apache POI?

Step 1 − Add Apache POI Common dependencies to the pom.xml file from the link Apache POI Common.

Step 2 − Add Apache POI API Based On OPC and OOXML Schemas dependencies to the pom.xml file from the link Apache POI API Based.

Step 3 − Save the pom.xml with all the dependencies and update the maven project.

Read all Values From an Excel

Let us take an example of the below excel named the Details.xlsx file, where we will read the whole excel file and retrieve all its values.

Selenium Excel Data File 1

Please Note: The Details.xlsx file was placed within the project under the Resources folder as shown in the below image.

Selenium Excel Data File 2

Example

package org.example;

import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;

public class ExcelRead {
   public static  void main(String args[]) throws IOException {

      // identify location of .xlsx file
      File f = new File("./Resources/Details.xlsx");
      FileInputStream i = new FileInputStream(f);

      // instance of XSSFWorkbook
      XSSFWorkbook w = new XSSFWorkbook(i);

      // create sheet in XSSFWorkbook with name Details1
      XSSFSheet s = w .getSheet("Details1");

      // handle total rows in XSSFSheet
      int r = s.getLastRowNum() - s.getFirstRowNum();

      // loop through rows
      for(int k = 0; k <= r ; k++){
      
         // get cells in each row
         int c = s.getRow(k).getLastCellNum();

         for(int j = 0; j < c; j++){
         
            // get cell values
            System.out.println(s.getRow(k).getCell(j).getStringCellValue());
         }
      }
   }
}

Dependencies added to pom.xml.

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
   xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 
   http://maven.apache.org/xsd/maven-4.0.0.xsd">

   <modelVersion>4.0.0</modelVersion>
   <groupId>org.example</groupId>
   <artifactId>SeleniumJava</artifactId>
   <version>1.0-SNAPSHOT</version>

   <properties>
      <maven.compiler.source>16</maven.compiler.source>
      <maven.compiler.target>16</maven.compiler.target>
      <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
   </properties>

   <!-- https://mvnrepository.com/artifact/org.seleniumhq.selenium/selenium-java -->
   <dependencies>
      <dependency>
         <groupId>org.seleniumhq.selenium</groupId>
         <artifactId>selenium-java</artifactId>
         <version>4.11.0</version>
      </dependency>

      <!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
      <dependency>
         <groupId>org.apache.poi</groupId>
         <artifactId>poi</artifactId>
         <version>5.2.5</version>
      </dependency>

      <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
      <dependency>
         <groupId>org.apache.poi</groupId>
         <artifactId>poi-ooxml</artifactId>
         <version>5.2.5</version>
      </dependency>
   </dependencies>
</project>

Output

Name
Street
Ram
Street 12
Rohan
Street 110

Process finished with exit code 0

In the above example, we had read the whole excel file and obtained all its value in the console.

Finally, the message Process finished with exit code 0 was received, signifying successful execution of the code.

Read and Write Values in an Excel

Let us take an example of the below excel named the DetailsStudent.xlsx file, where we would read the value from that excel file and input those data to the below registration page and once successfully done, we would write the text - Test Case: Pass in the cell(at same row and Column E). If not successfully done, we would write the text - Test Case: Fail in that same cell.

Selenium Excel Data File 3

The below image shows the registration page where we would enter data in the fields Full Name:, Last Name:, Username:, and Password from the DetailsStudent.xlsx file.

Selenium Excel Data File 4

Please Note − The DetailsStudent.xlsx excel file was placed within the project under the Resources folder as shown in the below image.

Selenium Excel Data File 5

Example

package org.example;

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.WebElement;
import org.openqa.selenium.chrome.ChromeDriver;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.concurrent.TimeUnit;

public class ExcelReadWrite {
   public static  void main(String args[]) throws IOException {

      // identify location of .xlsx file
      File f = new File("./Resources/DetailsStudent.xlsx");
      FileInputStream i = new FileInputStream(f);

      // instance of XSSFWorkbook
      XSSFWorkbook w = new XSSFWorkbook(i);

      // create sheet in XSSFWorkbook with name Details1
      XSSFSheet s = w .getSheet("Details1");

      // handle total rows in XSSFSheet
      int r = s.getLastRowNum() - s.getFirstRowNum();

      // Initiate the Webdriver
      WebDriver driver = new ChromeDriver();

      // adding implicit wait of 15 secs
      driver.manage().timeouts().implicitlyWait(30, TimeUnit.SECONDS);

      // Opening the webpage where we will identify elements
      driver.get("https://www.tutorialspoint.com/selenium/practice/register.php#");

      //Identify elements for registration
      WebElement fname = driver.findElement(By.xpath("//*[@id='firstname']"));
      WebElement lname = driver.findElement(By.xpath("//*[@id='lastname']"));
      WebElement uname = driver.findElement(By.xpath("//*[@id='username']"));
      WebElement pass = driver.findElement(By.xpath("//*[@id='password']"));
      WebElement btn = driver.findElement(By.xpath("//*[@id='signupForm']/div[5]/input"));

      // loop through rows, read and enter values in form
      for(int j = 1; j <= r; j++) {
         fname.sendKeys(s.getRow(j).getCell(0).getStringCellValue());
         lname.sendKeys(s.getRow(j).getCell(1).getStringCellValue());
         uname.sendKeys(s.getRow(j).getCell(2).getStringCellValue());
         pass.sendKeys(s.getRow(j).getCell(3).getStringCellValue());

         // submit registration form
         btn.click();

         // verify form submitted
         WebElement fname1 = driver.findElement(By.xpath("//*[@id='firstname']"));
         String value = fname1.getAttribute("value");

         // create cell at Column 4 to write values in excel
         XSSFCell c = s.getRow(j).createCell(4);

         // write results in excel
         if (value.isEmpty()) {
            c.setCellValue("Test Case: PASS");
         } else {
            c.setCellValue("Test Case: FAIL");
         }

         // complete writing value in excel
         FileOutputStream o = new FileOutputStream("./Resources/DetailsStudent.xlsx");
         w.write(o);
      }

      // closing workbook object
      w.close();

      // Quitting browser
      driver.quit();
   }
}

Dependencies added to pom.xml.

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
   xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 
   http://maven.apache.org/xsd/maven-4.0.0.xsd">

   <modelVersion>4.0.0</modelVersion>
   <groupId>org.example</groupId>
   <artifactId>SeleniumJava</artifactId>
   <version>1.0-SNAPSHOT</version>

   <properties>
      <maven.compiler.source>16</maven.compiler.source>
      <maven.compiler.target>16</maven.compiler.target>
      <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
   </properties>

   <!-- https://mvnrepository.com/artifact/org.seleniumhq.selenium/selenium-java -->
   <dependencies>
      <dependency>
         <groupId>org.seleniumhq.selenium</groupId>
         <artifactId>selenium-java</artifactId>
         <version>4.11.0</version>
      </dependency>

      <!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
      <dependency>
         <groupId>org.apache.poi</groupId>
         <artifactId>poi</artifactId>
         <version>5.2.5</version>
      </dependency>

      <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
      <dependency>
         <groupId>org.apache.poi</groupId>
         <artifactId>poi-ooxml</artifactId>
         <version>5.2.5</version>
      </dependency>
   </dependencies>
</project>

Output

Process finished with exit code 0

In the above example, we had read the whole excel file and write the value Test Case: Pass in the cell at the fifth Column.

Finally, the message Process finished with exit code 0 was received, signifying successful execution of the code.

Selenium Excel Data File 6

As seen in the image above, Test Case: Pass was written into DetailsStudent.xlsx excel file in the Column 5 post the test run with respect to the registration data available in the same excel.

Conclusion

This concludes our comprehensive take on the tutorial on Selenium Webdriver Excel Data File. Weve started with describing what is Apache POI, how to install Apache POI, and walked through examples of how to read and write values in excel taking help of Apache POI along with Selenium Webdriver. This equips you with in-depth knowledge of the Excel Data File in Selenium Webdriver. It is wise to keep practicing what youve learned and exploring others relevant to Selenium to deepen your understanding and expand your horizons.

Print Page
Previous
Next
Advertisements