In our previous post, you have seen how to use testNG dataProvider to run selenium tests multiple times, mostly we have hard coded the 2d array to have test data, but most people would like to read the test data from external file sources like excel, json or xml.
In this post, we will see how to read the excel sheet data into 2d array and use it with testNG dataProvider and run the tests.
Let’s first have an excel sheet with below data
For this we will be using the ExcelReader.java, and method getCellData(int rownum, int colnum) from this post to read the sheet cells [excluding the column header or 1st row.]
Add below content to the ExcelReader.java file, so we can have all excel related utility methods and can be used across framework.
//Excelreader.Java
public int getNoOfRows(){
return sh.getPhysicalNumberOfRows();
}
public int getNoOfColumns(){
return sh.getRow(0).getLastCellNum();
}
public Object[][] to2DArray() throws Exception {
int noOfRows = getNoOfRows()-1;
int noOfCells = getNoOfColumns();
Object obj[][] = new Object[noOfRows][noOfCells];
for(int i=0; i<noOfRows; i++){ //i = 0 1 2
//row = sh.getRow(i);
for(int j=0; j<noOfCells; j++){
obj[i][j] = getCellData(i+1,j); //i = 1 2 3
}
}
return obj;
}We have added these 3 methods to implement this –
getNoOfRows() getNoOfColumns() to2DArray()
in to2DArray(), we have 2 for loops, outer loop is for the rows, then inner loop is for column or cells of each row.
Now, let’s call the to2DArray() to assign the object array to the dataProvider
package testNgLearning.attributes.dataProv;
import excelUtil.ExcelReader;
import org.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.chrome.ChromeDriver;
import org.openqa.selenium.support.ui.ExpectedConditions;
import org.openqa.selenium.support.ui.WebDriverWait;
import org.testng.annotations.DataProvider;
import org.testng.annotations.Test;
import java.time.Duration;
public class DataProv_Excel {
WebDriver driver;
@DataProvider(name = "data-set")
public static Object[][] DataSet() throws Exception {
//read the jason or excel data
/*Object[][] obj = {
{"valid", "standard_user", "secret_sauce"},
{"invalid", "standard_user", "123"}
};*/
ExcelReader excel = new ExcelReader();
excel.setExcelFile("./testdata.xlsx", "Sheet2");
Object[][] obj = excel.to2DArray();
return obj;
}
@Test(dataProvider = "data-set")
public void DataProvSampleTest(String type, String username, String password) throws InterruptedException {
System.setProperty("webdriver.chrome.driver", "/Users/skpatro/sel/chromedriver");
System.out.println(type + " " + username + " " + password);
driver = new ChromeDriver();
WebDriverWait wait = new WebDriverWait(driver, Duration.ofSeconds(3));
driver.get("https://www.saucedemo.com/");
driver.findElement(By.id("user-name")).sendKeys(username);
driver.findElement(By.id("password")).sendKeys(password);
driver.findElement(By.id("login-button")).click();
if(type.equals("valid")){
wait.until(ExpectedConditions.presenceOfElementLocated(
By.cssSelector("[class='title']")));
}else
wait.until(ExpectedConditions.presenceOfElementLocated(
By.cssSelector("[data-test='error']")));
Thread.sleep(2000);
driver.quit();
}
}
DataSet() method –
First set the excel path and sheet name
then call the to2DArray() to fetch the excel data into 2DArray.
Object[][] obj = excel.to2DArray();
Now you can run the testng test DataProvSampleTest() to run the test multiple times with valid, invalid and locked user.
hope this helps!
1 Comment