TestNG runner when using @DataProvider combined with apache poi does not execute tests sequentially

Question

EDIT: I have an ExcelUtility.java class to get cell data from it and pass it on to the tests methods in my test class.

I am reading from 1 excel file. The excel file has 3 worksheets. Every worksheet has 1 unique working table inside. I have 1 Test class. The test class has 3 test methods. The test class contains 3 dataProviders. All dataProviders differ in the worksheet name and working table name.

The tests in the test class are written in the following manner:

  @Test(priority = 0, dataProvider = "dp1")
    public void test01(String...strings){
 }
 @Test(priority = 1, dataProvider = "dp2")
    public void test02(String...strings){
 } 
 @Test(priority = 2, dataProvider = "dp3")
    public void test03(String...strings){
}

I have the following java class to read from XLSX file using apache poi jars:

public class ExcelUtility {

        private static XSSFWorkbook ExcelWBook;
        private static XSSFSheet ExcelWSheet;

        /*
         * Set the File path, open Excel file
         * @params - Excel Path and Sheet Name
         */
        public static void setExcelFile(String path, String sheetName) throws Exception {
            try {
                // Open the Excel file
                FileInputStream ExcelFile = new FileInputStream(path);

                // Access the excel data sheet
                ExcelWBook = new XSSFWorkbook(ExcelFile);
                ExcelWSheet = ExcelWBook.getSheet(sheetName);
            } catch (Exception e) {
                throw (e);
            }
        }

        public static String[][] getTestData(String tableName) {
            String[][] testData = null;

            try {
                // Handle numbers and strings
                DataFormatter formatter = new DataFormatter();
                XSSFCell[] boundaryCells = findCells(tableName);
                XSSFCell startCell = boundaryCells[0];

                XSSFCell endCell = boundaryCells[1];

                int startRow = startCell.getRowIndex() + 1;
                int endRow = endCell.getRowIndex() - 1;
                int startCol = startCell.getColumnIndex() + 1;
                int endCol = endCell.getColumnIndex() - 1;

                testData = new String[endRow - startRow + 1][endCol - startCol + 1];

                for (int i=startRow; i<endRow+1; i++) {
                    for (int j=startCol; j<endCol+1; j++) {
                        // testData[i-startRow][j-startCol] = ExcelWSheet.getRow(i).getCell(j).getStringCellValue();
                        Cell cell = ExcelWSheet.getRow(i).getCell(j);
                        testData[i - startRow][j - startCol] = formatter.formatCellValue(cell);
                    }
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
            return testData;
        }

        public static XSSFCell[] findCells(String tableName) {
            DataFormatter formatter = new DataFormatter();
            String pos = "begin";
            XSSFCell[] cells = new XSSFCell[2];

            for (Row row : ExcelWSheet) {
                for (Cell cell : row) {
                    // if (tableName.equals(cell.getStringCellValue())) {
                    if (tableName.equals(formatter.formatCellValue(cell))) {
                        if (pos.equalsIgnoreCase("begin")) {
                            cells[0] = (XSSFCell) cell;
                            pos = "end";
                        } else {
                            cells[1] = (XSSFCell) cell;
                        }
                    }
                }
            }
            return cells;
        }
    }

In order to read from the excel file I have organized the test methods in the following manner:

@DataProvider(name = "dp1")
    public Object[][] dp1() throws IOException {
        Object[][] testData = new Object[0][];
        try {
            ExcelUtility.setExcelFile(Constants.File_Path+Constants.File_Name, "Page1");
            testData = ExcelUtility.getTestData("P1");
        } catch (Exception e) {
            e.printStackTrace();
        }
        return testData;
    }
    @DataProvider(name = "dp2")
    public Object[][] dp2() throws IOException {
        Object[][] testData = new Object[0][];
        try {
            ExcelUtility.setExcelFile(Constants.File_Path+Constants.File_Name, "Page2");
            testData = ExcelUtility.getTestData("P2");
        } catch (Exception e) {
            e.printStackTrace();
        }
        return testData;
    }
    @DataProvider(name = "dp3")
    public Object[][] dp3() throws IOException {
        Object[][] testData = new Object[0][];
        try {
            ExcelUtility.setExcelFile(Constants.File_Path+Constants.File_Name, "Page3");
           testData = ExcelUtility.getTestData("P3");
        } catch (Exception e) {
            e.printStackTrace();
        }
        return testData;
    }
    @Test(priority = 0, dataProvider = "dp1")
    public void test01(String...strings){
 //read data from excel and pass the value to the strings added as arguments in the method above
 }

 @Test(priority = 1, dataProvider = "dp2")
    public void test02(String...strings){

 }

 @Test(priority = 2, dataProvider = "dp3")
    public void test03(String...strings){

 }

What I would like to do is the following:

1. Read first row data from sheet1, pass it to test1, continue to test2

2. Read first row data from sheet2, pass it to test2, continue to test3

3. Read first row data from sheet3, pass it to test3, continue to test1

4. Read second row data from sheet 1, pass it to test1, continue to test2

And so on, depending of number of rows in the excel sheets.

What happens is:

The first test is executed, reads worksheet 1, row 1.
The first test is executed, reads worksheet 1, row 2.
The second test is executed, reads worksheet 2, row 1.
The second test is executed, reads worksheet 2, row 2.

All tests fail because they are dependable from each other, that is why I set execution priority.

Should I change something in the Test class, or something in the ExcelUtility.java class should be changed?

Thank you in advance!

Answer 1

TestNG allows you to specify ordering of tests with several approaches. However, you are doing something that does not align with unit testing methodology.

You should re-write your tests to perform a series of checks on the test data. What you call test1 and test2 are actually assertion1 and assertion2.

@Test(dataProvider = "dp1")
public void newTest1(String dp1) throws Exception {
  assertion1(dp1);
  assertion2(dp1);
}

@Test(dataProvider = "dp2")
public void newTest2(String dp2) throws Exception {
  assertion2(dp2);
  assertion3(dp2);
}

With this approach, the tests can be executed in any order. While you thought of the data being handled in a pipeline, that is not how unit testing frameworks operate.

You are also not constrained to have only a single assertion in a unit test. If there are religious zealots reviewing your code, then I would suggest composing those assertion methods into assertion1and2, etc, so that the test itself appears to make only one assertion.