CODEWITHSUNDEEP
javamavenselenium-webdriverapache-poi
SHACHINDRA TRIPATHI
SHACHINDRA TRIPATHI

Reputation: 77

NullPointerException when trying to read data from Excel file using Selenium

I am creating a Selenium project using Maven, wherein I am trying to read 'username' and 'password' from an Excel file (xlsx).

Here is the code -

public class NewTest {

WebDriver driver;


@BeforeTest
public void setup() {
    System.setProperty("webdriver.gecko.driver",Util.FIREFOX_PATH);
    driver = new FirefoxDriver();
    driver.navigate().to(Util.BASE_URL);
}
    

public void readExcel(String filePath, String fileName, String sheetName) throws IOException, InterruptedException {
    File file = new File(filePath+"\\"+fileName);
    FileInputStream fs = new FileInputStream(file);
    Workbook workBook = null;
    String fileExtensionName = fileName.substring(fileName.indexOf("."));
    if (fileExtensionName.equals(".xlsx")) {
        workBook = new XSSFWorkbook(fs); 
    }
    if (fileExtensionName.equals(".xls")) {
        workBook = new HSSFWorkbook(fs);
    }
    Sheet sheet = workBook.getSheet(sheetName);
    int rowCount = sheet.getLastRowNum();
    for (int i = 0; i <= rowCount; i++) {
        Row row = sheet.getRow(i);
        for (int j = 0; j < row.getLastCellNum(); j++) {
            
            driver.findElement(By.xpath("//input[@type='text']")).sendKeys(sheet.getRow(i).getCell(j).getStringCellValue());
            driver.findElement(By.xpath("//input[@type='password']")).sendKeys(sheet.getRow(i).getCell(j+1).getStringCellValue());
            driver.findElement(By.xpath("//input[@type='submit']")).click();
            Thread.sleep(5000);
            String actualTitle = driver.getTitle();
            String expectedTitle = "Home Page";
            SoftAssert sassert = new SoftAssert();
            sassert.assertEquals(actualTitle, expectedTitle);
            sassert.assertAll();
            break;
        }
    }
    
}

@Test
public static void callingFunction() throws IOException, InterruptedException {
    NewTest newTest = new NewTest();
    String filePath = "C:\\Users\\Sitesh\\Desktop";
    newTest.readExcel(filePath, "Book2.xlsx", "Sheet1");
}
}

I am getting the following error -

FAILED: callingFunction
java.lang.NullPointerException
at Guru99_Bank.Guru99_Bank.NewTest.readExcel(NewTest.java:59)
at Guru99_Bank.Guru99_Bank.NewTest.callingFunction(NewTest.java:78)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.testng.internal.MethodInvocationHelper.invokeMethod(MethodInvocationHelper.java:132)
at org.testng.internal.TestInvoker.invokeMethod(TestInvoker.java:599)
at org.testng.internal.TestInvoker.invokeTestMethod(TestInvoker.java:174)
at org.testng.internal.MethodRunner.runInSequence(MethodRunner.java:46)
at org.testng.internal.TestInvoker$MethodInvocationAgent.invoke(TestInvoker.java:822)
at org.testng.internal.TestInvoker.invokeTestMethods(TestInvoker.java:147)
at org.testng.internal.TestMethodWorker.invokeTestMethods(TestMethodWorker.java:146)
at org.testng.internal.TestMethodWorker.run(TestMethodWorker.java:128)
at java.util.ArrayList.forEach(Unknown Source)
at org.testng.TestRunner.privateRun(TestRunner.java:764)
at org.testng.TestRunner.run(TestRunner.java:585)
at org.testng.SuiteRunner.runTest(SuiteRunner.java:384)
at org.testng.SuiteRunner.runSequentially(SuiteRunner.java:378)
at org.testng.SuiteRunner.privateRun(SuiteRunner.java:337)
at org.testng.SuiteRunner.run(SuiteRunner.java:286)
at org.testng.SuiteRunnerWorker.runSuite(SuiteRunnerWorker.java:53)
at org.testng.SuiteRunnerWorker.run(SuiteRunnerWorker.java:96)
at org.testng.TestNG.runSuitesSequentially(TestNG.java:1218)
at org.testng.TestNG.runSuitesLocally(TestNG.java:1140)
at org.testng.TestNG.runSuites(TestNG.java:1069)
at org.testng.TestNG.run(TestNG.java:1037)
at org.testng.remote.AbstractRemoteTestNG.run(AbstractRemoteTestNG.java:115)
at org.testng.remote.RemoteTestNG.initAndRun(RemoteTestNG.java:251)
at org.testng.remote.RemoteTestNG.main(RemoteTestNG.java:77)

Screenshot of Excel file- enter image description here

The error I am getting is in this line:

driver.findElement(By.xpath("//input[@type='text']")).sendKeys(sheet.getRow(i).getCell(j).getStringCellValue());

Upvotes: 0

Views: 2048

Answers (2)

Alin Stelian
Alin Stelian

Reputation: 897

Issue - Null pointer exception

You are trying to access unavailable cell

sheet.getRow(i).getCell(j+1)

This is trying to move toward the next cell from the current one, but that cell doesn't exist

How does Excel reader work?

Firstly gets the first row and it parses all the cells, one by one sequentially. If no cell is available it goes to the next row.

If you try to access "two cells" in the same loop (sheet.getRow(i).getCell(j+1)) it will work only if you have data in those cells, if not you'll get an NP exception cause nothing is there.

This picture might clarify it to you enter image description here

Solution:

//rows
for (int i = 0; i < sheet.getPhysicalNumberOfRows(); i++) {
        Row row = sheet.getRow(i);
        //cells
        for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {
            // gettingrow cell
            Cell cell = row.getCell(j);
            driver.findElement(By.xpath("//input[@type='text']")).sendKeys(cell.getStringCellValue());
            driver.findElement(By.xpath("//input[@type='password']")).sendKeys(cell.getStringCellValue());
            driver.findElement(By.xpath("//input[@type='submit']")).click();
            Thread.sleep(5000);
            String actualTitle = driver.getTitle();
            String expectedTitle = "Home Page";
            SoftAssert sassert = new SoftAssert();
            sassert.assertEquals(actualTitle, expectedTitle);
            sassert.assertAll();
        }
    }

also - use getPhysicalNumberOfRows() - it return only the rows/cells with data.

Later update for NP exception

you need to add the webdriver as a parameter for your excel method. The webdriver gets an instance but when the readExcel method uses is null, so you need to pass the initialized object.

Change the method to

public void readExcel(String filePath, String fileName, String sheetName, WebDriver driver)

and this call to

newTest.readExcel(filePath, "Book2.xlsx", "Sheet1", driver);

and it will work.

Upvotes: 2

Senthil Kumar Vaithiyanathan
Senthil Kumar Vaithiyanathan

Reputation: 866

Since you are getting NullPointerException, you are using chaining of methods it is better split into smaller & understand which object is actually null to find reason. Without that information, people in the forum may not able to help out

driver.findElement(By.xpath("//input[@type='text']")).sendKeys(sheet.getRow(i).getCell(j).getStringCellValue());

Something like

Element e = driver.findElement(By.xpath("//input[@type='text']")); Row row = sheet.getRow(i); Cell c = row.getCell(j); String val = c.getStringCellValue();

Here element, row or cell might be null.

Upvotes: 1

Related Questions