Data-Driven testing is a software-testing methodology, which is an iterative process to assert the actual value with the expected value that fetches test input data from an external or internal data source. Data-Driven tests are generally carried out with bulk input data.
Java Excel API is termed the JXL API. It is the most widely used API for executing Selenium Data-Driven tests that allows the user to read, write, create, and modify sheets in an Excel Binary (.xls
) workbook at runtime. JXL API has no support for SpreadsheetML (.xlsx
) workbooks.
The API reads data from Excel Binary workbooks with versions Excel 95, 97, 2000, XP, and 2003. The following snippet tells you how to read an Excel Binary workbook:
FileInputStream fi = new FileInputStream("C:\...inputdata.xls");
Workbook wb = Workbook.getWorkbook(fi);
Sheet ws = wb.getSheet(0);
String a[][] = new String[ws.getRows()][ws.getColumns()];
for (int rowCnt = 1; rowCnt < ws.getRows(); rowCnt++) {
driver.get("www.example.com");
driver.findElement(By.locatorType("path")).sendKeys(ws.getCell(0, rowCnt).getContents());
...
}
Here, the getWorkbook()
method fetches the workbook as a file and not as string; the getSheet()
method accesses the sheet of the workbook; and the getRows()
and getColumns()
methods store row and column counts using two-dimensional arrays.
The JXL library also allows the users to create an Excel workbook and write data into the workbook. The following snippet tells you how to write an Excel Binary workbook:
FileOutputStream fo = new FileOutputStream("C:\...outputdata.xls"); WritableWorkbook wb = Workbook.createWorkbook(fo); WritableSheet ws = wb.createSheet("Sheet1", 0); for (int rowCnt = 1; rowCnt < wrksheet.getRows(); rowCnt++) { driver.get("www.example.com"); driver.findElement(By.locatorType("path")).sendKeys(wrksheet.getCell(0, rowCnt).getContents()); driver.findElement(By.locatorType("path")).click(); boolean resultfound = isElementPresent(By.locatorType("path")); if (resultfound) { //Writes data into 3rd column Label l3 = new Label(2, rowCnt, "pass"); ws.addCell(l3); else { //Writes data into 3rd column Label l2 = new Label(2, rowCnt, "fail"); ws.addCell(l2); } } wb.write(); wb.close(); }
Here, the write()
method returns the values to be saved in the Excel workbook and the close()
method quits the current workbook session. The getContents()
method returns all the values from the cell. Here's the syntax for this method:
wrksheet.getCell(0, rowCnt).getContents()
Let's see an example test method to read and write data in an Excel Binary workbook:
@Test public void readandwrite() throws Exception { // Read data from excel sheet FileInputStream fi = new FileInputStream("C:\...inputdata.xls"); Workbook wrkbook = Workbook.getWorkbook(fi); Sheet wrksheet = wrkbook.getSheet(0); String a[][] = new String[wrksheet.getRows()][wrksheet.getColumns()]; // Write the input data into another excel file FileOutputStream fo = new FileOutputStream("C:\...outputdata.xls"); WritableWorkbook wwb = Workbook.createWorkbook(fo); WritableSheet ws = wwb.createSheet("customsheet", 0); System.out.println("Total Rows: " + wrksheet.getRows()); System.out.println("Total Columns: " + wrksheet.getColumns()); for (int i = 0; i < wrksheet.getRows(); i++) { for (int j = 0; j < wrksheet.getColumns(); j++) { a[i][j] = wrksheet.getCell(j, i).getContents(); Label l = new Label(j, i, a[i][j]); Label l1 = new Label(2, 0, "Result"); ws.addCell(l); ws.addCell(l1); } } for (int rowCnt = 1; rowCnt < wrksheet.getRows(); rowCnt++) { driver.get("www.example.com"); //Enter search keyword by reading data from Excel [Here it read from 1st column] driver.findElement(By.locatorType("path")).sendKeys(wrksheet.getCell(0, rowCnt).getContents()); driver.findElement(By.locatorType("path")).click(); Thread.sleep(5000); boolean resultfound = isElementPresent(By.locatorType("path")); if (resultfound) { //Writes data into 3rd column Label l3 = new Label(2, rowCnt, "pass"); ws.addCell(l3); else { //Writes data into 3rd column Label l2 = new Label(2, rowCnt, "fail"); ws.addCell(l2); } } wwb.write(); wwb.close(); }
The Data-Driven technique is a repetitive process of any step with multiple sets of data. The Selenium WebDriver API doesn't have any built-in support for Data-Driven tests. Regardless of that, the JExcel/JXL library is a third-party API for Selenium-based tests to perform Data-Driven tasks. In this approach, the script to activate the Data-Driven function is embedded in the test class itself instead of the reusable library being put to use. Let's see an example with a pass condition and a fail condition using a simple Data-Driven technique from JXL API. The following screenshot is an Excel data source of the given example:
Here, we search for a set of keywords on every test run and finally assert the title on the Google search results page. TestNG is the unit-testing framework used here to prioritize and execute tests. The following script is built based on the simple Data-Driven approach using JXL API:
public class SimpleDataDriven { WebDriver driver; Sheet s; @BeforeTest public void setUp() { driver = new ChromeDriver(); driver.get("https://www.google.com/"); } @Test(priority = 1) public void Googlepass() throws Exception { FileInputStream fi = new FileInputStream("/Users/.../data.xls"); Workbook w = Workbook.getWorkbook(fi); s = w.getSheet(0); for (int row = 1; row <= s.getRows() - 1; row++) { String input1 = s.getCell(0, row).getContents(); String output1 = s.getCell(1, row).getContents(); driver.findElement(By.name("q")).clear(); driver.findElement(By.name("q")).sendKeys(input1); driver.findElement(By.name("q")).sendKeys(Keys.ENTER); Thread.sleep(3000); try { Assert.assertEquals(output1, driver.getTitle()); } catch (Error e) { verificationErrors.append(e.toString()); } } } @Test(priority = 2) public void Googlefail() throws Exception { FileInputStream fi = new FileInputStream("/Users/.../data.xls"); Workbook w = Workbook.getWorkbook(fi); s = w.getSheet(0); for (int row = 1; row <= s.getRows() - 1; row++) { String input2 = s.getCell(2, row).getContents(); String output2 = s.getCell(3, row).getContents(); driver.findElement(By.name("q")).clear(); driver.findElement(By.name("q")).sendKeys(input2); driver.findElement(By.name("q")).sendKeys(Keys.ENTER); Thread.sleep(3000); try { Assert.assertEquals(output2, driver.getTitle()); } catch (Error e) { verificationErrors.append(e.toString()); } } } }
The JExcel library does way more than the simple Data-Driven methodologies explained in the above section. It lets you perform any Java-oriented Data-Driven tasks by creating a reusable library file. The reusable library is in the form of a class rather than a JAR file; it can be easily understood and customized based upon the given specifications:
Create a library file with the following guidelines:
The following is the reusable library class file (ExcelSheetDriver.java
) built using JXL API:
import jxl.Sheet; import jxl.Workbook; import jxl.read.biff.BiffException; import java.io.File; import java.io.IOException; import java.util.Hashtable; public class ExcelSheetDriver { // create sheet name static Sheet wrksheet; // create workbook name static Workbook wrkbook = null; static Hashtable dict = new Hashtable(); //Create a Constructor public ExcelSheetDriver(String ExcelSheetPath) throws BiffException, IOException { //Initialize the workbook wrkbook = Workbook.getWorkbook(new File(ExcelSheetPath)); //Here, the worksheet is pointed to Sheet1 wrksheet = wrkbook.getSheet("Sheet1"); } //Returns the Number of Rows public static int RowCount() { return wrksheet.getRows(); } //Returns the Cell value by taking row and Column values as argument public static String ReadCell(int column, int row) { return wrksheet.getCell(column, row).getContents(); } //Create Column Dictionary to hold all the Column Names public static void ColumnDictionary() { //Iterate through all the columns in the Excel sheet and store the value in Hashtable for (int col = 0; col < wrksheet.getColumns(); col++) { dict.put(ReadCell(col, 0), col); } } //Read Column Names public static int GetCell(String colName) { try { int value; value = ((Integer) dict.get(colName)).intValue(); return value; } catch (NullPointerException e) { return (0); } } }
Now, let's create a test class to perform a simple Google search by reading values from the Excel sheet. To do so, first create a constructor to initialize the Excel data source and then create a loop to iterate through the Excel sheet cell values. The following screenshot is an Excel data source of the preceding example:
Here, we do a simple Google search for a set of keywords from the Excel file. TestNG is the unit-testing framework used here for executing tests. The following script (Googlesearch.java
) is built based on the Data-Driven reusable library using JXL API.
public class GoogleSearch { //Global initialization of Variables static ExcelSheetDriver xlsUtil; //Constructor to initialze Excel for Data source public GoogleSearch() throws BiffException, IOException { xlsUtil = new ExcelSheetDriver("/Users/.../data.xls"); //Load the Excel Sheet Col in to Dictionary xlsUtil.ColumnDictionary(); } private WebDriver driver; @BeforeTest public void setUp() throws Exception { driver = new ChromeDriver(); driver.get("https://www.google.com/"); } @Test public void Test01() throws Exception { //Create a for loop to iterate through the Excel sheet for (int rowCnt = 1; rowCnt < xlsUtil.RowCount(); rowCnt++) { driver.findElement(By.name("q")).clear(); //Enter search keyword by reading data from Excel driver.findElement(By.name("q")).sendKeys(xlsUtil.ReadCell(xlsUtil.GetCell("keyword1"), rowCnt)); driver.findElement(By.name("q")).sendKeys(Keys.ENTER); Thread.sleep(2000); driver.findElement(By.name("q")).clear(); //Enter search keyword by reading data from Exceldriver.findElement(By.name("q")).sendKeys(xlsUtil.ReadCell(xlsUtil.GetCell("keyword2"), rowCnt)); driver.findElement(By.name("q")).sendKeys(Keys.ENTER); Thread.sleep(2000); } } }
TestNG is a Java-based advanced unit-testing framework similar to JUnit 4, as mentioned in our previous chapters. In this method, we use the TestNG @dataProvider
annotation to fetch keywords from an Excel sheet and pass arguments into the test method. This framework using DataProvider
is highly recommended for use and an advised Data-Driven framework for implementing Java-based Selenium tests. Let's discuss this with a couple of methods in this section.
In the first method (with Excel), the Excel Binary workbook is used as a data source. This method is usually applied on projects with high data volumes. Here, the TestNG annotation, @dataProvider
, allows parameters to pass through the whole iteration process on the Test
method. The following screenshot is an Excel data source of the given example:
Create a test class by getting the array of tables in a two-dimensional array as shown in the following code:
public class DataDrivenWithExcel { WebDriver driver; private String baseUrl; @DataProvider(name = "Test") public Object[][] createPayId() throws Exception { Object[][] retObjArr = getTableArray("C:\...\data.xls", "Sheet1", "Test01"); return (retObjArr); } @BeforeClass public void BeforeClass() { driver = new FirefoxDriver(); driver.manage().window().maximize(); baseUrl = "http://www.google.co.in"; } @Test(dataProvider = "Test", description = "Testing ") public void Test01(String column1, String column2, String column3, String column4) throws Exception { driver.get(baseUrl + "/"); driver.findElement(By.name("q")).sendKeys(column1); driver.findElement(By.name("q")).sendKeys(Keys.RETURN); Thread.sleep(2000); driver.findElement(By.name("q")).clear(); } public String[][] getTableArray(String xlFilePath, String sheetName, String tableName) throws Exception { String[][] tabArray = null; Workbook workbook = Workbook.getWorkbook(new File(xlFilePath)); Sheet sheet = workbook.getSheet(sheetName); int startRow, startCol, endRow, endCol, ci, cj; Cell tableStart = sheet.findCell(tableName); startRow = tableStart.getRow(); startCol = tableStart.getColumn(); Cell tableEnd = sheet.findCell(tableName, startCol + 1, startRow + 1, 100, 64000, false); endRow = tableEnd.getRow(); endCol = tableEnd.getColumn(); System.out.println("startRow=" + startRow + ", endRow=" + endRow + ", " + "startCol=" + startCol + ", endCol=" + endCol); tabArray = new String[endRow - startRow - 1][endCol - startCol - 1]; ci = 0; for (int i = startRow + 1; i < endRow; i++, ci++) { cj = 0; for (int j = startCol + 1; j < endCol; j++, cj++) { tabArray[ci][cj] = sheet.getCell(j, i).getContents(); } } return (tabArray); } }
In the second method (without Excel), the Test
class makes use of a built-in data source within a class. Here, the TestNG annotation @dataprovider
plays a major role by maintaining effective Data-Driven tests as said in the preceding section. This method is applicable only to projects with lightweight data. Create a Test
class by storing data in memory with a two-dimensional array, as shown in the following code:
public class DataDrivenWithoutExcel {
WebDriver driver;
private String baseUrl;
@DataProvider(name = "Test")
public String[][] y() {
return new String[][] {
{
"prashanth sams", "prashanth"
}, {
"selenium esentials", "selenium"
}, {
"seleniumworks", "prashanth sams"
}
};
}
@BeforeClass
public void BeforeClass() {
driver = new FirefoxDriver();
driver.manage().window().maximize();
baseUrl = "http://www.google.co.in";
}
@Test(dataProvider = "Test")
public void Google(String actual, String expected) throws Exception
{
driver.get(baseUrl + "/");
driver.findElement(By.name("q")).sendKeys(actual);
driver.findElement(By.name("q")).sendKeys(Keys.RETURN);
Thread.sleep(2000);
boolean b = driver.getPageSource().contains(expected);
Assert.assertTrue(b);
}
@AfterClass
public void AfterClass() {
driver.quit();
}
}