I Am trying to pass in parameters from excel using a data driven approach using TestNG. My code runs fine, however, when my test gets to the section were the parameters are to be passed in to the fields on my form, they aren't passed in. My code is below for opening excel reading etc. And my Test code. I don't have excel installed as I have been creating spreadsheets in google sheets and exporting to xlsx. I am wondering if my issue is that I don't have excel installed so the file cant be opened and read and then the parameters passed in?
package utility;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
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(String Path,String SheetName) throws Exception {
try {
FileInputStream ExcelFile = new FileInputStream(Path);
ExcelWBook = new XSSFWorkbook(ExcelFile);
ExcelWSheet = ExcelWBook.getSheet(SheetName);
} catch (Exception e){
throw (e);
}
}
public static Object[][] getTableArray(String FilePath, String SheetName, int iTestCaseRow) throws Exception
{
String[][] tabArray = null;
try{
FileInputStream ExcelFile = new FileInputStream(FilePath);
ExcelWBook = new XSSFWorkbook(ExcelFile);
ExcelWSheet = ExcelWBook.getSheet(SheetName);
int startCol = 1;
int ci=0,cj=0;
int totalRows = 1; //Number of total rows in Data Sheet
int totalCols = 3; //Number of columns in Data Sheet - Columns start from 0
tabArray=new String[totalRows][totalCols];
for (int j=startCol;j<=totalCols;j++, cj++)
{
tabArray[ci][cj]=getCellData(iTestCaseRow,j);
System.out.println(tabArray[ci][cj]);
}
}
catch (FileNotFoundException e)
{
System.out.println("Could not read the Excel sheet");
e.printStackTrace();
}
catch (IOException e)
{
System.out.println("Could not read the Excel sheet");
e.printStackTrace();
}
return(tabArray);
}
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"";
}
}
public static String getTestCaseName(String sTestCase)throws Exception{
String value = sTestCase;
try{
int posi = value.indexOf("@");
value = value.substring(0, posi);
posi = value.lastIndexOf(".");
value = value.substring(posi + 1);
return value;
}catch (Exception e){
throw (e);
}
}
public static int getRowContains(String sTestCaseName, int colNum) throws Exception{
int i;
try {
int rowCount = ExcelUtils.getRowUsed();
for ( i=0 ; i<rowCount; i++){
if (ExcelUtils.getCellData(i,colNum).equalsIgnoreCase(sTestCaseName)){
break;
}
}
return i;
}catch (Exception e){
throw(e);
}
}
public static int getRowUsed() throws Exception {
try{
int RowCount = ExcelWSheet.getLastRowNum();
return RowCount;
}catch (Exception e){
System.out.println(e.getMessage());
throw (e);
}
}
}
Test Code is Below:
package utility;
import org.testng.annotations.Test;
import org.testng.annotations.BeforeMethod;
import java.util.concurrent.TimeUnit;
import org.apache.log4j.LogManager;
import org.apache.log4j.Logger;
import org.apache.log4j.xml.DOMConfigurator;
import org.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.WebElement;
import org.openqa.selenium.firefox.FirefoxDriver;
import org.testng.Assert;
import org.testng.Reporter;
import org.testng.annotations.AfterMethod;
import org.testng.annotations.DataProvider;
public class DataProviderWithExcel_001 {
private String sTestCaseName;
private int iTestCaseRow;
WebDriver driver;
private static Logger logger1 = LogManager.getLogger("Logger1");
@BeforeMethod
public void beforeMethod() {
DOMConfigurator.configure("log4j.xml");
driver = new FirefoxDriver();
logger1.info("New Instance of firefox created");
//Add wait to allow web elements to load
driver.manage().timeouts().implicitlyWait(15, TimeUnit.SECONDS);
logger1.info("Timeout Applied for 10 seconds to Allow Load of Elements");
//Launch FireFoxDriver - Open WebPage
driver.get("http://localhost/2010A15/");
logger1.info("Website Launched");
Reporter.log("Website Lauched Successfully | "); //Main Event Logger/Report
}
@Test(dataProvider = "Authentication")
public void f(String sUsername, String sPassword, String sMemorableWord) {
//Find Login Element
driver.findElement(By.id("WEB_LoginButton")).click();
logger1.info("Login Button Clicked");
//Find User name Element
driver.findElement(By.id("dijit_form_ValidationTextBox_1")).sendKeys(sUsername);
logger1.info("Username Entered");
//Find Password Element
driver.findElement(By.id("dijit_form_ValidationTextBox_2")).sendKeys(sPassword);
logger1.info("Password Entered");
//Find Memorable word Element
driver.findElement(By.id("dijit_form_ValidationTextBox_3")).sendKeys(sMemorableWord);
logger1.info("MemorableWord Entered");
Reporter.log("All Login Details Entered | "); //Main Event
WebElement login = driver.findElement(By.id("dijit_form_Button_1_label"));
//If statement - will check if element is Displayed before clicking on login button.
if(login.isDisplayed()){
login.click();
//Main Event is logged If Passed
Reporter.log("Login Form Submitted | ");
logger1.info("Submit Button Clicked");
}else{
Reporter.log("Login Failed | ");
Assert.fail("Login Failed - Check Data | ");
//Main Event Log Fail
}
WebElement logout = driver.findElement(By.id("dijit_form_Button_0_label"));
if(logout.isDisplayed()){
logout.click();
Reporter.log("Logout Successful | ");
}else {
Reporter.log("Logout Failed");
Assert.fail("Logout Failed - Check Data | ");
}
logger1.info("Logout Successful");
}
@AfterMethod
public void afterMethod() {
driver.close();
}
@DataProvider
public Object[][] Authentication() throws Exception {
// Setting up the Test Data Excel file
ExcelUtils.setExcelFile("C://Selenium-java-maven//workSpace//Practice-test//src//testData//TestData.xlsx","Sheet1");
sTestCaseName = this.toString();
// From above method we get long test case name including package and class name etc.
// The below method will refine your test case name, exactly the name use have used
sTestCaseName = ExcelUtils.getTestCaseName(this.toString());
// Fetching the Test Case row number from the Test Data Sheet
// Getting the Test Case name to get the TestCase row from the Test Data Excel sheet
iTestCaseRow = ExcelUtils.getRowContains(sTestCaseName,0);
Object[][] testObjArray = ExcelUtils.getTableArray("C://Selenium-java-maven//workSpace//Practice-test//src//testData//TestData.xlsx","Sheet1",iTestCaseRow);
return (testObjArray);
}
}
