PAGE 22 : SAVE TEST RESULT IN EXCEL FILE USING SELENIUM WEBDRIVER

Save Test Result in Excel File using Selenium-Webdriver

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

Create a testdata.xls file in the D drive with following details:

package apachedatadriven;

import java.io.*;

import java.util.concurrent.TimeUnit;

import org.apache.poi.hssf.usermodel.*;

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

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) throws InterruptedException { 

        WebDriver driver = new FirefoxDriver();

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

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

       

        WebElement username = driver.findElement(By.name("Email"));

        

        WebElement password=driver.findElement(By.name("Passwd"));

 try {

    

  FileInputStream file = new FileInputStream(new File("D:\\testdata.xls")); 

  HSSFWorkbook workbook = new HSSFWorkbook(file);

  HSSFSheet sheet = workbook.getSheetAt(0);

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

        Cell resultcell1=sheet.getRow(2).getCell(3);

        String usernamevalue = sheet.getRow(1).getCell(2).getStringCellValue();

        String passwordvalue=sheet.getRow(2).getCell(2).getStringCellValue();

        username.sendKeys(usernamevalue);

        password.sendKeys(passwordvalue);

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

        String usernametext=  username.getAttribute("value");

        String passwordtext= password.getAttribute("value");

        if(usernametext.equals(usernamevalue)){

                System.out.println("set is successful.");

                resultCell.setCellValue("PASS");

        } else {

                System.out.println("set is not successful.");

                resultCell.setCellValue("FAIL");

        }

        if(passwordtext.equals(passwordvalue)){

            System.out.println("set is successful.");

            resultcell1.setCellValue("PASS");

    } else {

            System.out.println("set is not successful.");

            resultcell1.setCellValue("FAIL");

    }

  

  file.close();

  FileOutputStream outFile =new FileOutputStream(new File("D:\\testdata-result.xls"));

  workbook.write(outFile);

  outFile.close();

 } catch (FileNotFoundException fnfe) {

  fnfe.printStackTrace();

 } catch (IOException e) {

  e.printStackTrace();

 }

 driver.findElement(By.name("signIn")).click();

 }

}

Open your test-result.xsl.its look like below image

Detailed explanation for the above program is as follows:

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

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

Following code is the required packages for Apache POI library.

6 7	import org.apache.poi.hssf.usermodel.*; import org.apache.poi.ss.usermodel.Cell;

Following code is the required packages for Selenium.

8 9 10 11

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.

17	WebDriver driver = new FirefoxDriver();

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

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

Following code is to maximize the Firefox Driver instance.

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

Following code is to store WebElement into a variable.

23	WebElement username = driver.findElement(By.name("Email")); WebElement password= driver.findElement(By.name("Passwd"));

Following code is to locate the path of excel file.

FileInputStream file =  new FileInputStream(new File("D:\\testdata.xls"));

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

HSSFWorkbook workbook = new HSSFWorkbook(file);

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

HSSFSheet sheet = workbook.getSheetAt(0);

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

String usernamevalue= sheet.getRow(1).getCell(2).getStringCellValue();

Following code is to type the keyword into search textbox.

username.sendKeys(keyword);

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 usernametext=  searchbox.getAttribute('value');

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

if(usernametext.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.

file.close();

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

FileOutputStream outFile =new FileOutputStream(new File("D:\\testdata-result.xls"));

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();