Selenium Java-将hashmap值写入excel

Question

    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);
        for (int j = 1; j < row.getLastCellNum(); j++) {
            System.out.println(row.getCell(j).getStringCellValue());
            driver.findElement(By.linkText("Login")).click();
            WebDriverWait wait = new WebDriverWait(driver, 10);
            wait.until(ExpectedConditions.visibilityOfElementLocated(By.id("username")));
            driver.findElement(By.id("username")).sendKeys(row.getCell(0).toString());
            driver.findElement(By.id("password")).sendKeys(row.getCell(1).toString());
            driver.findElement(By.name("submit")).click();
            Thread.sleep(10000);
            try {
                driver.findElement(By.linkText("Logout")).click();
                WebDriverWait wait1 = new WebDriverWait(driver, 10);
                wait1.until(ExpectedConditions.visibilityOfElementLocated(By.linkText("Login")));
                hm.put(row.getCell(0).toString(), "Pass");
            } catch (Exception ex) {
                hm.put(row.getCell(0).toString(), "Fail");
                driver.get("http://www.openclinica.com");
            }
        }
    }
    Set<String> keys = hm.keySet();
    for (String key: keys){
        System.out.println("Value of "+key+" is: "+hm.get(key));

        String filePath1 = System.getProperty("user.dir") + "\\Test Data";
        String fileName1 = "editSubscriptions1.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();

        for (int i=1; i < rowCount1; i++){
            Cell cell = sheet1.getRow(i).createCell(2);
            cell.setCellType(Cell.CELL_TYPE_STRING);
            cell.setCellValue(hm.put(key, hm.get(key)));
        }
    }
}

请帮我解决这个问题。

提前谢谢你。

Answer 1

int rowCount1 = sheet1.getLastRowNum() - sheet1.getFirstRowNum();

    for (int i=1; i < rowCount1; i++){
        Cell cell = sheet1.getRow(i).createCell(2);
        cell.setCellType(Cell.CELL_TYPE_STRING);
        cell.setCellValue(hm.put(key, hm.get(key)));
    }

根据文档，您的循环应该从索引0开始。

看起来您从未进入循环，因为您从索引1开始，但如果从空工作表开始，您的行计数初始值为0。

编辑：在评论之后，我会选择这样的解决方案（未经测试！）

 Set<String> keys = hm.keySet();
String filePath1 = System.getProperty("user.dir") + "\\Test Data";
    String fileName1 = "editSubscriptions1.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(); //set rowNumber to the last already set one
for (String key: keys){ //for every key write the name in column 1 and the result in column 2
    rowcount1++; //start at the next free row

    Cell cell1 = sheet1.createRow(rowCount1).createCell(1);
        Cell cell2 = sheet1.getRow(rowCount1).createCell(2);
        cell1.setCellType(Cell.CELL_TYPE_STRING);
        cell2.setCellType(Cell.CELL_TYPE_STRING);
        cell1.setCellValue(key));
        cell2.setCellValue(hm.get(key)));
    }
}