How to read excel file in cucumber project?

Question

I am creating testing automation framework using java but i am not able to read excel file in cucumber.

is there any way to use @DataProvider functionality og testNG?

I do not want to use datatable of feature file.

Possible duplicate of [Cucumber Selenium using Excel File as Data Table](https://stackoverflow.com/questions/44839635/cucumber-selenium-using-excel-file-as-data-table) — Marit, Jun 04 '19 at 13:10

score 1 · Answer 1 · answered Aug 11 '15 at 19:18

If you use CucumberJVM, I don't think you can make use of TestNG Data Providers without major hacks. Or at least this is not a "cucumber way" of doing things. Data Table is a Cucumber equivalent of TestNG Data Provider:

https://cucumber.io/docs/reference#data-tables

This is how you parametrise tests in Cucumber. I'm not saying the solution you are looking for can't be implemented, I'm saying you are most likely looking for a wrong thing. CucumberJVM makes use of DataProviders internally, to handle features this way:

https://github.com/cucumber/cucumber-jvm/blob/master/testng/src/main/java/cucumber/api/testng/AbstractTestNGCucumberTests.java

score 0 · Answer 2 · answered Nov 14 '19 at 13:16

In case it helps others:

here is described how to link a Cucumber Scenario Outline to read data from an Excel file https://startingwithseleniumwebdriver.blogspot.com/2017/04/getting-data-from-external-file-using.html
and here is described how to load data from an Excel file in a Cucumber feature file before executing Scenario steps https://startingwithseleniumwebdriver.blogspot.com/2017/04/loading-data-from-external-file-to.html

I my case this was very useful, as for each Scenario step I had to load Excel data (data from multiple rows having same group ID) in order to perform further validations. Like this the Cucumber feature file was a bit cleaner while the Excel had all the details under the hood.

score 0 · Answer 3 · answered Jul 10 '21 at 04:15

ExcelBDD Java edition can resolve this problem gracefully. code example

static Stream<Map<String, String>> provideExampleList() throws IOException {
    String filePath = TestWizard.getExcelBDDStartPath("excelbdd-test")
            + "excelbdd-test\\src\\test\\resources\\excel.xlsx";
    return Behavior.getExampleStream(filePath,"Expected1","Scenario1");
}

@ParameterizedTest(name = "Test{index}:{0}")
@MethodSource("provideExampleList")
void testgetExampleWithExpected(Map<String, String> parameterMap) {
    assertNotNull(parameterMap.get("Header"));
    System.out.println(String.format("=======Header: %s=======", parameterMap.get("Header")));
    for (Map.Entry<String, String> param : parameterMap.entrySet()) {
        System.out.println(String.format("%s --- %s", param.getKey(), param.getValue()));
    }
}

more detail at ExcelBDD Guideline By Java Example

score -1 · Answer 4 · answered Dec 11 '16 at 10:28

Here is the example how to read TestData from excel

public class Framework {
static String TestDataPath = System.getProperty("user.dir")
   + "\\ExcelFiles\\TestData.xlsx";
public static HashMap<String, HashMap<String, String>> hm1 = new HashMap<>();
static String s3;
public static void ReadTestData() throws IOException {

  FileInputStream file = new FileInputStream(TestDataPath);

  XSSFWorkbook workbook = new XSSFWorkbook(file);
  XSSFSheet sheet = workbook.getSheet("Sheet1");
  Row HeaderRow = sheet.getRow(0);
  for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
   Row currentRow = sheet.getRow(i);
   HashMap<String, String> currentHash = new HashMap<String, String>();
   for (int j = 0; j < currentRow.getPhysicalNumberOfCells(); j++) {

    Cell currentCell1 = currentRow.getCell(0);
    switch (currentCell1.getCellType()) {
    case Cell.CELL_TYPE_STRING:
     s3 = currentCell1.getStringCellValue();
     System.out.println(s3);
     break;
    case Cell.CELL_TYPE_NUMERIC:
     s3 = String.valueOf(currentCell1.getNumericCellValue());
     System.out.println(s3);
     break;
    }

    Cell currentCell = currentRow.getCell(j);
    switch (currentCell.getCellType()) {
    case Cell.CELL_TYPE_STRING:
     currentHash.put(HeaderRow.getCell(j).getStringCellValue(),
       currentCell.getStringCellValue());
     break;
    case Cell.CELL_TYPE_NUMERIC:
     currentHash.put(HeaderRow.getCell(j).getStringCellValue(),
       String.valueOf(currentCell.getNumericCellValue()));
     break;
    }

   }
   
   hm1.put(s3, currentHash);
  }

Here is the Model cucumber file and testData.

Scenario Outline: Successful Login with Valid Credentials
 Given User is on Home Page
 When User Navigate to LogIn Page
 And User enters mandatory details of "<TextCase>" 
 Then Message displayed Login Successfully
 Examples:
    |TextCase| 
    |Case1   |
    |Case2   |


[Test data img Link][1]


  [1]: https://i.stack.imgur.com/IjOap.png

Here is the Model Stepdefination File

@When("^User enters mandatory details of \"([^\"]*)\"$")
 public void user_enters_mandatory_details_of(String arg1) throws Throwable {
     // Write code here that turns the phrase above into concrete actions

  driver.FindElement("UserName").sendKeys(Framework.hm1.get(arg1).get("UserName"));
  
  Framework.FindElement("Password").sendKeys(Framework.hm1.get(arg1).get("Password"));
  
 }

Follow above three steps in cucumber you will able to read test data.

How to read excel file in cucumber project?

4 Answers4