Search code examples
javaseleniumcucumberbddcucumber-jvm

Java Sellinum Cucumber Excell data driven

Need to get "value" based on given "key" from Excel file I have excel file File name Test xlsx and sheet name sheet1

And sheet contains following key and value pairs and. JIRA ticket is unique .

Test case description testdata key Testdatavalue testdata2 key Testdata2 Value testdata3 key Testdata3 value
Sampiletest description1 Testcase-jira-1 user1id Harshadh Password 123ggg
Sampiletest2 discription Testcase-jira-2 user2 Ramu Password123 333ggg
Sampiletest3 discription Test case jira-3 user3 latha Password556 73hhh

Up to N number of rows

Here, I needs to get the data in following way by using Java Selenium Cucumber. I am going to use above test data to pass in Cucumber step definition class file by BDD way.

How can we get the data in definition file for following way 1)If pass Key value from current row how can we get the value of value for provide test input for webSeleinum element Example 4th row data

Sampiletest3 discription|Test case jira-3| user3|latha|Password556|73hhh
.....

If I call the "user3" that should return "Password556" Same way any row I need to get the value. Please guide me

Solution

  • You can try the below code.

    Feature file:

    • In examples, you can give the row numbers and sheet name to use the data for itterations.
        Scenario Outline: Login to the application with multiple users.
    
        Given get data from datasheet with "<test_id>" and "<sheetName>"
        And login to the application
    
        Examples: 
          | test_id | sheetName   |
          |       1 | Login       |
          |       2 | Login       |

    Excel data:

    enter image description here

    Read the data from excel and store it in a hashmap:

    • Create a class to read the data (Example: ExcelReader)
    • Use org.apache.poi.ss.usermodel and org.apache.poi.xssf.usermodel imports
        
    public class ExcelReader {
    
        private File file;
        private FileInputStream inputStream;
        private String testID;
        private String sheetName;
        private int testIdColumn;
        private int numberOfColumns;
        private XSSFCell cell;
        public  HashMap<String, String> fieldsAndValues;
    
        public ExcelReader(String testId, String sheetName) {
            file = new File(System.getProperty("user.dir") + "Excel location path");
            try {
                inputStream = new FileInputStream(file);
            } catch (FileNotFoundException e) {
                System.out.println("File not found at given location: " + e);
            }
            this.testID = testId;
            this.sheetName = sheetName;
    
            this.readExcelAndCreateHashMapForData();
        }
    
        public HashMap<String, String> readExcelAndCreateHashMapForData() {
            try {
                fieldsAndValues = new HashMap<String, String>();
    
                XSSFWorkbook workBook = new XSSFWorkbook(inputStream);
                XSSFSheet sheet = workBook.getSheet(sheetName);
    
                /* Get number of rows */
                int lastRow = sheet.getLastRowNum();
                int firstRow = sheet.getFirstRowNum();
                int numberOfRows = lastRow - firstRow;
    
                /*
                 * Get test_Id column number.
                 */
                outerloop: for (int row = 0; row < numberOfRows; row++) {
                    numberOfColumns = sheet.getRow(row).getLastCellNum();
                    for (int cellNumber = 0; cellNumber < numberOfColumns; cellNumber++) {
                        cell = sheet.getRow(row).getCell(cellNumber);
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        if (sheet.getRow(row).getCell(cellNumber).getStringCellValue().equalsIgnoreCase("test_ID")) {
                            testIdColumn = sheet.getRow(row).getCell(cellNumber).getColumnIndex();
                            break outerloop;
                        }
                    }
                }
    
                /*
                 * Search for the test id value.
                 */
                outerloop: for (int i = 0; i <= numberOfRows; i++) {
                    
                    cell = sheet.getRow(i).getCell(testIdColumn);
                    cell.setCellType(Cell.CELL_TYPE_STRING);
    
                    if (testID.equals(sheet.getRow(i).getCell(testIdColumn).getStringCellValue())) {
    
                        
                        for (int j = 0; j < numberOfColumns; j++) {
                            XSSFCell key = sheet.getRow(testIdColumn).getCell(j);
                            XSSFCell value = sheet.getRow(i).getCell(j);
    
                            key.setCellType(Cell.CELL_TYPE_STRING);
    
                            if (value == null) {
                                // Not capturing blank cells.
                            } else if (value.getCellType() == XSSFCell.CELL_TYPE_BLANK) {
                                // Not capturing blank cells.
                            } else {
                                value.setCellType(Cell.CELL_TYPE_STRING);
                                String fieldName = sheet.getRow(testIdColumn).getCell(j).getStringCellValue().trim();
                                String fieldValue = sheet.getRow(i).getCell(j).getStringCellValue().trim();
                                fieldsAndValues.put(fieldName, fieldValue);
                            }
                        }
                        System.out.println("Fields and values: " + Arrays.toString(fieldsAndValues.entrySet().toArray()));
                        break outerloop;
                    }
                }
            } catch (Exception e) {
                System.out.println("Exception occurred at getting the sheet: " + e);
            }
            /* Return the hash map */
            return fieldsAndValues;
        }
    
    }

    StepDefinition:

    
         ExcelReader excelReader;
    
        @Given("get data from datasheet with \"(.*)\" and \"(.*)\"$")
            public void get_data_from_datasheet(String testId, String sheetName) {            
            excelReader = new ExcelReader(testId, sheetName);
            }
    

     @And("login to the application")
        public void loginApplication(){      
driver.findElement(By.xpath("element")).sendKeys(excelReader.fieldsAndValues.get("UserName"));         
driver.findElement(By.xpath("element")).sendKeys(excelReader.fieldsAndValues.get("PassWord"));      
          driver.findElement(By.xpath("element")).click();  
        }