Pulling data from multiple Excel sheets with @Dataprovider and passing it to @test

Question

I am facing an issue related to @Dataprovider and @Test

Scenario : I need to get data from multiple sheets(sheet1, sheet2, sheet3, .....) with help of @dataprovider annotation, I have web application in which there are multiple webpages in which I have to pass data.

I've implemented it using POM, I need to get the data from multiple sheets at once and pass it to @test.

below is the snippet:

 @DataProvider
    public Object[][] getHrwsIdentityData() throws IOException 
    {   
        List<Object[]> DataList = null;
        loggerManager.logger.info("Reading the position testdata from excel sheet");
        
        DataList = Excel.excelRead(".\\resources\\data\\HR Workwise Automation -New Hires.xlsx", "Identity Details", "Execute Flag", "Y");
        return DataList.toArray(new Object[DataList.size()][]);
    }
    
    @DataProvider
    public Object[][] getHrwsPersonalData() throws IOException
    {
        List<Object[]> DataList = null;
        loggerManager.logger.info("Reading the position testdata from excel sheet");
        DataList = Excel.excelRead(".\\resources\\data\\HR Workwise Automation -New Hires.xlsx", "Personal Information", "Execute Flag", "Y");
        return DataList.toArray(new Object[DataList.size()][]);
    }
    

 @Test(priority = 1,enabled = true, dataProvider="getHrwsPersonalData", description="Add New Employee in Manage Employees Page; Coverage: AddNewEmployee")
    public void updateEmployeePersonalInfo(LinkedHashMap<String, String> DataSet) throws IOException
    {
        hrws.addEmployeePersonalInfo(DataSet);
    }
    
    @Test(priority = 2,enabled = true, dataProvider="getHrwsJobInformation", description="Add New Employee in Manage Employees Page; Coverage: AddNewEmployee")
    public void updateEmployeeJobInfo(LinkedHashMap<String, String> DataSet) throws IOException
    {
        hrws.addEmployeeJobInfo(DataSet);
    }

I tried using different single @DataProviders for each @Test, but No Luck!!

Also below is the readExcel file

public static List<Object[]> excelRead(String sExcelPath, String sSheetName, String sCondCol, String sCondVal) throws IOException{

        String[] sHeaderKey = new String[0];
        String[] sValue = new String[0];
        LinkedHashMap<String, String> RowData;
        List<Object[]> DataList = new ArrayList<>();

        try {
            FileInputStream oFis = new FileInputStream(sExcelPath);

            Workbook workbook = null;

            // Using XSSF for xlsx format, for xls use HSSF

            workbook = new XSSFWorkbook(oFis);

            Sheet sheet = workbook.getSheet(sSheetName);

            Iterator<Row> rowIterator = sheet.iterator();

            DataFormatter formatter = new DataFormatter(Locale.US);

            while (rowIterator.hasNext()) {
                Boolean bHeaderRow = false;
                sValue = new String[0];
                Row row = rowIterator.next();
                if (row.getRowNum() == 0) {
                    bHeaderRow = true;
                }

                Iterator<Cell> cellIterator = row.cellIterator();
                while (cellIterator.hasNext()) {
                    Cell cell = cellIterator.next();
                    if (bHeaderRow && (cell.getCellType() != Cell.CELL_TYPE_BLANK)) {
                        sHeaderKey = Arrays.copyOf(sHeaderKey, sHeaderKey.length + 1);
                        sHeaderKey[cell.getColumnIndex()] = formatter.formatCellValue(cell);
                    } else if ((!bHeaderRow) && (sHeaderKey[cell.getColumnIndex()] != null)) {
                        sValue = Arrays.copyOf(sValue, sValue.length + 1);
                        sValue[cell.getColumnIndex()] = formatter.formatCellValue(cell);
                    }
                }

                if ((sHeaderKey.length != 0) && (sValue.length != 0)) {
                    RowData = new LinkedHashMap<String, String>();

                    for (int i = 0; i < sHeaderKey.length; i++) {
                        RowData.put(sHeaderKey[i], sValue[i]);
                    }
                    
                    if(RowData.get(sCondCol).trim().toLowerCase().equals(sCondVal.trim().toLowerCase())){
                        DataList.add(new Object[]{RowData});
                    }
                    
                }
            }
            workbook.close();
            oFis.close();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            throw e;
        }
        return DataList;

    }

I need to merge these two @dataproviders into one!!

Answer 1

You dont need to have multiple data provider for different tests. You need a mechanism to identify which test is calling the data provider. TestNg data providers are overloaded, you can create a data provider method like this Please don't be critical to the code inside this method as its just for demonstration

    @DataProvider(name = "AllTestData")
public static Object[][] GetDataForEveryOne(ITestNGMethod testContext)
{
    if(testContext.getMethodName().equals("LoginWithMultipleUsersTest"))
    {
        String[][] usernamePassArray = { { "testType1", "pass1" },
                                         { "testType2", "pass1" }};

        return usernamePassArray;
    }
    else if(testContext.getMethodName().equals("LoginWithMultipleDataTest"))
    {
        String[][] usernamePassArray = { { "user1", "pass1" },
                 { "user2", "pass2" },
                 { "user3", "pass3" } };

        return usernamePassArray;
    }
    return null;
}

I have just trying to show how you can get the Test context in data provider which in turn can let you get the test method name. This test method name can be used as a key to extract data from Excel corresponding to this test.