java - 如何在 selenium webdriver 中使用 JXL 检查工作簿工作表是否存在？

Question

我想检查工作簿中是否包含工作表名称“Login”。如果存在，那么我想在其中写入数据行=行+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

策略:

workbookobj.getNumberOfSheets()---->获取工作簿中的工作表数量

使用 for 循环并迭代工作簿中的所有工作表

workbookobj.getSheet(sheetno).getName()---->返回工作表名称，将其与登录字符串比较，检查工作表名称是否等于登录。

 for(int i=0;i<workbookobj.getNumberOfSheets();i++){

    if(workbookobj.getSheet(i).getName().contentEquals("login")){

    //use  Writeableworkbookobj.getSheet(); to return the specific sheet and write the data into the sheet                                                                          

    }else {

                    //do your stuff
    }
                }

已编辑:

    private void generateReport(boolean bTestCaseStepStatus, String testSuiteName, String SNO, String testCaseDescription) 
        throws RowsExceededException, WriteException, IOException
        {

            //Here create an workbook object

             Workbook workbookobj =  Workbook.getWorkbook(new File(TEST_RESULT));

               WritableData writableData= new WritableData(TEST_RESULT, testSuiteName);

                for(int i=0;i<workbookobj.getNumberOfSheets();i++){

                   if(workbookobj.getSheet(i).getName().contentEquals("login")){

                 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);
        }                                  

        }else {
                   //use your writable object to write directly into any sheet

             if(bTestCaseStepStatus){

                  writableData.shSheet(/*write data into any other sheet*/);
                    driver.manage().timeouts().implicitlyWait(5, TimeUnit.SECONDS);
        }else{
                  writableData.shSheet(/*write data into any other sheet*/);
                   driver.manage().timeouts().implicitlyWait(5, TimeUnit.SECONDS);
        }            

        }
                    }

希望这对您有帮助......