Getting Data From External File Using Cucumber - JVM

Cucumber facilitates us to make our Scenario as data driven if we change it to Scenario Outline with Example. But still there is a constraint on this, user not able to feed any external file within it.

So now we learn how to feed data from external excel file. There are 2 ways to make our Cucumber feature file have ability to load the data from external excel file.

1. Providing numeric Row number in Example part of  Scenario Outline.
2. Update the feature file with external data before start to to execute the Scenarios.

Here we learn the first option and next one we will learn later.

Step 1 :

Create  feature file and mention the row number of external excel file in Example section of Scenario Outline.

Feature: This is a sample feature file

  Scenario Outline: This is a scenario to test datadriven test on Cucumber JVM.
    Given scenario data
    When executed from Runner Class
    Then UserName and Password shows on console form given <RowsNumber>.

    Examples:
      | RowsNumber |
      |          1             |
      |          2             |

Step 2 :

Generate glue code or step file.

public class TestSteps {
    @Given("^scenario data$")
    public void scenarioData() throws Throwable {
        System.out.println("Scenario Have Some Data");
    }

    @When("^executed from Runner Class$")
    public void executedFromRunnerClass() throws Throwable {
        System.out.println("Executed From Runner Class");
    }

    @Then("^UserName and Password shows on console form given (\\d+)\\.$")
    public void usernameAndPasswordShowsOnConsoleFormGiven(int rowNumber) throws Throwable {
        ExcelReader externalData = new ExcelReader();
        List<Map<String, String>> testData = externalData.getData("./resources/TestData.xlsx", 0);
        System.out.println("UserName : " + testData.get(rowNumber).get("UserName") + " Password : " + testData.get(rowNumber).get("Password"));
    }
}  

Now  if we drill down this code, we can understand what happen with this row number which is specified in our feature file.

In the last method we return our excel file, then we pass the row number and pickup our desired column after passing the column name on map.

Step 3 :

So let's create this excel reader class :

 public class ExcelReader {

    public List<Map<String, String>> getData(String excelFilePath, String sheetName)
            throws InvalidFormatException, IOException {
        Sheet sheet = getSheetByName(excelFilePath, sheetName);
        return readSheet(sheet);
    }

    public List<Map<String, String>> getData(String excelFilePath, int sheetNumber)
            throws InvalidFormatException, IOException {
        Sheet sheet = getSheetByIndex(excelFilePath, sheetNumber);
        return readSheet(sheet);
    }

    private Sheet getSheetByName(String excelFilePath, String sheetName) throws IOException, InvalidFormatException {
        Sheet sheet = getWorkBook(excelFilePath).getSheet(sheetName);
        return sheet;
    }

    private Sheet getSheetByIndex(String excelFilePath, int sheetNumber) throws IOException, InvalidFormatException {
        Sheet sheet = getWorkBook(excelFilePath).getSheetAt(sheetNumber);
        return sheet;
    }

    private Workbook getWorkBook(String excelFilePath) throws IOException, InvalidFormatException {
        return WorkbookFactory.create(new File(excelFilePath));
    }

    private List<Map<String, String>> readSheet(Sheet sheet) {
        Row row;
        int totalRow = sheet.getPhysicalNumberOfRows();
        List<Map<String, String>> excelRows = new ArrayList<Map<String, String>>();
        int headerRowNumber = getHeaderRowNumber(sheet);
        if (headerRowNumber != -1) {
            int totalColumn = sheet.getRow(headerRowNumber).getLastCellNum();
            int setCurrentRow = 0;
            if(sheet instanceof HSSFSheet){
                setCurrentRow = 1;
            }
            for (int currentRow = setCurrentRow; currentRow <= totalRow; currentRow++) {
                row = getRow(sheet, sheet.getFirstRowNum() + currentRow);
                LinkedHashMap<String, String> columnMapdata = new LinkedHashMap<String, String>();
                for (int currentColumn = 0; currentColumn < totalColumn; currentColumn++) {
                    columnMapdata.putAll(getCellValue(sheet, row, currentColumn));
                }
                excelRows.add(columnMapdata);
            }
        }
        return excelRows;
    }


    private int getHeaderRowNumber(Sheet sheet) {
        Row row;
        int totalRow = sheet.getLastRowNum();
        for (int currentRow = 0; currentRow <= totalRow + 1; currentRow++) {
            row = getRow(sheet, currentRow);
            if (row != null) {
                int totalColumn = row.getLastCellNum();
                for (int currentColumn = 0; currentColumn < totalColumn; currentColumn++) {
                    Cell cell;
                    cell = row.getCell(currentColumn, Row.CREATE_NULL_AS_BLANK);
                    if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                        return row.getRowNum();

                    } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                        return row.getRowNum();

                    }
                    else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
                        return row.getRowNum();
                    } else if (cell.getCellType() == Cell.CELL_TYPE_ERROR) {
                        return row.getRowNum();
                    }
                }
            }
        }
        return (-1);
    }

    private Row getRow(Sheet sheet, int rowNumber) {
        return sheet.getRow(rowNumber);
    }

    private LinkedHashMap<String, String> getCellValue(Sheet sheet, Row row, int currentColumn) {
        LinkedHashMap<String, String> columnMapdata = new LinkedHashMap<String, String>();
        Cell cell;
        if (row == null) {
            if (sheet.getRow(sheet.getFirstRowNum()).getCell(currentColumn, Row.CREATE_NULL_AS_BLANK)
                    .getCellType() != Cell.CELL_TYPE_BLANK) {
                String columnHeaderName = sheet.getRow(sheet.getFirstRowNum()).getCell(currentColumn)
                        .getStringCellValue();
                columnMapdata.put(columnHeaderName, "");
            }
        } else {
            cell = row.getCell(currentColumn, Row.CREATE_NULL_AS_BLANK);
            if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                if (sheet.getRow(sheet.getFirstRowNum()).getCell(cell.getColumnIndex(), Row.CREATE_NULL_AS_BLANK)
                        .getCellType() != Cell.CELL_TYPE_BLANK) {
                    String columnHeaderName = sheet.getRow(sheet.getFirstRowNum()).getCell(cell.getColumnIndex())
                            .getStringCellValue();
                    columnMapdata.put(columnHeaderName, cell.getStringCellValue());
                }
            } else if (cell.getCellTypeEnum() == CellType.NUMERIC) {
                if (sheet.getRow(sheet.getFirstRowNum()).getCell(cell.getColumnIndex(), Row.CREATE_NULL_AS_BLANK)
                        .getCellType() != Cell.CELL_TYPE_BLANK) {
                    String columnHeaderName = sheet.getRow(sheet.getFirstRowNum()).getCell(cell.getColumnIndex())
                            .getStringCellValue();
                    columnMapdata.put(columnHeaderName, NumberToTextConverter.toText(cell.getNumericCellValue()));
                }
            } else if (cell.getCellType() == CellType.BLANK.getCode()) {
                if (sheet.getRow(sheet.getFirstRowNum()).getCell(cell.getColumnIndex(), Row.CREATE_NULL_AS_BLANK)
                        .getCellType() != Cell.CELL_TYPE_BLANK) {
                    String columnHeaderName = sheet.getRow(sheet.getFirstRowNum()).getCell(cell.getColumnIndex())
                            .getStringCellValue();
                    columnMapdata.put(columnHeaderName, "");
                }
            } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
                if (sheet.getRow(sheet.getFirstRowNum()).getCell(cell.getColumnIndex(), Row.CREATE_NULL_AS_BLANK)
                        .getCellType() != Cell.CELL_TYPE_BLANK) {
                    String columnHeaderName = sheet.getRow(sheet.getFirstRowNum()).getCell(cell.getColumnIndex())
                            .getStringCellValue();
                    columnMapdata.put(columnHeaderName, Boolean.toString(cell.getBooleanCellValue()));
                }
            } else if (cell.getCellType() == Cell.CELL_TYPE_ERROR) {
                if (sheet.getRow(sheet.getFirstRowNum()).getCell(cell.getColumnIndex(), Row.CREATE_NULL_AS_BLANK)
                        .getCellType() != Cell.CELL_TYPE_BLANK) {
                    String columnHeaderName = sheet.getRow(sheet.getFirstRowNum()).getCell(cell.getColumnIndex())
                            .getStringCellValue();
                    columnMapdata.put(columnHeaderName, Byte.toString(cell.getErrorCellValue()));
                }
            }
        }
        return columnMapdata;
    }
}

Step 4 : 

At last we need a runner class to run our cucumber project.

@RunWith(Cucumber.class)
@CucumberOptions(
  plugin = {
    "html:target/cucumber-html-report",
    "json:target/cucumber.json",
    "pretty:target/cucumber-pretty.txt",
    "usage:target/cucumber-usage.json",
    "junit:target/cucumber-results.xml",
    "progress:target/cucumber-progress.txt"
        },features ={"./resources/sample.feature"},
   glue ={"com/automation/cucumber/steps"},strict = true,
   dryRun= false,monochrome = true, snippets= SnippetType.CAMELCASE)
public class CucumberRunner {

} 

Output : 

Scenario Have Some Data
Executed From Runner Class
UserName : Test UserName 1 Password : Test Password 1
Scenario Have Some Data
Executed From Runner Class
UserName : Test UserName 2 Password : Test Password 2

2 Scenarios (2 passed)
6 Steps (6 passed)
0m1.312s