1

How to fetch data from excel sheet in selenium web driver, suppose I have 44 URLs in excel sheet I need to fetch it through selenium webdriver and wants to hit the URL.

  • Trivial task if you have decent programming skills. Learn programming in any language, and answer will be obvious. – Peter M. - stands for Monica Oct 10 '17 at 14:26
1

In order to accomplish this, you would need a function to read from the excel file. I am using such function for my testing. You might have to make some minor tweaks in order to make it fit for your requirement. Moreover, it will work fine for both .xls and .xlsx extension.


public ArrayList<String[]> ExcelFileReader(String fileName) throws IOException, NullPointerException {

        ArrayList<String[]> aro = new ArrayList<>();

        String excelFilePath = "complete path to access the file. Also mention the filename with extension";
        FileInputStream inputStream = new FileInputStream(new File(excelFilePath));
        String fileExtensionName = fileName.substring(fileName.indexOf("."));
        Workbook workbook = null;
        System.out.println(fileExtensionName);
        try {
            if (fileExtensionName.equals(".xlsx")) {
                workbook = new XSSFWorkbook(inputStream);
            } else if (fileExtensionName.equals(".xls")) {
                workbook = new HSSFWorkbook(inputStream);

            }
        } catch (NullPointerException e) {
            System.out.println(e);
        }
        Sheet firstSheet = workbook.getSheet("Enter sheet name here");
        int i = firstSheet.getLastRowNum();
        System.out.println(i);

        try {
            for (Row row : firstSheet) {
                int columns = row.getPhysicalNumberOfCells();
                String[] LoginData = new String[columns];
                LoginData[0] = row.getCell(0).getStringCellValue();
                LoginData[1] = row.getCell(1).getStringCellValue();
                //If there is YES in below column then that means that this set of test data will be used for testing otherwise will be ignored
                LoginData[2] = row.getCell(2).getStringCellValue();
                if (LoginData[2].equalsIgnoreCase("yes")) {
                    aro.add(LoginData);
                }
            }
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }

        workbook.close();
        inputStream.close();
        return aro;

    }
| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.