java - 如何使用 Apache POI 和 TestNG dataProvider 编写 Excel

Question

我正在使用混合框架，在这些框架中，我使用数据提供者的 Apache-poi 库来编写 Excel 工作表。

我希望我的代码能够以这种方式读取和写入我的 Excel 工作表，其中已编写测试用例，并根据该情况设置其状态。

当前，当我执行代码时，它跳过了登录方法。其实我是初学者，尝试用它来读写Excel，有人可以帮我解决这个问题吗？

public class HybridExecuteTest {
    private static final String BROWSER_PATH = "D:\\abc\\setup\\FFinstalled\\firefox.exe";
    private static XSSFWorkbook ExcelWBook;
    private static XSSFSheet ExcelWSheet;
    private static XSSFCell Cell;
    private static XSSFRow Row;
    WebDriver webdriver = null;

    @Test(dataProvider = "hybridData")
    public void testLogin(String testcaseName, String keyword,
            String objectName, String objectType, String value)
            throws Exception {
        // TODO Auto-generated method stub
        if (testcaseName != null && testcaseName.length() != 0) {
            // webdriver=new FirefoxDriver();
            File file = new File(BROWSER_PATH);
            FirefoxBinary fb = new FirefoxBinary(file);
            webdriver = new FirefoxDriver(fb, new FirefoxProfile());
        }
        ReadObject object = new ReadObject();
        Properties allObjects = object.getObjectRepository();
        UIOperation operation = new UIOperation(webdriver);
        // Call perform function to perform operation on UI
        operation.perform(allObjects, keyword, objectName, objectType, value);
    }

    @DataProvider(name = "hybridData")

    // This method is to set the File path and to open the Excel file, Pass Excel Path and Sheetname as Arguments to this method
        public Object[][] setExcelFile(String filePath, String fileName, String sheetName) throws Exception {
        Object object[][] = null;
        try {
            File file = new File(filePath + "\\" + fileName);
            // Open the Excel file
            FileInputStream ExcelFile = new FileInputStream(file);
            // Access the required test data sheet
            ExcelWBook = new XSSFWorkbook(ExcelFile);
            ExcelWSheet = ExcelWBook.getSheet(sheetName);
        } catch (Exception e) {throw (e);}
        return object;
    }
    // This method is to read the test data from the Excel cell, in this we are passing parameters as Row num and Col num
    public String getCellData(int RowNum, int ColNum) throws Exception {
        try {
            Cell = ExcelWSheet.getRow(RowNum).getCell(ColNum);
            String CellData = Cell.getStringCellValue();
            return CellData;
        } catch (Exception e) {
            return "";
        }
    }
    // This method is to write in the Excel cell, Row num and Col num are the parameters
    public String setCellData(String Result, int RowNum, int ColNum,String filePath, String fileName) throws Exception {
        try {
            Row = ExcelWSheet.getRow(RowNum);
            Cell = Row.getCell(ColNum, Row.RETURN_BLANK_AS_NULL);
            if (Cell == null) {
                Cell = Row.createCell(ColNum);
                Cell.setCellValue(Result);
            } else {
                Cell.setCellValue(Result);
            }
            // Constant variables Test Data path and Test Data file name
            File file = new File(filePath + "\\" + fileName);
            // Open the Excel file
            FileOutputStream ExcelFile = new FileOutputStream(file);
            ExcelWBook.write(ExcelFile);
        } catch (Exception e) {
            throw (e);
        }
        return null;
    }
}

控制台:

SKIPPED: testLogin
org.testng.TestNGException: 
Some DataProvider public java.lang.Object[][] testCases.HybridExecuteTest.setExcelFile(java.lang.String,java.lang.String,java.lang.String) throws java.lang.Exception parameters unresolved:  at 1 type class java.lang.String
 at 2 type class java.lang.String
 at 3 type class java.lang.String

注意:我已经完成了 Apache poi 的教程，通常我理解如何编写，但在框架中我被卡住了。请在这些方面提供帮助。

Answer 1

您尝试将参数传递给 dataprovider，但这是不受支持的。将 String filePath、String fileName、String SheetName 声明为类级别变量，然后从方法访问它们。

String filePath="something"; String fileName="something"; String sheetName ="something";

 @DataProvider(name = "hybridData")
public Object[][] setExcelFile() throws Exception {
        Object object[][] = null;
        try {
            File file = new File(filePath + "\\" + fileName);
            // Open the Excel file
            FileInputStream ExcelFile = new FileInputStream(file);
            // Access the required test data sheet
            ExcelWBook = new XSSFWorkbook(ExcelFile);
            ExcelWSheet = ExcelWBook.getSheet(sheetName);
        } catch (Exception e) {throw (e);}
        return object;
    }

您将面临的另一个问题是，在返回 object[][] 之前，您没有为其分配任何内容。