How to pass data from number of sheets in a workbook with the help of dataprovider

Question

I like to pass test data from number of sheets in a workbook with the help of data provider.Means i like to run one test case from one sheet and another test case from another sheet.Is it possible through dataprovider?

Answer 1

Try the following code as it has been programmed for a single sheet yet you can implement it for multiple sheet using getNumberOfSheets keyword.

The following is a code that works fine for me

static Object[][] myObject = null;

    @DataProvider(name = "Authentication")
    public static Object[][] credentials() { 

        FileInputStream file;
        try {
            String filePath = System.getProperty("user.dir") + "\\needful\\Book1.xlsx";
            System.out.println(filePath);

            file = new FileInputStream(new File(filePath));

            // Get the workbook instance for XLS file
            XSSFWorkbook workbook = new XSSFWorkbook(file);

            // Get first sheet from the workbook
            XSSFSheet sheet = workbook.getSheetAt(0);

            // Get iterator to all the rows in current sheet
            Iterator<Row> rowIterator = sheet.iterator();

            Row row = rowIterator.next();
            int noOfrepeat = sheet.getLastRowNum();
            System.out.println("Test Repeat time " + noOfrepeat);

            short noOfData = row.getLastCellNum();
            System.out.println("No of Test Data " + noOfData);

            myObject = new Object[noOfrepeat][noOfData];

            int repeat_int = 0;
            while (rowIterator.hasNext()) {
                row = rowIterator.next();

                // For each row, iterate through each columns
                Iterator<Cell> cellIterator = row.cellIterator();

                String str = null;
                int data_int = 0;
                while (cellIterator.hasNext()) {

                    Cell cell = cellIterator.next();

                    switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_BOOLEAN:
                        str = "" + cell.getBooleanCellValue();
                        break;
                    case Cell.CELL_TYPE_NUMERIC:
                        str = "" + cell.getNumericCellValue();
                        break;
                    case Cell.CELL_TYPE_STRING:
                        str = "" + cell.getStringCellValue();
                        break;
                    }

                    myObject[repeat_int][data_int] = str;

                    System.out.println("repeat_int:" + repeat_int + " data_int:" + data_int + " - "
                            + myObject[repeat_int][data_int]);

                    data_int++;

                }
                System.out.println("");

                repeat_int++;

            }

            file.close();
            FileOutputStream out = new FileOutputStream(
                    new File(System.getProperty("user.dir") + "\\needful\\output.xlsx"));

            workbook.write(out);
            workbook.close();
            out.close();

        } catch (IOException e) {
            e.printStackTrace();
        }

        return myObject;
    }

    // Here we are calling the Data Provider object with its Name
    @Test(dataProvider = "Authentication")
    public void test(String sUsername, String sPassword, String str) {

        System.out.println(sUsername + " , " + sPassword + " , " + str);

    }

Answer 2

yes it is possible, by using different Dataproviders for different test methods

@Test(dataProvider="dp1")
public void testMethod1() {
    //Method body here
}

@Test(dataProvider="dp2")
public void testMethod2() {
   // Method body here
}

@DataProvider(name="dp1") 
public Object[][] dataProvider1{

   // Logic for DataProvider 1 here (return data from sheet 1)
}

@DataProvider(name="dp2") 
public Object[][] dataProvider2{

   // Logic for DataProvider 2 here (return data from sheet 2)
}