Selenium WebDriver Example : Data Drivern Testing. Demonstrate reading data from XL file

ReadDataFromXL.java

package com.pragmatic.selenium.examples;

import io.github.bonigarcia.wdm.WebDriverManager;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.chrome.ChromeDriver;
import org.testng.Assert;
import org.testng.annotations.*;

import java.io.FileInputStream;

/**
 * Created by hansi on 2018-12-28
 **/
public class ReadDataFromXL {

    private WebDriver driver;

    @BeforeClass
    public void beforeClass() {
        WebDriverManager.chromedriver().setup();
    }


    @BeforeMethod
    public void beforeMethod() {
        driver = new ChromeDriver();
        driver.get("http://hrm.pragmatictestlabs.com");
        driver.manage().window().maximize();
    }


    @AfterMethod
    public void afterMethod() {
        driver.close();
    }

    /**
     * Demonstrate use of test data from a XL file in a test method
     *
     * @param loginData
     */
    @Test(dataProvider = "login-data-set1")
    public void testLoginData(String[] loginData) {
        String username = loginData[0];
        String password = loginData[1];
        String expected = loginData[2];
        String msgError;

        driver.findElement(By.id("txtUsername")).sendKeys(username);
        driver.findElement(By.id("txtPassword")).sendKeys(password);
        driver.findElement(By.id("btnLogin")).click();
        msgError = driver.findElement(By.id("spanMessage")).getText();

        Assert.assertEquals(msgError, expected);

    }


    /**
     * Login test data
     *
     * @return
     * @throws Exception
     */
    @DataProvider(name = "login-data-set1")
    public Object[][] testReadingDataFromXL() throws Exception {
        String[][] loginData;


        //Get the workbook
        FileInputStream fileInputStream = new FileInputStream("testData/login-data.xlsx");
        XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream);

        //This should be added to work with the blank cell
        workbook.setMissingCellPolicy(Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);

        //Get the sheet
        XSSFSheet sheet = workbook.getSheetAt(0);

        int lastRow = sheet.getLastRowNum();
        //Get a row
        XSSFRow row = sheet.getRow(0);
        int columns = row.getLastCellNum();

        loginData = new String[lastRow][columns];


        //Reading data from the first row (i=1) to the last row
        for (int i = 1; i <= lastRow; i++) {

            XSSFRow currentRow = sheet.getRow(i);

            //Reading data from the cells in the current row
            for (int j = 0; j < currentRow.getLastCellNum(); j++) {
                loginData[i - 1][j] = currentRow.getCell(j).getStringCellValue();
            }
        }


        return loginData;
    }


}