0

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



                }


            }


        }       
        
    }
    
}
Manshi Prabhu
  • 1

2 Answers2

1

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.

Tahsin
  • 11
  • 3
  • Do you mean i should replace this for (int i= 1; i<=6; i++) with this code for (int i= previusLastSavedRowNum; i<=getLastRowNum; i++)? – Manshi Prabhu Jul 23 '22 at 13:23
  • Yes, if your goal is to keep the application up and running and capturing changes in the Excel file. – Tahsin Jul 23 '22 at 13:31
  • ok. When i tried with the code mentioned "previusLastSavedRowNum" it was showing a red line underneath. Do you mean getLastRowNum();? – Manshi Prabhu Jul 23 '22 at 13:40
  • I meant you have to save the previous last row value in each new loop. If you can assign it to a static global variable you can use it later. "previusLastSavedRowNum" this is just an example name you can give a static variable. – Tahsin Jul 23 '22 at 13:58
  • oh ok. But how do you do that? Im completely new to selenium and just started learning and not sure how. Do I need to create a new public static for (int i= 1; i<=6; i++) { public static for (int i= 1; i<=6; i++) } – Manshi Prabhu Jul 23 '22 at 14:28
  • You can learn from this page: [link](https://stackoverflow.com/questions/4646577/global-variables-in-java) You can improve yourself by writing lots of code. Good luck in your learning. – Tahsin Jul 23 '22 at 15:27
0

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.

Itay Sasson
  • 1
  • 2