How to read data from 2 sheets of same excel using same DataProvider in same class in Selenium WebDriver

Question

I tried searching for relevant information for this question but wasn't able to find. Here is the question: There are 2 sheets in same excel. I want to print the data from both sheets using TestNG DataProvider only once in the same class.

Sheet1:

UserName Password

administrator1 password1

administrator2 password2

Sheet2:

FirstName LastName

abc def

pqr xyz

String path=<filePath>;
String sheetName;
@Test (dataProvider="loginInfo")
public void loginVerification(String username, String password) throws InterruptedException
{
    sheetName="Sheet1";
    System.out.println("Username:"+username+" "+"Password:"+password);
    System.out.println("************");
}

@Test (dataProvider="loginInfo")
public void userNames(String firstName, String lastName)
{
    sheetName="sheet2";
    System.out.println("FirstName:"+firstName+" LastName:"+lastName);
    System.out.println("*******");
}


@DataProvider(name="loginInfo")
public Object[][] userFormData() throws BiffException, IOException
{
    Object[][] data = testData(path, sheetName);
    return data;
}

public Object[][] testData(String path, String sheetName) throws BiffException, IOException
{
    <code to read excel data>
        }
    }
    return inputData;
}

When I run the code, it just skips execution with Null Pointer exception. If I declare sheetName at the beginning as I did for filePath, only data from 1st sheet is picked. Any help on how to proceed here is appreciated.!

have you tried to debug until the NullPointerException ocours? — metar, Jul 19 '17 at 14:03

Madis Kangro · Answer 1 · 2017-07-19T14:52:07.337

The issue is that dataProvider gets the data before running the test, but you set the sheetname inside the test. So the sheetname is in that case null. This is why you get a nullpointer. The sheetname would get the value after running the test.

So if you want to use the same dataprovider no matter what. Then I would add the sheet switch to after the first test.

So currently the following code will work like this.

Dataprovider gets initated with the sheetName = "Sheet1"
loginVerification test is ran and after the test it changes the sheetName value to "Sheet2".
Dataprovider gets initated again for the userNames test, but this time the sheetname is Sheet2.

userNames test is ran with the data gotten from Sheet2.

String path=<filePath>;
String sheetName = "SheetName1";
            @Test (dataProvider="loginInfo")
            public void loginVerification(String username, String password) throws InterruptedException
{

    System.out.println("Username:"+username+" "+"Password:"+password);
    System.out.println("************");
    sheetName="Sheet2";
}

@Test (dataProvider="loginInfo")
public void userNames(String firstName, String lastName)
{

    System.out.println("FirstName:"+firstName+" LastName:"+lastName);
    System.out.println("*******");
}


@DataProvider(name="loginInfo")
public Object[][] userFormData() throws BiffException, IOException
{
    Object[][] data = testData(path, sheetName);
    return data;
}

public Object[][] testData(String path, String sheetName) throws BiffException, IOException
{
    <code to read excel data>
        }
    }
    return inputData;
}

With this solution the tests are error prone. If loginVerification test fails then the sheetname doesn't get updated and it will use Sheetname1 still.

I prefer to create 2 different dataproviders if they take data from different sheets.

How to read data from 2 sheets of same excel using same DataProvider in same class in Selenium WebDriver

1 Answers1