新手求助：基于Apache POI实现Excel驱动Selenium自动化测试（保留指定循环）

我是Selenium完全新手，无法实现以下Selenium代码的自动化。尝试多种方法后，仍在从Excel获取数据并执行自动化的环节遇到问题，甚至试过TestNG方法也未解决。现请求帮助，如何在不修改for (int i= 1; i<=noOfRows ; i++)这一步行号逻辑的前提下，实现该代码的自动化。

原代码 #

public static void main(String[] args) throws IOException, InterruptedException {

    System.setProperty("driver location");
    WebDriver driver = new FirefoxDriver();

    driver.get("link");


    FileInputStream file = new FileInputStream("xcel file location");

    XSSFWorkbook workbook = new XSSFWorkbook(file);
    XSSFSheet sheet= workbook.getSheet("SO Reg");

    int noOfRows = sheet.getLastRowNum(); // returns the row count
    System.out.println("No. of Records in the Excel Sheet:" + noOfRows);

    int cols=sheet.getRow(1).getLastCellNum();  
    System.out.println("No. of Records in the Excel Sheet:" + cols);

    for (int i= 1; i<=6; i++)
    {
                String SO_Name = row.getCell(0).getStringCellValue();
                String Contact_Person = row.getCell(1).getStringCellValue();
                String Address_1 = row.getCell(2).getStringCellValue();
                String Address_2 = row.getCell(3).getStringCellValue();
                String City = row.getCell(4).getStringCellValue();
                String State = row.getCell(5).getStringCellValue();
                String ZipCode = row.getCell(6).getStringCellValue();
                String Phone_Number = row.getCell(8).getStringCellValue();
                String Username = row.getCell(9).getStringCellValue();
                String Email = row.getCell(10).getStringCellValue();
                String Re_Type_Email = row.getCell(11).getStringCellValue();

                //Registration Process

                driver.findElement(By.cssSelector("p.text-white:nth-child(4) > a:nth-child(1)")).click(); //create an account
                Thread.sleep(5000); 

                //Enter Data information

                driver.findElement(By.id("SOName")).sendKeys(SO_Name);
                driver.findElement(By.xpath("//*[@id=\"ContactPerson\"]")).sendKeys(Contact_Person);
                driver.findElement(By.xpath("//*[@id=\"AddressLine1\"]")).sendKeys(Address_1);
                driver.findElement(By.xpath("//*[@id=\"AddressLine2\"]")).sendKeys(Address_2);
                driver.findElement(By.id("City")).sendKeys(City);
                driver.findElement(By.id("State")).sendKeys(State);
                driver.findElement(By.id("ZipCode")).sendKeys(ZipCode);
                driver.findElement(By.id("Phone")).sendKeys(Phone_Number);
                driver.findElement(By.xpath("//*[@id=\"UserName\"]")).sendKeys(Username);       
                driver.findElement(By.xpath("//*[@id=\"Email\"]")).sendKeys(Email);
                driver.findElement(By.xpath("//*[@id=\"RandText\"]")).sendKeys(Re_Type_Email);
                driver.findElement(By.id("ConfirmBox")).click();
                driver.findElement(By.xpath("/html/body/app-root/app-soregistration/div[2]/div/div/div/div/form[2]/div/div[12]/div/button[1]")).click();
                driver.findElement(By.cssSelector(".btn-green-text-black")).click(); //finish button


                driver.findElement(By.cssSelector("p.text-white:nth-child(4) > a:nth-child(1)")).click(); //create an account
                Thread.sleep(5000); 
            }
        }
    }       
}

问题分析与修正方案 #

原代码存在语法缺失、逻辑漏洞等问题，以下是严格保留for (int i= 1; i<=noOfRows ; i++)行号逻辑的修复版本：

核心修复点 #

1. 补全Firefox驱动路径配置
2. 循环内新增当前行对象获取逻辑，避免空指针异常
3. 增加单元格空值与类型兼容处理，防止读取失败
4. 关闭Excel文件流、工作簿及浏览器驱动，避免资源泄漏
5. 简化部分定位器（如将xpath替换为id），提升稳定性

修正后代码 #

import org.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.firefox.FirefoxDriver;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFCell;
import java.io.FileInputStream;
import java.io.IOException;

public class SeleniumExcelAutomation {
    public static void main(String[] args) throws IOException, InterruptedException {
        // 配置Firefox驱动路径，替换为本地实际路径
        System.setProperty("webdriver.gecko.driver", "C:\\your-path\\geckodriver.exe");
        WebDriver driver = new FirefoxDriver();
        driver.manage().window().maximize();

        // 打开目标网站，替换为实际链接
        driver.get("https://your-target-website.com");

        // 读取Excel文件，替换为实际文件路径
        FileInputStream file = new FileInputStream("C:\\your-path\\data.xlsx");
        XSSFWorkbook workbook = new XSSFWorkbook(file);
        XSSFSheet sheet = workbook.getSheet("SO Reg");

        if (sheet == null) {
            System.out.println("工作表'SO Reg'不存在");
            driver.quit();
            workbook.close();
            file.close();
            return;
        }

        int noOfRows = sheet.getLastRowNum();
        System.out.println("Excel记录行数：" + noOfRows);

        int cols = sheet.getRow(1).getLastCellNum();
        System.out.println("Excel列数：" + cols);

        // 保留要求的循环逻辑：从第1行到最后一行
        for (int i = 1; i <= noOfRows; i++) {
            XSSFRow row = sheet.getRow(i);
            if (row == null) continue; // 跳过空行

            // 安全读取单元格内容
            String SO_Name = getCellValue(row, 0);
            String Contact_Person = getCellValue(row, 1);
            String Address_1 = getCellValue(row, 2);
            String Address_2 = getCellValue(row, 3);
            String City = getCellValue(row, 4);
            String State = getCellValue(row, 5);
            String ZipCode = getCellValue(row, 6);
            String Phone_Number = getCellValue(row, 8);
            String Username = getCellValue(row, 9);
            String Email = getCellValue(row, 10);
            String Re_Type_Email = getCellValue(row, 11);

            // 注册流程
            driver.findElement(By.cssSelector("p.text-white:nth-child(4) > a:nth-child(1)")).click();
            Thread.sleep(5000);

            // 输入表单数据
            driver.findElement(By.id("SOName")).sendKeys(SO_Name);
            driver.findElement(By.id("ContactPerson")).sendKeys(Contact_Person);
            driver.findElement(By.id("AddressLine1")).sendKeys(Address_1);
            driver.findElement(By.id("AddressLine2")).sendKeys(Address_2);
            driver.findElement(By.id("City")).sendKeys(City);
            driver.findElement(By.id("State")).sendKeys(State);
            driver.findElement(By.id("ZipCode")).sendKeys(ZipCode);
            driver.findElement(By.id("Phone")).sendKeys(Phone_Number);
            driver.findElement(By.id("UserName")).sendKeys(Username);
            driver.findElement(By.id("Email")).sendKeys(Email);
            driver.findElement(By.id("RandText")).sendKeys(Re_Type_Email);
            driver.findElement(By.id("ConfirmBox")).click();
            
            // 替换绝对xpath为相对定位（需根据实际页面调整）
            driver.findElement(By.cssSelector("form:nth-of-type(2) button[type='submit']")).click();
            driver.findElement(By.cssSelector(".btn-green-text-black")).click();
        }

        // 关闭资源
        driver.quit();
        workbook.close();
        file.close();
    }

    // 辅助方法：兼容不同类型单元格的读取
    private static String getCellValue(XSSFRow row, int cellIndex) {
        XSSFCell cell = row.getCell(cellIndex);
        if (cell == null) return "";
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                return String.valueOf((long) cell.getNumericCellValue()); // 处理数字类型（如邮编、电话）
            default:
                return "";
        }
    }
}

额外建议 #

• 替换Thread.sleep为显式等待，提升脚本稳定性，示例：

  WebDriverWait wait = new WebDriverWait(driver, 10);
  wait.until(ExpectedConditions.elementToBeClickable(By.id("SOName"))).sendKeys(SO_Name);
  

• 确保项目引入Apache POI（poi、poi-ooxml）和Selenium Java依赖
• 确认Excel中"SO Reg"工作表存在，数据从第1行开始（第0行为表头）

内容的提问来源于stack exchange，提问作者Manshi Prabhu