java - 从同一工作簿 Selenium 的多个工作表中读取数据

Question

请帮助我使用 Selenium 和 Java 代码从同一个 Excel 文件的多个工作表中读取数据。请在下面找到我尝试过的代码。它总是从最后一个工作表中读取数据。但我需要读取sheet 0到sheet n的数据。

    public class HybridExecuteTest {
    WebDriver webdriver = null;
    @Test(dataProvider="hybridData")
    public void testLogin(String testcaseName,String keyword,String    objectName,String objectType,String value) 
        throws Exception {
            if(testcaseName!=null&&testcaseName.length()!=0){
    webdriver=new FirefoxDriver();
    webdriver.manage().window().maximize();
            }
    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);
            }
    @SuppressWarnings("null")
    @DataProvider(name="hybridData")
    public Object[][] get() throws IOException
    {
    Object[][] object = null; 
    String filePath = System.getProperty("user.dir")+"\\";
    System.out.println("File Path is:" + filePath);
    String fileName = "TestCaseRep.xls";
    //Create a object of File class to open xlsx file
    File file = new File(filePath+"\\"+fileName);
    //Create an object of FileInputStream class to read excel file
    FileInputStream inputStream = new FileInputStream(file);
    int totalSheet = 0;
    Sheet  Sheet1=null;
    Workbook Workbookone = null;
    //Find the file extension by spliting file name in substing and getting only extension name
    String fileExtensionName = fileName.substring(fileName.indexOf("."));
    //Check condition if the file is xlsx file
    if(fileExtensionName.equals(".xlsx")){
    //If it is xlsx file then create object of XSSFWorkbook class
        Workbookone  = new XSSFWorkbook(inputStream);
    }
    //Check condition if the file is xls file
    else if(fileExtensionName.equals(".xls")){
        //If it is xls file then create object of XSSFWorkbook class
        Workbookone  = new HSSFWorkbook(inputStream);
    }
    //Read sheet inside the workbook by its name
    totalSheet = Workbookone.getNumberOfSheets();
    if(totalSheet > 0) {
        System.out.println("Total Sheet Found:" + totalSheet);
        for(int k=0;k<totalSheet ;k++) {
            System.out.println("Sheet Name:" + Workbookone.getSheetName(k));
            Sheet1 = Workbookone.getSheetAt(k);
            System.out.println("Sheet Found:" + Sheet1);
         //Find number of rows in excel file
    int rowCount = Sheet1.getLastRowNum()-Sheet1.getFirstRowNum();
    object = new Object[rowCount][5];
    for (int i = 0; i < rowCount; i++) {
        //Loop over all the rows
        Row row = Sheet1.getRow(i+1);
        //Create a loop to print cell values in a row
        for (int j = 0; j < row.getLastCellNum(); j++) {
            //Print excel data in console
            object[i][j] = row.getCell(j).toString();   
            System.out.println(object[i][j]);
        }
    }
    System.out.println("");
}
 }
    return object;
    }
}

Answer 1

问题是您正在重新初始化 object for 循环中的变量这就是您获取最后一张纸的值的原因。 由于它是一个二维数组，您只能存储行和列两个值。

如果你也想存储sheet，你必须使用三维数组。但我建议你使用List。所以我修改了返回类型 Object[][]至ArrayList<Object[][]>以便将工作表数据添加到列表中

public ArrayList<Object[][]> get() throws IOException {
        ArrayList<Object[][]> sheetDatas=new ArrayList<>();
        Object[][] object = null;
        String filePath = System.getProperty("user.dir") + "\\";
        System.out.println("File Path is:" + filePath);
        String fileName = "TestCaseRep.xls";
        //Create a object of File class to open xlsx file
        File file = new File(filePath + "\\" + fileName);
        //Create an object of FileInputStream class to read excel file
        FileInputStream inputStream = new FileInputStream(file);
        int totalSheet = 0;
        Sheet Sheet1 = null;
        Workbook Workbookone = null;
        //Find the file extension by spliting file name in substing and getting only extension name
        String fileExtensionName = fileName.substring(fileName.indexOf("."));
        //Check condition if the file is xlsx file
        if (fileExtensionName.equals(".xlsx")) {
            //If it is xlsx file then create object of XSSFWorkbook class
            Workbookone = new XSSFWorkbook(inputStream);
        } //Check condition if the file is xls file
        else if (fileExtensionName.equals(".xls")) {
            //If it is xls file then create object of XSSFWorkbook class
            Workbookone = new HSSFWorkbook(inputStream);
        }
        //Read sheet inside the workbook by its name
        totalSheet = Workbookone.getNumberOfSheets();
        if (totalSheet > 0) {
            System.out.println("Total Sheet Found:" + totalSheet);
            for (int k = 0; k < totalSheet; k++) {
                System.out.println("Sheet Name:" + Workbookone.getSheetName(k));
                Sheet1 = Workbookone.getSheetAt(k);
                System.out.println("Sheet Found:" + Sheet1);
                //Find number of rows in excel file
                int rowCount = Sheet1.getLastRowNum() - Sheet1.getFirstRowNum();
                /**
                 * 
                 * You are reinitializing the object here so old datas will be lost
                 */
                object = new Object[rowCount][5];
                for (int i = 0; i < rowCount; i++) {
                    //Loop over all the rows
                    Row row = Sheet1.getRow(i + 1);
                    //Create a loop to print cell values in a row
                    for (int j = 0; j < row.getLastCellNum(); j++) {
                        //Print excel data in console
                        object[i][j] = row.getCell(j).toString();
                        System.out.println(object[i][j]);
                    }
                }
                System.out.println("");
                sheetDatas.add(object);
            }
        }
        return sheetDatas;
    }
}

例如，如果您想从工作表 2 中获取值
你可以像list.get(1)这样调用