Reading data from xlsx files for Selenium Webdriver projects

Reading data from xlsx files for Selenium Webdriver projects

Hi, all welcome to another of my lockdown blog series posts.  In my previous blog post, I have discussed how we can read data from JSON and XML files. In this blog post, I will discuss how we can read data from an xlsx file. The scenario we will cover is to read the URL and the Name parameter from the xlsx file and navigate to the URL and pass the value of Name as the search parameter.

For working with spreadsheets we need to add a set of Apache POI dependencies. For trying out this scenario please add the below dependencies to your pom.xml along with the selenium related dependencies

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.2</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.2</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml-schemas</artifactId>
    <version>4.1.2</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-scratchpad</artifactId>
    <version>4.1.2</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>ooxml-schemas</artifactId>
    <version>1.4</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-excelant</artifactId>
    <version>4.1.2</version>
</dependency>

Next, our objective is to read the column values from the excel file and extract the details. So first create an excel file with the below contents. As we can see below the Cell A2 has the URL and the Cell B2 has the Name values which we want to read from the file.







Next, let's look at the code that we have to write for reading these values from the xlsx file. For this web driver code, we have a setup method that reads the excel file a test method that does the google search and the teardown method that will quit the web driver instance.

package readExcel;
import java.io.FileInputStream; 

import java.io.IOException; 

import java.io.File; 

import org.apache.poi.ss.usermodel.Sheet;

import org.apache.poi.ss.usermodel.Workbook; 

import org.apache.poi.xssf.usermodel.XSSFWorkbook; 

import org.openqa.selenium.*; 

import org.openqa.selenium.support.ui.ExpectedConditions; 

import org.openqa.selenium.firefox.FirefoxDriver; 

import org.openqa.selenium.support.ui.WebDriverWait; 

import org.testng.annotations.AfterTest; 

import org.testng.annotations.BeforeTest; 

import org.testng.annotations.Test; 

 public class ReadExcel {

    WebDriver driver; 

    WebDriverWait wait;

    WebElement searchButton; 

    String URL, name; 

     @BeforeTest 

     public void setup() throws IOException {


            System.setProperty("webdriver.firefox.driver", "training/training/geckodriver"); 

            driver = new FirefoxDriver();
            String filePath = "/training/training"; 

            String fileName = "student.xlsx"; 

            String sheetName = "Student";

            File file = new File(filePath + "//" + fileName);

            FileInputStream inputStream = new FileInputStream(file);
            Workbook studentInfo = null;
            //Find the file extension by splitting file name in substring  and getting only extension name
            String fileExtensionName = fileName.substring(fileName.indexOf("."));
            //Check condition if the file is xlsx file
            if (fileExtensionName.equals(".xlsx")) {

                //If it is xlsx file then create object of XSSFWorkbook class
                studentInfo = new XSSFWorkbook(inputStream);
            }

            //Read sheet inside the workbook by its name
            Sheet xlsSheet = studentInfo.getSheet(sheetName);

             URL=xlsSheet.getRow(1).getCell(0).getStringCellValue();

             name=xlsSheet.getRow(1).getCell(1).getStringCellValue();
        }

        @Test 

        public void testSearch () throws java.lang.IllegalMonitorStateException, NullPointerException
        {
            driver.get(URL); 

            driver.findElement(By.name("q")).sendKeys(name); 

            wait = new WebDriverWait(driver, 20); 

            searchButton = wait.until(ExpectedConditions.visibilityOfElementLocated(By.xpath("/html/body/div/div[3]/form/div[2]/div[1]/div[3]/center/input[1]"))); 

            searchButton.click();
        }

        @AfterTest 

        public void tearDown () {
            driver.quit();
        }


    }

When the above code is run first a web driver instance for firefox driver will be created, next 

the xlsx file of the given path will be read and the cell vaues from cells 0 and 1 will be extracted

and assigned to the variablesd URL and name.

The selenium code will then navigate to the url based on the extrated URL value and pass the name

value as the parameter to search the contents.