如何在seleniumwebdriver中使用JXL来检查工作簿表是否存在？

Question

我想检查工作簿中是否包含工作表名称“ Login”。如果存在，那么我要在其中写入数据row = row +
1，否则直接写入数据，然后关闭报告。所以，任何人都可以让我知道该怎么做。我想用下面的方法做。

主类：

public class MainClass {
    private static final String BROWSER_PATH = "D:\\softs\\FF installed\\FF18\\firefox.exe";
    private static final String TEST_SUITE_PATH = "D:\\softs\\configuration\\GmailTestSuite.xls";
    private static final String TEST_RESULT = "D:\\softs\\configuration\\TestResult.xls";
    private static final String OBJECT_REPOSITORY_PATH = "D:\\softs\\configuration\\objectrepository.xls";
    private static final String ADDRESS_TO_TEST = "https://www.gmail.com";

    // other constants

    private WebDriver driver;
    private Properties properties;

    /* private WebElement we; */

    public MainClass() {
        File file = new File(BROWSER_PATH);
        FirefoxBinary fb = new FirefoxBinary(file);
        driver = new FirefoxDriver(fb, new FirefoxProfile());
        driver.get(ADDRESS_TO_TEST);
    }

    public static void main(String[] args) throws Exception {
        MainClass main = new MainClass();

        main.handleTestSuite();
    }

    private void handleTestSuite() throws Exception {
        ReadPropertyFile readConfigFile = new ReadPropertyFile();
        properties = readConfigFile.loadPropertiess();

        ExcelHandler testSuite = new ExcelHandler(TEST_SUITE_PATH, "Suite");
        testSuite.columnData();

        int rowCount = testSuite.rowCount();
        System.out.println("Total Rows=" + rowCount);

        for (int i = 1; i < rowCount; i++) {
            String executable = testSuite.readCell(
                    testSuite.getCell("Executable"), i);
            System.out.println("Executable=" + executable);

            if (executable.equalsIgnoreCase("y")) {
                // exe. the process
                String scenarioName = testSuite.readCell(
                        testSuite.getCell("TestScenario"), i);
                System.out.println("Scenario Name=" + scenarioName);
                handleScenario(scenarioName);
            }

        }

    }

    private void handleScenario(String scenarioName) throws Exception {
        ExcelHandler testScenarios = new ExcelHandler(TEST_SUITE_PATH);
        testScenarios.setSheetName(scenarioName);
        testScenarios.columnData();
        int rowWorkBook1 = testScenarios.rowCount();
        for (int j = 1; j < rowWorkBook1; j++) {
            String sno = testScenarios.readCell(
                    testScenarios.getCell("Sno"), j); // SendKey
            String testCaseDescription = testScenarios.readCell(
                    testScenarios.getCell("TestCaseDescription"), j);

            String framWork = testScenarios.readCell(
                    testScenarios.getCell("FrameworkName"), j);
            String operation = testScenarios.readCell(
                    testScenarios.getCell("Operation"), j); // SendKey
            String value = testScenarios.readCell(
                    testScenarios.getCell("Value"), j);
            System.out.println("FRMNameKK=" + framWork + ",Operation="
                    + operation + ",Value=" + value);

//          handleObjects(operation, value, framWork);
            boolean bTestCaseStepStatus = handleObjects(operation, value, framWork);

            generateReport(bTestCaseStepStatus,scenarioName,sno,testCaseDescription);

            //Report
        }
    }

    private void generateReport(boolean bTestCaseStepStatus, String testSuiteName, String SNO, String testCaseDescription) 
    throws RowsExceededException, WriteException, IOException
    {
        //String testReportPath = "D://Chayan//TestExecution.xls";
        WritableData writableData= new WritableData(TEST_RESULT, testSuiteName);

        //If sheet name is already exist then .... write data at row = row +1 
        //else write directly .. means row = 1


        System.out.println("WRITABLEDATA>>><<<<>>><<>>"+writableData);
            writableData.shSheet("Login", 5, 1, "Pass");
            driver.manage().timeouts().implicitlyWait(5, TimeUnit.SECONDS);
            //writableData.shSheet("Login", 5, 2, "Fail");
            //driver.manage().timeouts().implicitlyWait(5, TimeUnit.SECONDS);
           // writableData.shSheet("Login", 5, 3, "N/A");
            driver.manage().timeouts().implicitlyWait(5, TimeUnit.SECONDS);


        //Finally .... close the Report file ...    
    }

    private boolean handleObjects(String operation, String value, String framWork)
            throws Exception {
        //Return true / False
        System.out.println("HandleObject--> " + framWork);
        ExcelHandler objectRepository = new ExcelHandler(
                OBJECT_REPOSITORY_PATH, "OR");
        objectRepository.columnData();
        int rowCount = objectRepository.rowCount();
        System.out.println("Total Rows in hadleObject=" + rowCount);
        boolean testStepStatus = false;

        for (int k = 1; k < rowCount; k++) {
            String frameWorkName = objectRepository.readCell(
                    objectRepository.getCell("FrameworkName"), k);
            String ObjectName = objectRepository.readCell(
                    objectRepository.getCell("ObjectName"), k);
            String Locator = objectRepository.readCell(
                    objectRepository.getCell("Locator"), k); // SendKey

            System.out.println("FrameWorkNameV=" + frameWorkName
                    + ",ObjectName=" + ObjectName + ",Locator=" + Locator);

            if (framWork.equalsIgnoreCase(frameWorkName)) {
                testStepStatus = operateWebDriver(operation, Locator, value, ObjectName);

            }
        }

        return testStepStatus;
    }

    private boolean operateWebDriver(String operation, String Locator,
            String value, String objectName) throws Exception 
    {
        boolean testCaseStep = false;

        try {
            System.out.println("Operation execution in progress");
            WebElement temp = getElement(Locator, objectName);
            if (operation.equalsIgnoreCase("SendKey")) {
                temp.sendKeys(value);
            }
            Thread.sleep(1000);
            driver.manage().timeouts().implicitlyWait(10, TimeUnit.SECONDS);
            if (operation.equalsIgnoreCase("Click")) {
                temp.click();
            }
            if (operation.equalsIgnoreCase("Verify")) {
                System.out.println("Verify--->" + temp);
                temp.isDisplayed();

            }

            testCaseStep = true;
        } catch (Exception e) {
            System.out.println("Exception occurred operateWebDriver"+ e.getMessage());
        }

        return testCaseStep;
    }

    public WebElement getElement(String locator, String objectName)
            throws Exception {
        WebElement temp = null;
        System.out.println("Locator-->" + locator);
        if (locator.equalsIgnoreCase("id")) {
            temp = driver.findElement(By.id(objectName));

        } else if (locator.equalsIgnoreCase("xpath")) {
            temp = driver.findElement(By.xpath(objectName));
            System.out.println("xpath temp ----->" + temp);
        } else if (locator.equalsIgnoreCase("name")) {
            temp = driver.findElement(By.name(objectName));
        }
        return temp;


    }

}

WritablData类别：

public class WritableData {

    Workbook wbook;
    WritableWorkbook wwbCopy;
    String ExecutedTestCasesSheet;
    WritableSheet shSheet;

    public WritableData(String testSuitePath, String OBJECT_REPOSITORY_PATH) throws RowsExceededException, WriteException 
    {
        // TODO Auto-generated constructor stub


         try { 
         wbook = Workbook.getWorkbook(new File(testSuitePath)); 
         wwbCopy= Workbook.createWorkbook(new File(testSuitePath),wbook);
         System.out.println("writable workbookC-->" +wwbCopy);
         shSheet=wwbCopy.getSheet("Login");
         System.out.println("writable sheetC-->" +shSheet); 
          //shSheet = wwbCopy.createSheet("Login", 1); 
         } catch (Exception e) { 
             // TODO: handle exception 
             System.out.println("Exception message" + e.getMessage()); e.printStackTrace(); } 
    }

          public void shSheet(String strSheetName, int iColumnNumber, int
          iRowNumber, String strData) throws WriteException, IOException { 
              // TODO Auto-generated method stub


        System.out.println("strDataCC---->>" +strData);
        System.out.println("strSheetName<<>><<>><<>>" +strSheetName);

          WritableSheet wshTemp = wwbCopy.getSheet(strSheetName);
          //shSheet=wwbCopy.getSheet("Login"); 
          shSheet=wwbCopy.getSheet(strSheetName);

          //String a[][] = new String[shSheet.getRows()][shSheet.getColumns()];

         WritableFont cellFont = null;
          WritableCellFormat cellFormat = null;

          if (strData.equalsIgnoreCase("PASS")) { 
          cellFont = new WritableFont(WritableFont.TIMES, 12);
          cellFont.setColour(Colour.GREEN);
          cellFont.setBoldStyle(WritableFont.BOLD);

          cellFormat = new WritableCellFormat(cellFont);
          cellFormat.setBorder(Border.ALL, BorderLineStyle.THIN); }

          else if (strData.equalsIgnoreCase("FAIL")) { 
          cellFont = new WritableFont(WritableFont.TIMES, 12); 
          cellFont.setColour(Colour.RED);
          cellFont.setBoldStyle(WritableFont.BOLD);

          cellFormat = new WritableCellFormat(cellFont);
          cellFormat.setBorder(Border.ALL, BorderLineStyle.THIN); }

          else { cellFont = new WritableFont(WritableFont.TIMES, 12);
          cellFont.setColour(Colour.BLACK);
          cellFormat = new WritableCellFormat(cellFont);
          cellFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
          cellFormat.setWrap(true); }


          Label labTemp = new Label(iColumnNumber, iRowNumber, strData,
          cellFormat);
          shSheet.addCell(labTemp); 
          System.out.println("writableSheet---->"+shSheet);

          wwbCopy.write(); 
          wwbCopy.close();
          wbook.close();
}
}

Answer 1

我在这里没有得到我问题的答案，但是是的，现在我有了解决方案，它对我来说也很好用，请检查以下代码，希望它也对您有所帮助：

public class MainClass {
    private static final String BROWSER_PATH = "D:\\softs\\FF installed\\FF18\\firefox.exe";
    private static final String TEST_SUITE_PATH = "D:\\softs\\configuration\\GmailTestSuite.xls";
    private static final String TEST_RESULT = "D:\\softs\\configuration\\TestResult.xls";
    private static final String OBJECT_REPOSITORY_PATH = "D:\\softs\\configuration\\objectrepository.xls";
    private static final String ADDRESS_TO_TEST = "https://www.gmail.com";

    // other constants

    private WebDriver driver;
    private Properties properties;

    /* private WebElement we; */

    public MainClass() {
        File file = new File(BROWSER_PATH);
        FirefoxBinary fb = new FirefoxBinary(file);
        driver = new FirefoxDriver(fb, new FirefoxProfile());
        driver.get(ADDRESS_TO_TEST);
    }

    public static void main(String[] args) throws Exception {
        MainClass main = new MainClass();
        main.handleTestSuite();
    }

    private void handleTestSuite() throws Exception {
        ReadPropertyFile readConfigFile = new ReadPropertyFile();
        properties = readConfigFile.loadPropertiess();

        ExcelHandler testSuite = new ExcelHandler(TEST_SUITE_PATH, "Suite");
        testSuite.columnData();

        int rowCount = testSuite.rowCount();
        System.out.println("Total Rows=" + rowCount);

        for (int i = 1; i < rowCount; i++) {
            String executable = testSuite.readCell(
                    testSuite.getCell("Executable"), i);
            System.out.println("Executable=" + executable);

            if (executable.equalsIgnoreCase("y")) {
                // exe. the process
                String scenarioName = testSuite.readCell(
                        testSuite.getCell("TestScenario"), i);
                System.out.println("Scenario Name=" + scenarioName);
                handleScenario(scenarioName);
            }

        }

    }

    private void handleScenario(String scenarioName) throws Exception {
        ExcelHandler testScenarios = new ExcelHandler(TEST_SUITE_PATH);
        testScenarios.setSheetName(scenarioName);
        testScenarios.columnData();
        int rowWorkBook1 = testScenarios.rowCount();
        for (int j = 1; j < rowWorkBook1; j++) {
            String sno = testScenarios.readCell(
                    testScenarios.getCell("Sno"), j); // SendKey
            String testCaseDescription = testScenarios.readCell(
                    testScenarios.getCell("TestCaseDescription"), j);

            String framWork = testScenarios.readCell(
                    testScenarios.getCell("FrameworkName"), j);
            String operation = testScenarios.readCell(
                    testScenarios.getCell("Operation"), j); // SendKey
            String value = testScenarios.readCell(
                    testScenarios.getCell("Value"), j);
            System.out.println("FRMNameKK=" + framWork + ",Operation="
                    + operation + ",Value=" + value);

            boolean bTestCaseStepStatus = handleObjects(operation, value, framWork);
                    generateReport(bTestCaseStepStatus,scenarioName,sno,testCaseDescription,j);
            }
    }

    private boolean generateReport(boolean bTestCaseStepStatus, String testSuiteName, String SNO, String testCaseDescription, int j) 
    throws RowsExceededException, WriteException, IOException
    {
        WritableData writableData= new WritableData(TEST_RESULT, testSuiteName);

        if(bTestCaseStepStatus){
            writableData.shSheet("Login", 2, j, "Pass");
            driver.manage().timeouts().implicitlyWait(5, TimeUnit.SECONDS);
        }else{
            writableData.shSheet("Login", 2, j, "Fail");
            driver.manage().timeouts().implicitlyWait(5, TimeUnit.SECONDS);
        }

        return bTestCaseStepStatus;

    }

    private boolean handleObjects(String operation, String value, String framWork)
            throws Exception {
        //Return true / False
        System.out.println("HandleObject--> " + framWork);
        ExcelHandler objectRepository = new ExcelHandler(
                OBJECT_REPOSITORY_PATH, "OR");
        objectRepository.columnData();
        int rowCount = objectRepository.rowCount();
        System.out.println("Total Rows in hadleObject=" + rowCount);
        boolean testStepStatus = false;

        for (int k = 1; k < rowCount; k++) {
            String frameWorkName = objectRepository.readCell(
                    objectRepository.getCell("FrameworkName"), k);
            String ObjectName = objectRepository.readCell(
                    objectRepository.getCell("ObjectName"), k);
            String Locator = objectRepository.readCell(
                    objectRepository.getCell("Locator"), k); // SendKey

            System.out.println("FrameWorkNameV=" + frameWorkName
                    + ",ObjectName=" + ObjectName + ",Locator=" + Locator);

            if (framWork.equalsIgnoreCase(frameWorkName)) {
                testStepStatus = operateWebDriver(operation, Locator, value, ObjectName);

            }
        }

        return testStepStatus;
    }

    private boolean operateWebDriver(String operation, String Locator,
            String value, String objectName) throws Exception 
    {
        boolean testCaseStep = false;

        try {
            System.out.println("Operation execution in progress");
            WebElement temp = getElement(Locator, objectName);
            if (operation.equalsIgnoreCase("SendKey")) {
                temp.sendKeys(value);
            }
            Thread.sleep(1000);
            driver.manage().timeouts().implicitlyWait(5, TimeUnit.SECONDS);
            if (operation.equalsIgnoreCase("Click")) {
                temp.click();
            }
            if (operation.equalsIgnoreCase("Verify")) {
                System.out.println("Verify--->" + temp);
                temp.isDisplayed();

            }

            testCaseStep = true;
        } catch (Exception e) {
            System.out.println("Exception occurred operateWebDriver"+ e.getMessage());
        }

        return testCaseStep;
    }

    public WebElement getElement(String locator, String objectName)
            throws Exception {
        WebElement temp = null;
        System.out.println("Locator-->" + locator);
        if (locator.equalsIgnoreCase("id")) {
            temp = driver.findElement(By.id(objectName));

        } else if (locator.equalsIgnoreCase("xpath")) {
            temp = driver.findElement(By.xpath(objectName));
            System.out.println("xpath temp ----->" + temp);
        } else if (locator.equalsIgnoreCase("name")) {
            temp = driver.findElement(By.name(objectName));
        }
        return temp;
        }
}