Carl Laneave Carl Laneave - 1 month ago 14
Java Question

Handling excel spreadsheets with Cucumber Scenario Outline

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

}

Answer

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.