Q2

package solution;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.time.Duration;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.WebElement;
import org.openqa.selenium.firefox.FirefoxDriver;
import org.openqa.selenium.support.ui.Select;

import org.testng.Assert;
import org.testng.annotations.*;
public class SubmitTest {
WebDriver driver;

String path="/home/pg_user/Desktop/AdditionalSuppliedFiles/Data.xls";

@BeforeClass
public void beforeClass() {

    System.setProperty("webdriver.gecko.driver","/home/pg_user/Desktop/geckodriver");
    driver=new FirefoxDriver();
    driver.get("http://localhost:8080/application/index.html");
    driver.manage().window().maximize();
    driver.manage().timeouts().implicitlyWait(Duration.ofSeconds(8));
}

@Test(priority=1)
public void test1() throws Exception {

    driver.findElement(By.linkText("Add a Member")).click();

    FileInputStream fis=new FileInputStream(path);
    HSSFWorkbook wb=new HSSFWorkbook(fis);
    HSSFSheet sheet=wb.getSheetAt(0);

    HSSFRow row=sheet.getRow(1);

    driver.findElement(By.xpath("/html/body/div[1]/form/input[1]"))
            .sendKeys(row.getCell(0).getStringCellValue());

    driver.findElement(By.xpath("/html/body/div[1]/form/input[2]"))
            .sendKeys(row.getCell(1).getStringCellValue());

    driver.findElement(By.id("email_form"))
            .sendKeys(row.getCell(2).getStringCellValue());

    driver.findElement(By.id("password_form"))
            .sendKeys(String.valueOf((int)row.getCell(3).getNumericCellValue()));

    Select stream=new Select(driver.findElement(By.xpath("/html/body/div[1]/form/select")));

    for(WebElement option:stream.getOptions()) {
        if(option.getText().equals(row.getCell(4).getStringCellValue())) {
            option.click();
            break;
        }
    }

    driver.findElement(By.id("radio_button")).click();

    driver.findElement(By.xpath("/html/body/div[1]/form/button")).click();

    String msg=driver.findElement(By.id("message")).getText();

    HSSFCell cell=row.createCell(5);
    cell.setCellValue(msg);

    FileOutputStream fos=new FileOutputStream(path);
    wb.write(fos);

    wb.close();
    fis.close();
    fos.close();

    driver.findElement(By.xpath("/html/body/div[1]/nav/a")).click();
}

@Parameters({"type","pswd"})
@Test(priority=2)
public void test2(String type,String pswd) {

    driver.findElement(By.linkText("Already a Member?")).click();

    driver.findElement(By.id("inputName")).sendKeys(type);
    driver.findElement(By.id("inputPassword")).sendKeys(pswd);

    driver.findElement(By.xpath("/html/body/div/form/button")).click();

    Assert.assertTrue(driver.findElement(By.xpath("/html/body/nav/div/div[2]/ul/li/a")).isDisplayed());
}

@DataProvider(name="courseData")
public Object[][] data() {

    return new Object[][] {

        {"Selenium Using Python","Educator"},
        {"Agile Testing","Co-Educator"}

    };
}

@Test(priority=3,dataProvider="courseData")
public void test3(String course,String role) {

    Select c=new Select(driver.findElement(By.id("course")));
    c.selectByVisibleText(course);

    Select r=new Select(driver.findElement(By.id("role")));
    r.selectByVisibleText(role);

    driver.findElement(By.xpath("/html/body/div[3]/form/button[1]")).click();
}

@AfterClass
public void afterClass() {
     driver.quit();
     driver.close();

}

}