How to read values from multiple excel sheets usin

Question

I am automating a web application with Selenium/Java/TestNG. The application has data in the main page and is being read from excel sheet by @dataProvider. Based on some criteria in the main page,I want to get data from other sheets in the same excel file and pass it into corresponding @test in the same class. Tried many options but not able to find a proper solution.

Thanks much in advance, Resh

Answer 1

Here's one way in which this can be done.

You basically inject the "sheet" name of your excel spreadsheet into the current <test> tag's context viz., ITestContext as an attribute and then from within the @DataProvider annotated data provider, you basically read this attribute to decide which sheet has to be read.

The below sample demonstrates two @Test methods doing this, wherein the first @Test method injects this attribute as part of it doing a flow and the second @Test method (it would have to depend on the first one) which is now powered by a dynamic data provider just consumes the data.

import org.testng.ITestContext;
import org.testng.Reporter;
import org.testng.annotations.DataProvider;
import org.testng.annotations.Test;

public class SampleTestClass {
    /**
     * This method is simulating the master test method which would interact with
     * the web app and bring the application to a state wherein the next test method
     * should take over and interact.
     */
    @Test
    public void testLogin() {
        //Simulating toggling between multiple flows.
        int whichFlow = Integer.parseInt(System.getProperty("flow", "1"));
        Reporter.getCurrentTestResult().getTestContext().setAttribute("flow", whichFlow);
    }

    /**
     * This method is intentionally dependent on "testLogin" because its "testLogin" that will
     * first interact with the web application and bring the application to the place from where
     * further interaction is required, but which will vary based on some "x" criteria
     * The "x" criteria is now available as an attribute in the current <test> tag's
     * context.
     */
    @Test(dependsOnMethods = "testLogin", dataProvider = "getData")
    public void testSomethingElse(int a, String b) {
        //Real test method logic goes here.
        System.out.println(a + ":" + b);

    }

    @DataProvider
    public Object[][] getData(ITestContext context) {
        int whichFlow = Integer.parseInt(context.getAttribute("flow").toString());
        switch (whichFlow) {
            case 1:
                return new Object[][]{
                        {1, "Login"},
                        {2, "Signup"}
                };
            case 2:
                return new Object[][]{
                        {100, "Fees"},
                        {200, "Charges"}
                };

            case 3:
            default:
                return new Object[][]{
                        {900, "Logout"},
                        {1000, "Random"}
                };

        }
    }
}

Answer 2

Just an approach, you can try to copy or move particular sheet in to your excel sheet , more specifically in to your workbook.