How to fetch URL from excel to @BeforeTest for different Env using DataDriven Testing with TestNG

Question

In this scenario I just wanted to iterate all the Environment one by one using dataprovider or any other way that could be possible. Please see my attached excel file

Code for one Environment: LTI given below. Note: Same Code is repeated for all other Environment by just changing class names.

public class LTI_ENV {

    public WebDriver driver;
    private String sTestCaseName;
    private int iTestCaseRow;

    @BeforeTest
    public void beforeMethod() throws Exception {

        DOMConfigurator.configure("log4j.xml");
        // Getting the Test Case name, as it will going to use in so many places
        // The main use is to get the TestCase row from the Test Data Excel sheet
        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

        //Here I want to parameterize URL for each test ,is there any method that I can do ???
        //My application have 3 module i.e for LTI –Env
        //1)Account Management (need to check pass?fail)
        //2)Prep and rating (need to check pass?fail)
        //3)Risk Eval (need to check pass?fail)
        sTestCaseName = Utils.getTestCaseName(this.toString());
        Log.startTestCase(sTestCaseName);
        ExcelUtils.setExcelFile(Constant.Path_TestData + Constant.File_TestData,"Sheet1");
        iTestCaseRow = ExcelUtils.getRowContains(sTestCaseName,Constant.Col_TestCaseName);
        driver = Utils.OpenBrowser(iTestCaseRow);
        new BaseClass(driver);  
    }

    @Test(priority=1)
    public void AccountManagement() throws Exception {  
        try {
            AccountCreation_Action.Execute(iTestCaseRow);
            AM_SubmissionLOB_Action.Execute(iTestCaseRow);
            AM_Classification_Action.Execute(iTestCaseRow);     
            //Verification_Action1.Execute();
            if (BaseClass.bResult==true) {
                ExcelUtils.setCellData("Pass", iTestCaseRow, Constant.Col_ResultStatus);
            } else {
                throw new Exception("Test Case Failed because of Verification");
            }
        } catch (Exception e) {
            ExcelUtils.setCellData("Fail", iTestCaseRow, Constant.Col_ResultStatus);
            Utils.takeScreenshot(driver, sTestCaseName);
            // This will print the error log message
            Log.error(e.getMessage());
            // Again throwing the exception to fail the test completely in the TestNG results
            throw (e);
        }   
    }

    @Test(priority=2,dependsOnMethods="AccountManagement")
    public void PrepandRating() throws Exception {

        try {
            GenInfopage_Action.Execute(iTestCaseRow);
            LocationSchedulePage_Actions.Execute(iTestCaseRow);
            PolicyCoveragePage_Action.Execute(iTestCaseRow);
            LocationInfo_Page_Action.Execute(iTestCaseRow);
            LocationSchedulePage_Actions.Execute(iTestCaseRow);
            AutoVehicleCoveragePage_Action.Execute(iTestCaseRow);
            AutoPolicyCoveragePage_Action.Execute(iTestCaseRow);
            PremiumSummaryPage_Action.Execute(iTestCaseRow);
            //Verification_Action2.Execute();

            if (BaseClass.bResult==true) {
                ExcelUtils.setCellData("Pass", iTestCaseRow+2, Constant.Col_ResultStatus);
            } else {
                throw new Exception("Test Case Failed because of Verification");
            }
        } catch (Exception e) {
            ExcelUtils[enter image description here][1].setCellData("Fail", iTestCaseRow+2, Constant.Col_ResultStatus);
            Utils.takeScreenshot(driver, sTestCaseName);
            Log.error(e.getMessage());
            throw (e);
        }
    }

    @Test(priority=3,dependsOnMethods="PrepandRating")
    public void RiskEval() throws Exception {
        //will update code for this module
    }

    // Its time to close the finish the test case       
    @AfterTest
    public void afterTest() {
        // Printing beautiful logs to end the test case
        Log.endTestCase(sTestCaseName);
        // Closing the  driver
        driver.quit(); //I need to close all the browser and then will launch again for other environment tests
    }
}

Challenges that I am facing are:

1. In the above code I am first searching test case name in the excel TestCaseName column using Utils.getTestCaseName(this.toString()); Hence here I am able to run only one Environment at a time.

2. I want to run test cases if my test run status is RUN/Otherwise Skipped

How do I write this scenario using testNG? As in the @BeforeTest I need to fetch URL for each test environment.

Please find below framework that I have implemented: http://toolsqa.wpengine.com/selenium-webdriver/selenium-automation-hybrid-framework

Answer 1

package TestNG.DataDriven;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.testng.Assert;
import org.testng.TestNG;
import org.testng.annotations.DataProvider;
import org.testng.annotations.Test;

public class XMLRun {

    public static XSSFWorkbook book;
    public static XSSFSheet sheet1;
    public static int LastRow=1;

    @Test(dataProvider="XMLFeed")
    public static void XMLRead(String Status,String XMLPath) throws Exception{

        if (Status.equalsIgnoreCase("Run")){
            // Create object of TestNG Class
            TestNG runner=new TestNG(); 
            // Create a list of String 
            List<String> suitefiles=new ArrayList<String>();

            // Add xml file which you have to execute
            suitefiles.add(XMLPath); 
            // now set xml file for execution
            runner.setTestSuites(suitefiles); 
            // finally execute the runner using run method
            runner.run();
        } else {
            Assert.fail("Skipped Test");
            //sheet1.getRow(LastRow).createCell(4).setCellValue("Not Executed");
            //com.ReadExcel.WriteExcel.WriteExcelSheet(LastRow, "NotExecuted", 4);
            com.ReadExcel.Excelreadwrite.excelwrite("Not Executed", LastRow);
        }
        LastRow++;
    }

    @DataProvider(name="XMLFeed")
    public Object[][] getData()
    {
        Object[][] obj=new Object[5][];

        File file=new File("TestData.xlsx");

        try {
            InputStream is=new FileInputStream(file);

            book=new XSSFWorkbook(is);

            sheet1=book.getSheet("Sheet1");

            for (int i=0;i<sheet1.getLastRowNum()+1;i++)
            {
                obj[i]=new Object[2];
                obj[i][0]=sheet1.getRow(i+1).getCell(1).
                getStringCellValue();
                obj[i][1]=sheet1.getRow(i+1).getCell(8).
                getStringCellValue();
                /*String result=login(username,password);
                sheet1.getRow(i).createCell(2).setCellValue(result);*/
            }
            book.close();
            is.close();
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
        return obj;
    }
}

I used this way. It's working fine but I want to have all this should be present within one suite.. and all testNG report should be generated within one report.

Here are my testcases classes: