How do I read data from excel sheet and use data as an input for webpage using selenium ? I am able-CodePudding

I have just started learning selenium and I am not able to automate the code only reads one at a time from excel.I need to make the code read from the excel automatically instead of changing the row count number in this line "for (int i= 1; i<=6; i )."

How can I make it automatically read from the code below?

    public static void main(String[] args) throws IOException, InterruptedException {

        System.setProperty("driver location");
        WebDriver driver = new FirefoxDriver();

        driver.get("link");


        FileInputStream file = new FileInputStream("xcel file location");

        XSSFWorkbook workbook = new XSSFWorkbook(file);
        XSSFSheet sheet= workbook.getSheet("SO Reg");

        int noOfRows = sheet.getLastRowNum(); // returns the row count
        System.out.println("No. of Records in the Excel Sheet:"   noOfRows);

        int cols=sheet.getRow(1).getLastCellNum();  
        System.out.println("No. of Records in the Excel Sheet:"   cols);

        for (int i= 1; i<=6; i  )

        {

            

                    String SO_Name = row.getCell(0).getStringCellValue();
                    String Contact_Person = row.getCell(1).getStringCellValue();
                    String Address_1 = row.getCell(2).getStringCellValue();
                    String Address_2 = row.getCell(3).getStringCellValue();
                    String City = row.getCell(4).getStringCellValue();
                    String State = row.getCell(5).getStringCellValue();
                    String ZipCode = row.getCell(6).getStringCellValue();
                    String Phone_Number = row.getCell(8).getStringCellValue();
                    String Username = row.getCell(9).getStringCellValue();
                    String Email = row.getCell(10).getStringCellValue();
                    String Re_Type_Email = row.getCell(11).getStringCellValue();

                    //Registration Process


                    driver.findElement(By.cssSelector("p.text-white:nth-child(4) > a:nth-child(1)")).click(); //create an account
                    Thread.sleep(5000); 

                    //Enter Data information

                    driver.findElement(By.id("SOName")).sendKeys(SO_Name);
                    driver.findElement(By.xpath("//*[@id=\"ContactPerson\"]")).sendKeys(Contact_Person);
                    driver.findElement(By.xpath("//*[@id=\"AddressLine1\"]")).sendKeys(Address_1);
                    driver.findElement(By.xpath("//*[@id=\"AddressLine2\"]")).sendKeys(Address_2);
                    driver.findElement(By.id("City")).sendKeys(City);
                    driver.findElement(By.id("State")).sendKeys(State);
                    driver.findElement(By.id("ZipCode")).sendKeys(ZipCode);
                    driver.findElement(By.id("Phone")).sendKeys(Phone_Number);
                    driver.findElement(By.xpath("//*[@id=\"UserName\"]")).sendKeys(Username);       
                    driver.findElement(By.xpath("//*[@id=\"Email\"]")).sendKeys(Email);
                    driver.findElement(By.xpath("//*[@id=\"RandText\"]")).sendKeys(Re_Type_Email);
                    driver.findElement(By.id("ConfirmBox")).click();
                    driver.findElement(By.xpath("/html/body/app-root/app-soregistration/div[2]/div/div/div/div/form[2]/div/div[12]/div/button[1]")).click();
                    driver.findElement(By.cssSelector(".btn-green-text-black")).click(); //finish button



                    driver.findElement(By.cssSelector("p.text-white:nth-child(4) > a:nth-child(1)")).click(); //create an account
                    Thread.sleep(5000); 



                }


            }


        }       
        
    }
    
}

CodePudding user response：

Do you only want to process newly added rows in Excel? If so, you should also save your last stay. First of all, you can simply keep it in an infinite loop. Like

while(true){...}

. You can also start your loop here by keeping the last line you read from Excel in a static variable. For example:

for (int i= previusLastSavedRowNum; i<=getLastRowNum; i  ) {...}

If there is no new record, you can wait for a while in the WHILE loop.

Of course, for a better solution, you can create a SpringBoot project and set up a structure that listens for changes in Excel. When Excel detects the change, you can call the Selenium code with a trigger.

CodePudding user response：

Better way to handle it is to open the excel file as CSV. You can read all the data into one String with: String excelToString = new String(Files.readAllBytes(Paths.get(path_to_file))); If you want to keep it as table you can parse this String into String [] [] table.