在Selenium WebDriver中使用Apache POI使用TestNG进行DataDriven测试

Question

我是TestNG框架的新手。请指导如何使用Apache POI（Excel）参数化测试用例。

public class spreadData {

private transient Collection data = null;

public spreadData(final InputStream excelInputStream) throws IOException {
    this.data = loadFromSpreadsheet(excelInputStream);
}

public Collection getData() {
    return data;
}

private Collection loadFromSpreadsheet(final InputStream excelFile)
        throws IOException {
    HSSFWorkbook workbook = new HSSFWorkbook(excelFile);

    data = new ArrayList();
    Sheet sheet = workbook.getSheetAt(0);

    int numberOfColumns = countNonEmptyColumns(sheet);
    List rows = new ArrayList();
    List rowData = new ArrayList();

   /*for (Row row : sheet) {
        if (isEmpty(row)) {
            break;
        } else {
            rowData.clear();
            for (int column = 0; column < numberOfColumns; column++) {
                Cell cell = row.getCell(column);
                rowData.add(objectFrom(workbook, cell));
            }
            rows.add(rowData.toArray());
        }
    }*/


    int rowStart = 1;
 //int rowEnd = Math.max(1400, sheet.getLastRowNum());
    for (int rowNum = rowStart; rowNum <=  sheet.getLastRowNum(); rowNum++) {
        //Row r = sheet.getRow(rowNum);
        Row read = sheet.getRow(rowNum);
        if (isEmpty(read)) {
            break;
        } else {
            rowData.clear();
            for (int column = 0; column < numberOfColumns; column++) {

                Cell cell = read.getCell(column);
                rowData.add(objectFrom(workbook, cell));
            }
            rows.add(rowData.toArray());
        }
    }
    return rows;
}

private boolean isEmpty(final Row row) {
    Cell firstCell = row.getCell(0);
    boolean rowIsEmpty = (firstCell == null)
            || (firstCell.getCellType() == Cell.CELL_TYPE_BLANK);
    return rowIsEmpty;
}

/**
 * Count the number of columns, using the number of non-empty cells in the
 * first row.
 */
private int countNonEmptyColumns(final Sheet sheet) {
    Row firstRow = sheet.getRow(0);
    return firstEmptyCellPosition(firstRow);
}

private int firstEmptyCellPosition(final Row cells) {
    int columnCount = 0;
    for (Cell cell : cells) {
        if (cell.getCellType() == Cell.CELL_TYPE_BLANK) {
            break;
        }
        columnCount++;
    }
    return columnCount;
}

private Object objectFrom(final HSSFWorkbook workbook, final Cell cell) {
    Object cellValue = null;

    if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
        cellValue = cell.getRichStringCellValue().getString();
    } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
        cellValue = getNumericCellValue(cell);
    } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
        cellValue = cell.getBooleanCellValue();
    } else if (cell.getCellType()  ==Cell.CELL_TYPE_FORMULA) {
        cellValue = evaluateCellFormula(workbook, cell);
    }

    return cellValue;

}

private Object getNumericCellValue(final Cell cell) {
    Object cellValue;
    if (DateUtil.isCellDateFormatted(cell)) {
        cellValue = new Date(cell.getDateCellValue().getTime());
    } else {
        cellValue = cell.getNumericCellValue();
    }
    return cellValue;
}

private Object evaluateCellFormula(final HSSFWorkbook workbook, final Cell cell) {
    FormulaEvaluator evaluator = workbook.getCreationHelper()
            .createFormulaEvaluator();
    CellValue cellValue = evaluator.evaluate(cell);
    Object result = null;

    if (cellValue.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
        result = cellValue.getBooleanValue();
    } else if (cellValue.getCellType() == Cell.CELL_TYPE_NUMERIC) {
        result = cellValue.getNumberValue();
    } else if (cellValue.getCellType() == Cell.CELL_TYPE_STRING) {
        result = cellValue.getStringValue();   
    }

    return result;
}

Answer 1

我不确定您想要哪组数据...但是dataProvider的工作原理如下：

假设我有一个测试，它接受一个字符串，然后接受一个int值，如下所示：

@Test(dataProvider = "excelData")
public void executeTest(String name, int value){}

我的数据提供程序如下所示：

@DataProvider(name = "excelData")
public Object[][] data(){
    return new Object[][]{
               {"Test",1},
               {"More Testing",7},
               {"Last Test",-5}}
}

测试将运行3次，数组的每一行都是要传入的数据集。您需要将excel数据转换为这样的格式。

注意，如果愿意，还可以返回迭代器 。