Handling excel spreadsheets with Cucumber Scenario Outline

Question

I am trying to see, if possible, to have a more elegant way to handle calling nTh numbers from a Cucumber Scenario Outline that correlates to an excel spreadsheet row(nth).

Currently I am using iteration numbers to define the row # of the excel spread sheet to pull the data from. I wanted to see if it was possible to use cucumber with excel in a more elegant way than the below example with the scenario outline.

Some background:

• Each iteration needs to be its own scenario. Hence why I'm not using a simple for loop with row.count.
• I am fully aware of scenario outline as a way to do data tables but my company wants to see a POF that we can integrate large data sets through excel.
• Current setup works for small data sets but when we get into the large excel spreadsheets I do not want to type out nth numbers on the outline

Cucumber code:

Feature: User is using an excel spreadsheet with cucumber driving it

  Scenario Outline: Data Driven with excel and data sets

   When I am on the amps mainscreen
    Then I input username and passwords with excel row"<row_index>" dataset

    Examples:
    | row_index  |
    | 1          |
    | 2          |
    | 3          |
    | 4          |

Step File:

//Excel Steps
@When("^I am on the amps mainscreen$")
public void i_am_on_the_amps_mainscreen()  {
    System.out.println("Im loading");
}
//Excel Steps
@Then("^I input username and passwords with excel row\"([^\"]*)\" dataset$")
public void i_input_username_and_passwords_with_excel_row_dataset(int rownum)    throws IOException {
    login.readExcel(rownum);
}

Actual Code:

 public void readExcel (int row) throws IOException{

    File src=new File("src/test/resources/username.xlsx");
    FileInputStream fis=new FileInputStream(src);
    XSSFWorkbook srcBook= new XSSFWorkbook(fis);
    XSSFSheet sourceSheet = srcBook.getSheetAt(0);

    XSSFRow sourceRow = sourceSheet.getRow(row);
    XSSFCell username=sourceRow.getCell(0);
    XSSFCell password=sourceRow.getCell(1);
    String userExcel = username.getStringCellValue();
    String pwExcel = password.getStringCellValue();
    System.out.println("The username is" +userExcel);
    System.out.println("The password is" +pwExcel);
    log.info("The username on " +row + " is: "+userExcel);
    log.info("The password on "+row+ " is: "+pwExcel);
    driver.findElement(txtbox_username).sendKeys(userExcel);
    driver.findElement(txtbox_password).sendKeys(pwExcel);
    driver.findElement(btn_logon).click();

}

Solution

You can use QMetry Automation Framework with gherkin factory. It supports test data provided outside feature file for example excel, xml, json, csv, or database. you can provide datafile for examples like:

Examples:{'datafile':'resources/testdata.xls'}

Here is the example you can check.