空指针异常:尝试使用Selenium从Excel文件读取数据时

huangapple go评论74阅读模式
英文:

NullPointerException when trying to read data from Excel file using Selenium

问题

我正在使用Maven创建一个Selenium项目,其中我正在尝试从Excel文件(xlsx)中读取'username'和'password'。

以下是代码部分 -

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 {
		// ... 代码继续 ...
		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");
}
}

我得到了以下错误 -

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)

Excel文件的屏幕截图-
(图片链接已省略)

我遇到错误的代码行是:

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

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-
空指针异常:尝试使用Selenium从Excel文件读取数据时

The error I am getting is in this line:

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

答案1

得分: 2

问题 - 空指针异常

您正在尝试访问不可用的单元格

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

这是在尝试从当前单元格移动到下一个单元格,但该单元格不存在。

Excel读取器是如何工作的?

首先获取第一行,并逐个顺序解析所有单元格。如果没有可用的单元格,则会转到下一行。

如果您在同一个循环中尝试访问“两个单元格”(sheet.getRow(i).getCell(j+1)),只有在这些单元格中有数据时才能正常工作,如果没有数据,您将得到一个NP异常,因为什么都不存在。

这张图片可能会对您有所说明
空指针异常:尝试使用Selenium从Excel文件读取数据时

解决方案:

//行
for (int i = 0; i < sheet.getPhysicalNumberOfRows(); i++) {
        Row row = sheet.getRow(i);
        //单元格
        for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {
            // 获取行单元格
            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();
        }
    }

另外 - 使用getPhysicalNumberOfRows() - 它只返回带有数据的行/单元格。

后续更新以处理NP异常

您需要将webdriver添加为您的Excel方法的参数。Webdriver获得一个实例,但当readExcel方法使用时为null,因此您需要传递初始化的对象。

将方法更改为

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

并将此调用更改为

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

这样就能正常工作。

英文:

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
空指针异常:尝试使用Selenium从Excel文件读取数据时

Solution:

//rows
for (int i = 0; i &lt; sheet.getPhysicalNumberOfRows(); i++) {
        Row row = sheet.getRow(i);
        //cells
        for (int j = 0; j &lt; row.getPhysicalNumberOfCells(); j++) {
            // gettingrow cell
            Cell cell = row.getCell(j);
            driver.findElement(By.xpath(&quot;//input[@type=&#39;text&#39;]&quot;)).sendKeys(cell.getStringCellValue());
            driver.findElement(By.xpath(&quot;//input[@type=&#39;password&#39;]&quot;)).sendKeys(cell.getStringCellValue());
            driver.findElement(By.xpath(&quot;//input[@type=&#39;submit&#39;]&quot;)).click();
            Thread.sleep(5000);
            String actualTitle = driver.getTitle();
            String expectedTitle = &quot;Home Page&quot;;
            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, &quot;Book2.xlsx&quot;, &quot;Sheet1&quot;, driver);

and it will work.

答案2

得分: 1

由于您遇到了空指针异常(NullPointerException),您正在使用方法链,最好将其拆分成更小的部分并了解哪个对象实际上为null,以找到原因。在没有这些信息的情况下,论坛中的其他人可能无法提供帮助。

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

可以尝试类似这样的做法:

WebElement element = driver.findElement(By.xpath("//input[@type='text']"));
Row row = sheet.getRow(i);
Cell cell = row.getCell(j);
String value = cell.getStringCellValue();

在这里,element、row 或 cell 可能为空。

英文:

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(&quot;//input[@type=&#39;text&#39;]&quot;)).sendKeys(sheet.getRow(i).getCell(j).getStringCellValue());

Something like

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

Here element, row or cell might be null.

huangapple
  • 本文由 发表于 2020年9月20日 20:01:35
  • 转载请务必保留本文链接:https://go.coder-hub.com/63978762.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定