Read Test data from Excel sheet and save test result into Excel

Create Three Classes given below, and run the ReadTestDataAndResultInExcel script using TestNG

Please add POI jar, TestNG and selenium jar accordingly

Create TestData.xlsx inside your project folder where src folder found, Create rows and columns given below screenshot

---

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelUtils {

private static XSSFSheet ExcelWSheet;
private static XSSFWorkbook ExcelWBook;
private static XSSFCell Cell;
private static XSSFRow Row;

public static void setExcelFile() throws Exception {
try {
// Open the Excel file
String path = System.getProperty("user.dir") + File.separator + "TestData.xlsx";
System.out.println("The patht :" + path);
FileInputStream ExcelFile = new FileInputStream(path);
// Access the required test data sheet
ExcelWBook = new XSSFWorkbook(ExcelFile);
ExcelWSheet = ExcelWBook.getSheet("sheet1");
} catch (Exception e){
throw (e);
}
}

//This method is to read the test data from the Excel cell, in this we are passing parameters as Row num and Col num
public static String getCellData(int RowNum, int ColNum) throws Exception{
try{
Cell = ExcelWSheet.getRow(RowNum).getCell(ColNum);
String CellData = Cell.getStringCellValue();
return CellData;
}catch (Exception e){
return"";
}
}

//This method is to write in the Excel cell, Row num and Col num are the parameters
public static void setCellData(String Result, int RowNum, int ColNum) throws Exception {
try{
Row = ExcelWSheet.getRow(RowNum);
Cell = Row.getCell(ColNum, org.apache.poi.ss.usermodel.Row.RETURN_BLANK_AS_NULL);
if (Cell == null) {
Cell = Row.createCell(ColNum);
Cell.setCellValue(Result);
} else {
Cell.setCellValue(Result);
}
// Constant variables Test Data path and Test Data file name
FileOutputStream fileOut = new FileOutputStream("TestData.xlsx");
ExcelWBook.write(fileOut);
fileOut.flush();
fileOut.close();
}catch(Exception e){
throw (e);
}
}

}

---

import org.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.testng.Assert;

public class LoginPage {

public static void signIn(WebDriver driver) throws Exception{
//This is to get the values from Excel sheet, passing parameters (Row num & Col num)to getCellData method
String userName = ExcelUtils.getCellData(1, 0);
String password = ExcelUtils.getCellData(1, 1);
System.out.println("The user name " + userName + "Password is " + password);
driver.findElement(By.id("username")).sendKeys(userName);
driver.findElement(By.id("password")).sendKeys(password);
driver.findElement(By.id("button")).submit();
Thread.sleep(5000);
Assert.assertTrue(driver.findElement(By.xpath("//a[contains(.,'Signout')]")).isDisplayed());
}
}

---

import java.util.concurrent.TimeUnit;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.firefox.FirefoxDriver;
import org.testng.annotations.Test;

public class ReadTestDataAndResultInExcel {

@Test(description ="Login")
public void loginTestCase() throws Exception {
WebDriver driver = new FirefoxDriver();
//This is to open the Excel file. Excel path, file name and the sheet name are parameters to this method
ExcelUtils.setExcelFile();
driver.manage().timeouts().implicitlyWait(20, TimeUnit.SECONDS);

driver.get("http://passframework.mypassbrains.com/");
LoginPage.signIn(driver);

System.out.println("Login Successfully");
driver.quit();
//This is to send the PASS value to the Excel sheet in the result column.
ExcelUtils.setCellData("Pass", 1, 2);
System.out.println("Done");
}

}


O/P Screenshot :