Selenium 简明教程

Selenium WebDriver - Excel Data File

Selenium Webdriver 可用于与 Excel 数据文件交互。在自动化测试中，经常有一些要求，即通过 Excel 文件向测试用例馈送大量数据。这样做是为了验证特定场景或创建 * data driven framework* 。

Selenium Webdriver can be used to interact with the excel data file. Often in an automation test, there are requirements which has large amount of data to be fed through an excel file for a test case. This is done to verify a specific scenario or to create a data driven framework.

Java 提供了大量的类和方法，使用 Apache POI 库对 Excel 文件执行读取和写入数据操作。Apache POI API 是免费的、开源的 * Java* 库组。

Java gives a large option of classes and methods to carry read and write data operations on an excel file using the Apache POI libraries. An Apache POI API is a group of free, and open source Java libraries.

What is Apache POI?

Apache POI* 用于处理 Microsoft 文件。它可以用来在不同格式的文件（包括 Excel）上执行读取、写入、更新和其他操作。Java 无法直接用来处理 Excel 文件，因此 Apache POI（一个第三方 API）被用来和 Java 一同使用，以创建需要从 Excel 中获取数据的 Selenium 测试。

Apache POI is used to work with Microsoft files. It can be used to carry on read, write, update, and other operations on files of various formats including excels. Java cannot be used directly to work with excel files, hence Apache POI (a third party API) is used along with Java, to create Selenium tests which require data to be fed from excel.

使用 Workbook 接口，可以通过 Apache POI 来处理 Excel 工作簿。该接口利用 WorkBookFactory 类来生成特定工作簿。HSSFWorkbook 类（实现了 Workbook 接口）具有可帮助对具有 .xls 格式的 * Microsoft Excel* 文件执行读取写入操作的方法。XSSFWorkbook 类（实现了 Workbook 接口）具有可帮助对具有 .xlsx 或 .xls 格式的 Microsoft Excel 和 OpenOffice XML 文件执行读取写入操作的方法。

The excel workbooks can be handled with Apache POI using the Workbook interface. This interface utilizes the WorkBookFactory class to produce the specific workbooks. The HSSFWorkbook class (implements the Workbook interface) has methods which help to carry out read and write operations to Microsoft Excel files having the .xls format. The XSSFWorkbook class (implements the Workbook interface) has methods which help to carry out read and write operations to Microsoft Excel and OpenOffice XML files having the .xlsx or .xls formats.

同样，使用 Sheet 接口可以通过 Apache POI 来处理 Excel 工作表。HSSFSheet 类（实现了 Sheet 接口）具有可在 HSSFWorkbook 工作簿（具有 .xls 格式的 Microsoft Excel 文件）中创建工作表的方法。XSSFSheet 类（实现了 Sheet 接口）具有可在 XSSFWorkbook 工作簿（具有 .xlsx 或 .xls 格式的 Microsoft Excel 和 OpenOffice XML 文件）中创建工作表的方法。

Similarly, the excel worksheets can be handled with Apache POI using the Sheet interface. The HSSFSheet class (implements the Sheet interface) has methods to create a worksheet in HSSFWorkbook workbooks (Microsoft Excel files having the .xls format). The XSSFSheet class(implements the Sheet interface) has methods to create a worksheet in XSSFWorkbook workbooks (Microsoft Excel and OpenOffice XML files having the .xlsx or .xls formats).

使用 Row 接口可以通过 Apache POI 来处理 Excel 行。HSSFRow 类（实现了 Row 接口）具有表示 HSSFSheet 中行的的方法。XSSFRow 类（实现了 Row 接口）具有表示 XSSFSheet 中行的的方法。

The excel rows can be handled with Apache POI using the Row interface. The HSSFRow class (implements the Row interface) has methods which signify rows in the HSSFSheet. The XSSFRow class (implements the Row interface) has methods which signify rows in the XSSFSheet.

使用 Cell 接口可以通过 Apache POI 来处理 Excel 单元格。HSSFCell 类（实现了 Row 接口）具有可以处理 HSSFRow 中单元格的方法。XSSFCell 类（实现了 Row 接口）具有可以处理 XSSFRow 中单元格的方法。

The excel cells can be handled with Apache POI using the Cell interface. The HSSFCell class (implements the Row interface) has methods which handle cells in the HSSFRow. The XSSFCell class (implements the Row interface) has methods which handle cells in the XSSFRow.

How to Install the Apache POI?

Step 1 − 从链接 Apache POI Common 向 pom.xml 文件中添加 Apache POI 通用依赖关系。

Step 1 − Add Apache POI Common dependencies to the pom.xml file from the link Apache POI Common.

Step 2 − 从链接 Apache POI API Based 向 pom.xml 文件中添加基于 OPC 和 OOXML 模式的 Apache POI API 依赖关系。

Step 2 − Add Apache POI API Based On OPC and OOXML Schemas dependencies to the pom.xml file from the link Apache POI API Based.

Step 3 − 保存带有所有依赖关系的 pom.xml 并更新 * maven* 项目。

Step 3 − Save the pom.xml with all the dependencies and update the maven project.

Read all Values From an Excel

我们来看一下名为 Details.xlsx 文件的 Excel 示例，我们将读取整个 Excel 文件并检索其所有值。

Let us take an example of the below excel named the Details.xlsx file, where we will read the whole excel file and retrieve all its values.

请注意： Details.xlsx 文件已放在 Resources 文件夹下方的项目中，如下图所示。

Please Note: The Details.xlsx file was placed within the project under the Resources folder as shown in the below image.

Example

package org.example;

import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;

public class ExcelRead {
   public static  void main(String args[]) throws IOException {

      // identify location of .xlsx file
      File f = new File("./Resources/Details.xlsx");
      FileInputStream i = new FileInputStream(f);

      // instance of XSSFWorkbook
      XSSFWorkbook w = new XSSFWorkbook(i);

      // create sheet in XSSFWorkbook with name Details1
      XSSFSheet s = w .getSheet("Details1");

      // handle total rows in XSSFSheet
      int r = s.getLastRowNum() - s.getFirstRowNum();

      // loop through rows
      for(int k = 0; k <= r ; k++){

         // get cells in each row
         int c = s.getRow(k).getLastCellNum();

         for(int j = 0; j < c; j++){

            // get cell values
            System.out.println(s.getRow(k).getCell(j).getStringCellValue());
         }
      }
   }
}

添加到 pom.xml 的依赖关系。

Dependencies added to pom.xml.

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
   xsi:schemaLocation="http://maven.apache.org/POM/4.0.0
   http://maven.apache.org/xsd/maven-4.0.0.xsd">

   <modelVersion>4.0.0</modelVersion>
   <groupId>org.example</groupId>
   <artifactId>SeleniumJava</artifactId>
   <version>1.0-SNAPSHOT</version>

   <properties>
      <maven.compiler.source>16</maven.compiler.source>
      <maven.compiler.target>16</maven.compiler.target>
      <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
   </properties>

   <!-- https://mvnrepository.com/artifact/org.seleniumhq.selenium/selenium-java -->
   <dependencies>
      <dependency>
         <groupId>org.seleniumhq.selenium</groupId>
         <artifactId>selenium-java</artifactId>
         <version>4.11.0</version>
      </dependency>

      <!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
      <dependency>
         <groupId>org.apache.poi</groupId>
         <artifactId>poi</artifactId>
         <version>5.2.5</version>
      </dependency>

      <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
      <dependency>
         <groupId>org.apache.poi</groupId>
         <artifactId>poi-ooxml</artifactId>
         <version>5.2.5</version>
      </dependency>
   </dependencies>
</project>

Name
Street
Ram
Street 12
Rohan
Street 110

Process finished with exit code 0

在上面的示例中，我们读取了整个 Excel 文件并在控制台中获取了所有值。

In the above example, we had read the whole excel file and obtained all its value in the console.

最后，收到了消息 Process finished with exit code 0 ，表示代码成功执行。

Finally, the message Process finished with exit code 0 was received, signifying successful execution of the code.

Read and Write Values in an Excel

我们来看一下名为 DetailsStudent.xlsx 文件的 Excel 示例，我们将从该 Excel 文件中读取值，并将这些数据输入到下面的注册页面中，成功之后，我们将在单元格中（在同一行和 E 列）写入文本 - Test Case: Pass 。如果没有成功执行，我们将在那同一个单元格中写入文本 - Test Case: Fail 。

Let us take an example of the below excel named the DetailsStudent.xlsx file, where we would read the value from that excel file and input those data to the below registration page and once successfully done, we would write the text - Test Case: Pass in the cell(at same row and Column E). If not successfully done, we would write the text - Test Case: Fail in that same cell.

下图显示了注册页面，我们将在字段 Full Name:, Last Name:, Username: 和 Password 中输入来自 DetailsStudent.xlsx 文件的数据。

The below image shows the registration page where we would enter data in the fields Full Name:, Last Name:, Username:, and Password from the DetailsStudent.xlsx file.

Please Note − DetailsStudent.xlsx Excel 文件已放在 Resources 文件夹下方的项目中，如下图所示。

Please Note − The DetailsStudent.xlsx excel file was placed within the project under the Resources folder as shown in the below image.

Example

package org.example;

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.WebElement;
import org.openqa.selenium.chrome.ChromeDriver;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.concurrent.TimeUnit;

public class ExcelReadWrite {
   public static  void main(String args[]) throws IOException {

      // identify location of .xlsx file
      File f = new File("./Resources/DetailsStudent.xlsx");
      FileInputStream i = new FileInputStream(f);

      // instance of XSSFWorkbook
      XSSFWorkbook w = new XSSFWorkbook(i);

      // create sheet in XSSFWorkbook with name Details1
      XSSFSheet s = w .getSheet("Details1");

      // handle total rows in XSSFSheet
      int r = s.getLastRowNum() - s.getFirstRowNum();

      // Initiate the Webdriver
      WebDriver driver = new ChromeDriver();

      // adding implicit wait of 15 secs
      driver.manage().timeouts().implicitlyWait(30, TimeUnit.SECONDS);

      // Opening the webpage where we will identify elements
      driver.get("https://www.tutorialspoint.com/selenium/practice/register.php#");

      //Identify elements for registration
      WebElement fname = driver.findElement(By.xpath("//*[@id='firstname']"));
      WebElement lname = driver.findElement(By.xpath("//*[@id='lastname']"));
      WebElement uname = driver.findElement(By.xpath("//*[@id='username']"));
      WebElement pass = driver.findElement(By.xpath("//*[@id='password']"));
      WebElement btn = driver.findElement(By.xpath("//*[@id='signupForm']/div[5]/input"));

      // loop through rows, read and enter values in form
      for(int j = 1; j <= r; j++) {
         fname.sendKeys(s.getRow(j).getCell(0).getStringCellValue());
         lname.sendKeys(s.getRow(j).getCell(1).getStringCellValue());
         uname.sendKeys(s.getRow(j).getCell(2).getStringCellValue());
         pass.sendKeys(s.getRow(j).getCell(3).getStringCellValue());

         // submit registration form
         btn.click();

         // verify form submitted
         WebElement fname1 = driver.findElement(By.xpath("//*[@id='firstname']"));
         String value = fname1.getAttribute("value");

         // create cell at Column 4 to write values in excel
         XSSFCell c = s.getRow(j).createCell(4);

         // write results in excel
         if (value.isEmpty()) {
            c.setCellValue("Test Case: PASS");
         } else {
            c.setCellValue("Test Case: FAIL");
         }

         // complete writing value in excel
         FileOutputStream o = new FileOutputStream("./Resources/DetailsStudent.xlsx");
         w.write(o);
      }

      // closing workbook object
      w.close();

      // Quitting browser
      driver.quit();
   }
}

添加到 pom.xml 的依赖关系。

Dependencies added to pom.xml.

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
   xsi:schemaLocation="http://maven.apache.org/POM/4.0.0
   http://maven.apache.org/xsd/maven-4.0.0.xsd">

   <modelVersion>4.0.0</modelVersion>
   <groupId>org.example</groupId>
   <artifactId>SeleniumJava</artifactId>
   <version>1.0-SNAPSHOT</version>

   <properties>
      <maven.compiler.source>16</maven.compiler.source>
      <maven.compiler.target>16</maven.compiler.target>
      <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
   </properties>

   <!-- https://mvnrepository.com/artifact/org.seleniumhq.selenium/selenium-java -->
   <dependencies>
      <dependency>
         <groupId>org.seleniumhq.selenium</groupId>
         <artifactId>selenium-java</artifactId>
         <version>4.11.0</version>
      </dependency>

      <!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
      <dependency>
         <groupId>org.apache.poi</groupId>
         <artifactId>poi</artifactId>
         <version>5.2.5</version>
      </dependency>

      <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
      <dependency>
         <groupId>org.apache.poi</groupId>
         <artifactId>poi-ooxml</artifactId>
         <version>5.2.5</version>
      </dependency>
   </dependencies>
</project>

Process finished with exit code 0

在上面的示例中，我们已经读取了整个 Excel 文件，并在第五列的单元格中写入了值 Test Case: Pass 。

In the above example, we had read the whole excel file and write the value Test Case: Pass in the cell at the fifth Column.

最后，收到了消息 Process finished with exit code 0 ，表示代码成功执行。

Finally, the message Process finished with exit code 0 was received, signifying successful execution of the code.

如上图所示，在测试基于同一 Excel 中注册数据后， Test Case: Pass 被写入 DetailsStudent.xlsx Excel 文件的第 5 列中。

As seen in the image above, Test Case: Pass was written into DetailsStudent.xlsx excel file in the Column 5 post the test run with respect to the registration data available in the same excel.

Conclusion

这就结束了我们关于 Selenium Webdriver Excel 数据文件教程的全面讲解。我们从描述 Apache POI 是什么开始，介绍如何安装 Apache POI，并逐步举例说明如何借助 Selenium Webdriver 和 Apache POI 读取和写入 Excel 中的值。这使你充分了解 Selenium Webdriver 中的 Excel 数据文件。明智的做法是不断练习你所学到的内容，并探索与 Selenium 相关的其他知识以加深你的理解并拓展你的视野。

This concludes our comprehensive take on the tutorial on Selenium Webdriver Excel Data File. We’ve started with describing what is Apache POI, how to install Apache POI, and walked through examples of how to read and write values in excel taking help of Apache POI along with Selenium Webdriver. This equips you with in-depth knowledge of the Excel Data File in Selenium Webdriver. It is wise to keep practicing what you’ve learned and exploring others relevant to Selenium to deepen your understanding and expand your horizons.