SAVE TEST RESULTS IN EXCEL FILE (.XLSX) IN SELENIUM WEBDRIVER

We can perform data driven using excel file and save the test results into excel file with help of Apache POI library.

Here is the very basic program to perform data driven from excel file and save the test results into excel file:

Create a testdata.xlsx file in the C drive with following details:

#	Action	Input	Result
1	Type in search text box	hello selenium
2	Type in search text box	abhishek yadav qa

Use below code to perform data driven from excel file and save the test results into excel file:

Java Source code:

package com.helloselenium.selenium.test;

import java.io.*;
import java.util.concurrent.TimeUnit;

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

public class SaveTestResultToExcelFile{

 public static void main(String[] args) { 

        WebDriver driver = new FirefoxDriver();

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

        driver.manage().window().maximize();  
      
        WebElement searchbox = driver.findElement(By.name("q"));

 try {
   
  FileInputStream file = new FileInputStream(new File("C:\\testdata.xlsx")); 
  XSSFWorkbook workbook = new XSSFWorkbook(file);

  XSSFSheet sheet = workbook.getSheetAt(0);

for (int i=1; i <= sheet.getLastRowNum(); i++){

        Cell resultCell= sheet.getRow(i).getCell(3);

        String keyword = sheet.getRow(i).getCell(2).getStringCellValue();

        searchbox.clear();

        searchbox.sendKeys(keyword);

        searchbox.submit();       
 
        driver.manage().timeouts().implicitlyWait(10000, TimeUnit.MILLISECONDS);

        String searchText =  searchbox.getAttribute("value");

        if(searchText.equals(keyword)){
                System.out.println("Search is successful.");
                resultCell.setCellValue("PASS");
        } else {
                System.out.println("Search is not successful.");
                resultCell.setCellValue("FAIL");
        }

}

  workbook.close();
  file.close();

  FileOutputStream outFile =new FileOutputStream(new File("C:\\testdata-result.xlsx"));
  workbook.write(outFile);
  outFile.close();

 } catch (FileNotFoundException fnfe) {
  fnfe.printStackTrace();
 } catch (IOException ioe) {
  ioe.printStackTrace();
 }
 }
}

Detailed explanation for the above program is as follows:

Following code is the required packages for JAVA IO to make integration with excel file.

import java.io.*;
import java.util.concurrent.TimeUnit;

Following code is the required packages for Apache POI library.

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

Following code is the required packages for Selenium.

import org.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.WebElement;
import org.openqa.selenium.firefox.FirefoxDriver;

Following code is to initialize the Firefox driver.

WebDriver driver = new FirefoxDriver();

Following code is to open the hello selenium blog in browser.

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

You can also use the following code is to open the hello selenium blog in browser.

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

Following code is to maximize the Firefox Driver instance.

driver.manage().window().maximize();

Following code is to store WebElement into a variable.

WebElement searchbox = driver.findElement(By.name("q"));

Following code is to locate the path of excel file.

FileInputStream file = new FileInputStream(new File("C:\\testdata.xlsx"));

Following code is to initialize the excel file as a workbook.

XSSFWorkbook workbook = new XSSFWorkbook(file);

Following code is to initialize the excel sheet of the workbook. Here 0 (zero) refers to the first sheet of the workbook.

XSSFSheet sheet = workbook.getSheetAt(0);

Following code is to run the loop till it found cell value of last row.

for (int i=1; i <= sheet.getLastRowNum(); i++){

Following code is to get the result cell location from the worksheet.

Cell resultCell= sheet.getRow(i).getCell(3);

Following code is to get the keyword value from the worksheet.

String keyword = sheet.getRow(i).getCell(0).getStringCellValue();

Following code is to clear previous text from search textbox.

searchbox.clear();

Following code is to type the keyword into search textbox.

searchbox.sendKeys(keyword);

Following code is to press RETURN within textbox.

searchbox.submit();

Following code is wait for 10 seconds.

driver.manage().timeouts().implicitlyWait(10000, TimeUnit.MILLISECONDS)

Following code is to get the value of search text box.

String searchText =  searchbox.getAttribute('value');

Following code is to output the result to console and excel both.

if(searchText.equals(keyword){
                System.out.println("Search is successful.");
                resultCell.setCellValue("PASS");
        } else {
                System.out.println("Search is not successful.");
                resultCell.setCellValue("FAIL");
        }

You can also use the following code is to close the excel file.

workbook.close();
file.close();

Following code is to define the path of output excel file.

FileOutputStream outFile =new FileOutputStream(new File("C:\\testdata-result.xlsx"));

Following code is to update the output file on defined location.

workbook.write(outFile);

Use the following code is to close the output excel file.

outFile.close();