java - 使用 Selenium WebDriver 在 Java 中将状态写入 Excel 文件

Question

我可以通过我的程序将状态写入 Excel 文件，但仍然存在问题，状态未正确写入 Excel 工作表。控制台输出正常 -

Value of MoulikaNimmala is: Fail key is:MoulikaNimmala Fail and 1

Value of lakshman_nimmala@yahoo.com is: Pass key is:lakshman_nimmala@yahoo.com Pass and 2

Value of sailakshman.nimmala@gmail.com is: Fail key is:sailakshman.nimmala@gmail.com Fail and 3

Value of Keshav is: Fail key is:Keshav Fail and 4

但 Excel 工作表中的状态填写不正确。 lakshman_nimmala@yahoo 应填写 Pass，keshav 应填写 Fail。

    @Test(priority = 2)
    public void readData() throws Exception {
    String filePath = System.getProperty("user.dir") + "\\Test Data";
    String fileName = "editSubscriptions.xls";
    String sheetName = "datapool";

    File file = new File(filePath + "\\" + fileName);
    FileInputStream fis = new FileInputStream(file);
    Workbook workbook = null;

    String fileExtName = fileName.substring(fileName.indexOf("."));

    if (fileExtName.equals(".xlsx")) {
        workbook = new XSSFWorkbook(fis);
    } else if (fileExtName.equals(".xls")) {
        workbook = new HSSFWorkbook(fis);
    }
    Sheet sheet = workbook.getSheet(sheetName);
    int rowCount = sheet.getLastRowNum() - sheet.getFirstRowNum();

    HashMap<String, String> hm = new HashMap<String, String>();

    for (int i = 1; i < rowCount+1; i++) {
        Row row = sheet.getRow(i);

            WebDriverWait wait = new WebDriverWait(driver, 10);
            wait.until(ExpectedConditions.visibilityOfElementLocated(By.xpath("//*[text()='Sign In']")));
            driver.findElement(By.xpath("//*[text()='Sign In']")).click();

            WebDriverWait wait1 = new WebDriverWait(driver, 10);
            wait1.until(ExpectedConditions.visibilityOfElementLocated(By.name("username")));
            driver.findElement(By.name("username")).clear();

            driver.findElement(By.name("username")).sendKeys(row.getCell(0).toString());
            driver.findElement(By.name("passwd")).clear();

            driver.findElement(By.name("passwd")).sendKeys(row.getCell(1).toString());
            driver.findElement(By.name("signin")).click();
            Thread.sleep(10000);
            try {
                new Actions(driver).moveToElement(driver.findElement(By.xpath("//*[text()='nimmala']"))).perform();
                driver.findElement(By.xpath("//*[text()='Sign Out']")).click();

                WebDriverWait wait2 = new WebDriverWait(driver, 10);
                wait2.until(ExpectedConditions.visibilityOfElementLocated(By.xpath("//*[text()='Sign In']")));
                driver.findElement(By.xpath("//*[text()='Sign In']")).click();

                hm.put(row.getCell(0).toString(), "Pass");
            } catch (Exception ex) {
                //ex.printStackTrace();
                hm.put(row.getCell(0).toString(), "Fail");
                driver.get("http://in.yahoo.com/?p=us");
            }
    }  

    Set<String> keys = hm.keySet();

    int i=1;
    for (String key: keys){
        System.out.println("Value of "+key+" is: "+hm.get(key));

        String filePath1 = System.getProperty("user.dir") + "\\Test Data";
        String fileName1 = "editSubscriptions.xls";
        String sheetName1 = "datapool";

        File file1 = new File(filePath1 + "\\" + fileName1);
        FileInputStream fis1 = new FileInputStream(file1);
        Workbook workbook1 = null;

        String fileExtName1 = fileName1.substring(fileName1.indexOf("."));

        if (fileExtName1.equals(".xlsx")) {
            workbook1 = new XSSFWorkbook(fis1);
        } else if (fileExtName1.equals(".xls")) {
            workbook1 = new HSSFWorkbook(fis1);
        }
        Sheet sheet1 = workbook1.getSheet(sheetName1);
        int rowCount1 = sheet1.getLastRowNum() - sheet1.getFirstRowNum();

            Cell cell1 = sheet1.getRow(i).createCell(2);
            cell1.setCellType(Cell.CELL_TYPE_STRING);
            cell1.setCellValue(hm.get(key));
            System.out.println(" key is:" + key+" "+hm.get(key) + " and " + i);
            i = i+1;

      //Close input stream
        fis1.close();

      //Create an object of FileOutputStream class to create write data in excel file
        FileOutputStream outputStream = new FileOutputStream(file1);

        //write data in the excel file
        workbook1.write(outputStream);

        //close output stream
        outputStream.close();
    }
  }

问题在于识别行索引。

有没有办法让我们根据 Excel 文件中的单元格值来识别行索引，以便我可以用正确的行索引替换下面语句中的 i？:

单元格 cell1 =sheet1.getRow(i).createCell(2);

Answer 1

要返回给定单元格值的行号，以下代码将起作用 -

public int findRow(Sheet sheet, String content) {
int rowNum = 0;
for (Row row : sheet) {
for (Cell cell: row) {
    if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
        if (cell.getRichStringCellValue().getString().trim().equals(content)) {
                    return row.getRowNum();
                }
            }
        }
    }
    return 0;}