How to use single data provider to multiple testNG methods in single class

[Sai7574]

package com.Automation.Utils;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.HashMap;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

public class Excelutil2 {
public Workbook book;
public Sheet sheet;

public Excelutil2(String filePath, String sheetName) {
try {
FileInputStream file = new FileInputStream(new File(filePath));
book = WorkbookFactory.create(file);
sheet = book.getSheet(sheetName);
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} catch (InvalidFormatException e) {
e.printStackTrace();
}
}

public Object[][] getTestData() {
int rowCount = getRowCount();
int columnCount = getCellCount();

Object[][] data = new Object[rowCount][columnCount];

for (int i = 0; i < rowCount; i++) {
	Row row = sheet.getRow(i + 1);
	for (int j = 0; j < columnCount; j++) {
		Cell cell = row.getCell(j);
		cell.setCellType(Cell.CELL_TYPE_STRING);
		data[i][j] = cell.getStringCellValue().trim();
	}
}

return data;

}

public HashMap<String, String> getTestDataInMap(int rowNum) {
HashMap<String, String> testDataMap = new HashMap<>();
Row headerRow = sheet.getRow(0);
Row dataRow = sheet.getRow(rowNum);

if (dataRow != null) { // Add null check for data row
	for (int i = 0; i < headerRow.getLastCellNum(); i++) {
		Cell headerCell = headerRow.getCell(i);
		Cell dataCell = dataRow.getCell(i);

		if (headerCell != null && dataCell != null) { // Add null checks for header and data cells
			headerCell.setCellType(Cell.CELL_TYPE_STRING);
			dataCell.setCellType(Cell.CELL_TYPE_STRING);

			String key = headerCell.getStringCellValue().trim();
			String value = dataCell.getStringCellValue().trim();

			testDataMap.put(key, value);
		}
	}
}
return testDataMap;

}

public int getRowCount() {
return sheet.getLastRowNum();
}

public int getCellCount() {
Row headerRow = sheet.getRow(0);
return headerRow.getLastCellNum();
}
}
/////////////////////////
String ExcelPath = "./TestData/CheckListExecutionData.xlsx";
String[] sheets = { "ChecklistExecutionData", "Sheet1", "Sheet2" };

@dataProvider(name = "ChecklistExecutionData")
public Object[][] testDataSupplier() throws Exception {
List<Object[]> testDataList = new ArrayList<>();
for (String sheet : sheets) {
Excelutil2 excel = new Excelutil2(ExcelPath, sheet);
for (int i = 1; i <= excel.getRowCount(); i++) {
HashMap<String, String> testData = excel.getTestDataInMap(i);
testDataList.add(new Object[] { sheet, testData });
}
}
return testDataList.toArray(new Object[0][]);
}
with this dataprovider how to use different sheets for different @test methods this is which u have created i have modified few things how to use single dataprovider for multiple testNG methods in class

[Sai7574]

plz help out thanks

[avighub]

Hi @Sai7574 , Trying to understand your usecase.
What kind of data do you want to keep in excel ?

1. Is it some domain specific data related to project?
2. Or is it some random data such as username, phone, email, random strings and so on?

Your answer will depend on the solution

[Sai7574]

Yes, some project-related domain-specific data Thank you for replying.

[Sai7574]

However, that requires me to use three sheets, so I will need the first sheet for the first method and the second sheet for the second method, etc.

[avighub]

I strongly recommend not to use Excel and instead use separate CSV files for each test, here is why

1. Excel read and write is a heavy IO operation and tend to fail, due to file corruption
2. Excel is not great with Git versioning, where as CSV is just like text file (Comma separate while it still gives flexibility like excel, but only one Sheet)
3. CSV read and write is faster compared to excel
4. Also JSON is even better but if your test data has more columns, CSV is better else go with JSON file, super lightweight

public class CsvDataReaderTest {

    @DataProvider(name = "testdata")
    public Object[][] testData() {
        return readTestDataFromCsv("testdata.csv");
    }

    @Test(dataProvider = "testdata")
    public void testWithData(String username, String password) {
        // Your test logic here
        System.out.println("Username: " + username + ", Password: " + password);
    }

    private Object[][] readTestDataFromCsv(String csvFile) {
        List<Object[]> testData = new ArrayList<>();
        String line;
        String csvSplitBy = ",";

        try (BufferedReader br = new BufferedReader(new FileReader(csvFile))) {
            while ((line = br.readLine()) != null) {
                String[] data = line.split(csvSplitBy);
                testData.add(data);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        
        // Convert List<Object[]> to Object[][]
        Object[][] testDataArray = new Object[testData.size()][];
        for (int i = 0; i < testData.size(); i++) {
            testDataArray[i] = testData.get(i);
        }
        return testDataArray;
    }
}

[Sai7574]

Since Excel is used in my organization instead of JSON, I had to create an Excel util class in order to comply. However, I only need to use one data provider for multiple pull testNG methods, which is exactly what I want.Could you please help me out here? Thank you for responding.

[avighub]

@Sai7574 I understand, but you can always suggest changes to existing approach. In engineering you don't have to follow what others say/do, if you have better solution show them and change the code accordingly. Otherwise what is the point of technology evolution?
If you still want to stick to your own complex solution, you could use Zerocell library which is way better than having so much of boiler plate code from Apache POI.
Try this
https://www.youtube.com/watch?v=Lemk4KwLmos&ab_channel=TestingMiniBytes

[Sai7574]

Since I also need to pass parameters to page class methods and test methods, I am actually using hashmaps to handle my large amount of test data. I am using hashmap in the code above, which you have again provided, because it is incredibly inefficient. Does the method require me to pass strings?

[Sai7574]

Is it possible to use the code above and read the sheet for a particular testNG method without changing it? Exists it at all? Please, could you give me the code? My code is running, but there is a loop that will not end. I am not sure why, but it keeps going after a pause and executes three sheets for a single testNG method. Would you please try to change that code?