Automação Datadriven arquivos xlsx

import java.io.IOException;

import java.io.InputStream;

import java.util.ArrayList;

import java.util.Collection;

import java.util.Date;

import java.util.List;

import org.apache.poi.ss.usermodel.Cell;

import org.apache.poi.ss.usermodel.CellValue;

import org.apache.poi.ss.usermodel.DateUtil;

import org.apache.poi.ss.usermodel.FormulaEvaluator;

import org.apache.poi.ss.usermodel.Row;

import org.apache.poi.ss.usermodel.Sheet;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

private transient Collection<Object[]> data = null;

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

public Collection<Object[]> getData() {
    return data;
}

private Collection<Object[]> loadFromSpreadsheet(final InputStream excelFile)
        throws IOException {
	//HSSFWorkbook workbook = new HSSFWorkbook(excelFile);
	XSSFWorkbook workbook = new XSSFWorkbook(excelFile);

    data = new ArrayList<Object[]>();
    Sheet sheet = workbook.getSheetAt(0);

    int numberOfColumns = countNonEmptyColumns(sheet);
    List<Object[]> rows = new ArrayList<Object[]>();
    List<Object> rowData = new ArrayList<Object>();

    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());
        }
    }
    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 XSSFWorkbook 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 XSSFWorkbook 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;
}

import java.io.FileInputStream;

import java.io.IOException;

import java.io.InputStream;

import java.util.Collection;

import org.junit.Before;

import org.junit.Test;

import org.junit.runner.RunWith;

import org.junit.runners.Parameterized;

import org.junit.runners.Parameterized.Parameters;

import com.alc.ceos.pageobject.GeneralInformation_AtaPage;

import com.alc.ceos.pageobject.LogoutPage;

import com.alc.ceos.pageobject.Lot_AtaPage;

import com.alc.ceos.pageobject.Manager_AtaPage;

import com.alc.ceos.pageobject.Organs_AtaPage;

import com.alc.ceos.util.SpreadsheetData;

private double codigo;
private int unity;
private int quantity;
private double value;

public AtasPagesValidation(double codigo, int unity, int quantity, double value) {

	this.codigo = codigo;
	this.unity = unity;
	this.quantity = quantity;
	this.value = value;
	
}

private static Boolean isLoggedIn = Boolean.FALSE;

//@Rule public TestName name = new TestName();
@Before
public void before(){
	if(isLoggedIn){
		LogoutPage.clickLogoutButton();
		isLoggedIn = Boolean.FALSE;
	}
}


@Test
public void should_create_ata_with_success(){
	
	Lot_AtaPage.acssess_Lotlink();
	Lot_AtaPage.add_NewIten();
	Lot_AtaPage.fillOut_general_Informations_Lote("28/06/2017", "30/07/2017");
	Lot_AtaPage.acssess_providerLink();
	Lot_AtaPage.fillOut_provider("5885");
	Lot_AtaPage.acssess_linkItens();
	Lot_AtaPage.fillOut_addItens();

            // Aqui eu passos os dados da planilha
	Lot_AtaPage.fillOut_addProduct(String.valueOf(this.codigo), String.valueOf(this.unity), 
			String.valueOf(this.quantity), String.valueOf(this.value));
	Lot_AtaPage.fillOut_totalValue();
	Lot_AtaPage.addLot();
	Lot_AtaPage.saveAta();
	Lot_AtaPage.isRegistered();
}

@Parameters
public static Collection spreadsheetData() throws IOException {
    InputStream spreadsheet = new FileInputStream("src\\main\\resources\\productLote.xlsx");
    return new SpreadsheetData(spreadsheet).getData();
}

*/

@RunWith(Suite.class)

@Suite.SuiteClasses({

LoginPageValidation.class,

AtasPagesValidation.class,

LogoutPageValidation.class

})

public static Boolean isAllTestsExecution = false;

@BeforeClass
public static void beforeClass() throws Exception {
	isAllTestsExecution = true;
	driver = Selenium.getDriver();
	driver.navigate().to(Config.SITE_ADDRESS);
}

@AfterClass
public static void afterClass() throws Exception {
	driver.quit();
}