TestNG多DataProvider向含多测试方法的测试类传参及Excel读取问题

Got it, let's walk through how to solve this exactly as you described. You've got an ExcelUtility.java class to read data from a 3-sheet Excel file, and you want three DataProviders in a single TestNG test class—each feeding a unique test method with data from its corresponding worksheet. Here's a complete, actionable solution:

Step 1: Refine Your ExcelUtility for Reusable Sheet Data Fetching #

First, make sure your ExcelUtility has a method that can fetch data from a specific worksheet and return it in the 2D array format TestNG DataProviders require. If you haven't already, add this core logic (key parts included here):

import org.apache.poi.ss.usermodel.*;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

public class ExcelUtility {
    // Helper to get the workbook instance
    private static Workbook getWorkbook(String filePath) throws IOException {
        try (FileInputStream fis = new FileInputStream(filePath)) {
            return WorkbookFactory.create(fis);
        }
    }

    // Fetch data from a specific sheet, return as 2D array for TestNG
    public static Object[][] getTestDataFromSheet(String filePath, String sheetName) {
        List<Object[]> testData = new ArrayList<>();
        
        try (Workbook workbook = getWorkbook(filePath)) {
            Sheet sheet = workbook.getSheet(sheetName);
            if (sheet == null) {
                throw new IllegalArgumentException("Sheet '" + sheetName + "' not found in Excel file!");
            }

            // Skip header row (adjust rowNum start to 0 if no header)
            for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
                Row row = sheet.getRow(rowNum);
                if (row == null) continue;

                Object[] rowData = new Object[row.getLastCellNum()];
                for (int colNum = 0; colNum < row.getLastCellNum(); colNum++) {
                    rowData[colNum] = getCellValue(row.getCell(colNum));
                }
                testData.add(rowData);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return testData.toArray(new Object[0][]);
    }

    // Handle different cell types to get the correct value
    private static Object getCellValue(Cell cell) {
        if (cell == null) return "";
        switch (cell.getCellType()) {
            case STRING: return cell.getStringCellValue();
            case NUMERIC: 
                return DateUtil.isCellDateFormatted(cell) ? cell.getDateCellValue() : cell.getNumericCellValue();
            case BOOLEAN: return cell.getBooleanCellValue();
            default: return "";
        }
    }
}

Step 2: Build Your Test Class with Multiple DataProviders #

Now create your test class with three DataProviders (one per worksheet) and three corresponding test methods. Each DataProvider will call ExcelUtility with the specific sheet name, and each test method binds to its DataProvider via the dataProvider attribute:

import org.testng.annotations.DataProvider;
import org.testng.annotations.Test;

public class MultiSheetTestSuite {
    // Replace with your actual Excel file path
    private static final String EXCEL_PATH = "src/test/resources/test_data.xlsx";

    // DataProvider 1: For "LoginSheet" worksheet
    @DataProvider(name = "loginTestData")
    public Object[][] loginData() {
        return ExcelUtility.getTestDataFromSheet(EXCEL_PATH, "LoginSheet");
    }

    // DataProvider 2: For "CheckoutSheet" worksheet
    @DataProvider(name = "checkoutTestData")
    public Object[][] checkoutData() {
        return ExcelUtility.getTestDataFromSheet(EXCEL_PATH, "CheckoutSheet");
    }

    // DataProvider 3: For "ProfileSheet" worksheet
    @DataProvider(name = "profileTestData")
    public Object[][] profileData() {
        return ExcelUtility.getTestDataFromSheet(EXCEL_PATH, "ProfileSheet");
    }

    // Test Method 1: Binds to loginTestData DataProvider
    @Test(priority = 0, dataProvider = "loginTestData")
    public void testUserLogin(String username, String password, String expectedStatus) {
        // Your login test logic here
        System.out.printf("Testing Login: User='%s', Pass='%s', Expected='%s'%n", username, password, expectedStatus);
        // Add assertions like Assert.assertEquals(actualLoginStatus, expectedStatus);
    }

    // Test Method 2: Binds to checkoutTestData DataProvider
    @Test(priority = 1, dataProvider = "checkoutTestData")
    public void testCartCheckout(String productId, String quantity, String expectedTotal) {
        // Your checkout test logic here
        System.out.printf("Testing Checkout: Product='%s', Qty='%s', Expected Total='%s'%n", productId, quantity, expectedTotal);
    }

    // Test Method 3: Binds to profileTestData DataProvider
    @Test(priority = 2, dataProvider = "profileTestData")
    public void testProfileUpdate(String firstName, String lastName, String email) {
        // Your profile update test logic here
        System.out.printf("Testing Profile Update: Name='%s %s', Email='%s'%n", firstName, lastName, email);
    }
}

Key Things to Keep in Mind #

• Unique DataProvider Names: Each @DataProvider must have a distinct name so TestNG can map the right data to the right test method.
• Excel Path Consistency: Use a relative path for your Excel file (like src/test/resources/) to make the project portable across environments.
• Header Handling: The example skips the first row (assuming it's a header). If your Excel has no header, change rowNum = 1 to rowNum = 0 in the ExcelUtility.
• Type Safety: The getCellValue method handles different cell types (strings, numbers, dates) to ensure your test methods receive the correct data type.

Extra Tips #

• If you need to reuse the same DataProvider across multiple test classes, move the DataProviders to a separate utility class and use dataProviderClass in the @Test annotation (e.g., dataProviderClass = DataProviders.class).
• Add logging (instead of System.out.println) to make debugging easier when tests fail.
• Validate that your Excel sheets have consistent column counts for each row—otherwise, you might get array index exceptions.

内容的提问来源于stack exchange，提问作者me2me2you2me