时间戳

首页 > 解决方案 > 从excel中读取测试数据

java - 从excel中读取测试数据

问题描述

我正在使用保存在属性文件中的 POM 框架和测试数据（此代码可以正常工作），但根据当前要求，我应该将测试数据保存在 excel 文件中。根据我的代码，数据正在从 excel 中读取，但值没有发送到 chrome（我通过在控制台中打印值进行了交叉检查），当我进行调试时，我知道 Data 正在返回一个空值。问题在于 p.load(fs1); 行，因为数据未加载。

// below code is for properties file and its working without any issue.
/*    public static String readTestData(String key) throws IOException {
            String filename = "testData";
            String path = System.getProperty("user.dir") + "/data/testData.properties";
            if (path == null || path.length() == 0) {
                path = System.getProperty("user.dir") + "/data/" + filename + ".properties";
            }
            Properties p = new Properties();
            FileInputStream fs = new FileInputStream(path);
             System.out.print("File Input Stream value is "+fs);
            p.load(fs);
            System.out.println("Value of login username is "+(String)p.get(key));
            return (String) p.get(key);
        }*/




// Below code is for reading test data from xlsx

public static String readTestData(String key) throws IOException {
            String filename = "testData";
            String path = System.getProperty("user.dir") + "/data/testData.xlsx";
           if (path == null || path.length() == 0) {
                path = System.getProperty("user.dir") + "/data/" + filename + ".xlsx";
            }
            Properties p = new Properties();
            FileInputStream fs = new FileInputStream(path);
            Workbook SapWorkbook = null;
            StringBuffer sbf = new StringBuffer();
            SapWorkbook = new XSSFWorkbook(fs);
            Sheet SapSheet = SapWorkbook.getSheet("Sheet1");
            int rowCount = SapSheet.getLastRowNum()-SapSheet.getFirstRowNum();
            for (int i = 0; i < rowCount+1; i++) {

                Row row = SapSheet.getRow(i);

                //Create a loop to print cell values in a row

                for (int j = 0; j < row.getLastCellNum(); j++) {
                    //Print Excel data in console
                    sbf.append(row.getCell(j).getStringCellValue());
                    System.out.print(row.getCell(j).getStringCellValue()+"|| ");

                }

                System.out.println();
            } 
            byte[] bytes = sbf.toString().getBytes();
            ByteArrayInputStream fs1 = new ByteArrayInputStream(bytes);
            p.load(fs1);
            System.out.println("Value of login username is "+(String)p.get(key));
           return (String) p.get(key);

        }   

 public static void enterText(String key, String data) throws IOException, InterruptedException {
            try {
                waitForPresenceAndVisibilityOfElementry(readobjectRepo(key));
                WebElement ele = driver.findElement(By.xpath(readobjectRepo(key)));
                ele.clear();
                Thread.sleep(1200);
                System.out.println("about to read Base page");
                ele.sendKeys(readTestData(data));
                System.out.println("data read");
                startExtent.log(LogStatus.PASS, "Entering  data.. " + readTestData(data) + " is sucessful");
                Thread.sleep(1200);
            } catch (Exception e) {
                e.printStackTrace();
                reportFailure("Click on element is unsucessful");
            }
        }

In the console result.

loginUserName|| ABCD@gmail.com|| 
loginPassword|| abc@1a|| 

Value of login username is null

标签： javaselenium-webdriverapache-poi

解决方案

你的代码看起来很乱，可以做一些清理工作。这个块是没有意义的：

String filename = "testData";
String path = System.getProperty("user.dir") + "/data/testData.xlsx";
if (path == null || path.length() == 0) {
    path = System.getProperty("user.dir") + "/data/" + filename + ".xlsx";
}

您只是以不同的方式对同一事物进行硬编码，坚持

String path = System.getProperty("user.dir") + "/data/testData.xlsx";

很不清楚您在这里尝试做什么，但我猜您想要第二列中的值与第一列中的键相关联。所有创建属性对象的东西似乎都已经过时了，所以我将您的代码重写为：

Workbook SapWorkbook = new XSSFWorkbook(fs);
Sheet SapSheet = SapWorkbook.getSheet("Sheet1");
int rowCount = SapSheet.getLastRowNum() - SapSheet.getFirstRowNum();
for (int i = 0; i < rowCount + 1; i++) {
    Row row = SapSheet.getRow(i);
    if (key.equals(row.getCell(0).getStringCellValue())) {
        return row.getCell(1).getStringCellValue();
    }
}

throw new IllegalArgumentException(String.format("%s not found!", key));

现在，这将滚动搜索您的“键”的第一个实例的每一行，然后返回相关的“值”。如果它没有找到密钥，它将引发异常。要将其全部打包为一个方法：

public static String readTestData(String key) throws IllegalArgumentException, IOException {
    String path = System.getProperty("user.dir") + "/data/testData.xlsx";
    FileInputStream fs = new FileInputStream(path);
    Workbook SapWorkbook = new XSSFWorkbook(fs);
    Sheet SapSheet = SapWorkbook.getSheet("Sheet1");
    int rowCount = SapSheet.getLastRowNum() - SapSheet.getFirstRowNum();
    for (int i = 0; i < rowCount + 1; i++) {
        Row row = SapSheet.getRow(i);
        if (key.equals(row.getCell(0).getStringCellValue())) {
            return row.getCell(1).getStringCellValue();
        }
    }

    throw new IllegalArgumentException(String.format("%s not found!", key));
}

推荐阅读