Reading an Excel File and Submitting a Web Form Using Selenium Java

Pre-requisites:

1. geckodriver.exe
2. maven dependencies for apache poi & Selenium

<dependency>
            <groupId>org.seleniumhq.selenium</groupId>
            <artifactId>selenium-java</artifactId>
            <version>4.1.1</version>
        </dependency>
   <!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>5.2.2</version>
        </dependency>

This automation process is divided into two parts:

1. Reading from an Excel file 
2. WebForm Automated Filling & Submission

Reading an MS Excel File Using Apache POI:

Apache POI is an open-source java library developed and distributed by Apache Software Foundation to design and modify MS Office files. We will be using XSSF a class apache poi. XSSF: XML Spreadsheet Format is used for .xlsx files of MS-Excel.

1. Reading an Excel file using Java Apache POI is a six-step process: 
2. Put Excel file on input Stream.
3. Create a workbook instance of an excel sheet.
4. Get to the desired sheet of file.
5. Iterate rows of files.
6. Iterate over all cells in one row and place the values into an object of a custom class Record.
7. Repeat steps 4 and 5 until all data is read

Let’s see all the above steps in the code. The following function readFile() read all the values in the Excel file – cell by cell and returns a list of Records.

public static ArrayList<Record> readFile(String filePath) throws IOException {

        ArrayList<Record> recordArrayList = new ArrayList<>();
        try {

            //put file on input stream
            FileInputStream file = new FileInputStream(new File(filePath));
            XSSFWorkbook workbook = new XSSFWorkbook(file);

            //Get desired sheet from the workbook
            XSSFSheet sheet = workbook.getSheetAt(0);

            //Iterate through each row one by one
            Iterator<Row> rowIterator = sheet.iterator();
            Row row = rowIterator.next();

            //get value from each cell and insert it into Record object
            while (rowIterator.hasNext()) {

                row = rowIterator.next();
                Record record = new Record();

                //insert data from cells to record Object
                Cell cell = row.getCell(0);
                record.id = (int) cell.getNumericCellValue();

                cell = row.getCell(1);
                record.name = cell.getStringCellValue();

                cell = row.getCell(2);
                record.age = (int) cell.getNumericCellValue();

                cell = row.getCell(3);
                record.gender = cell.getStringCellValue();

                cell = row.getCell(4);
                record.city = cell.getStringCellValue();

                cell = row.getCell(5);
                record.courses = (cell.getStringCellValue()).split(",");

                cell = row.getCell(6);
                record.goal = cell.getStringCellValue();

                recordArrayList.add(record);

            }
            file.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

        return recordArrayList;
    }

Following is the code for Record.java class:

public class Record {
    int id;
    int age;
    String name;
    String city;
    String gender;
    String[] courses;
    String goal;
}

WebForm Automation Using Selenium:

Selenium is an open-source web-based automation tool that is implemented using a web driver. We will be using geckodriver because Selenium 3 enables geckodriver as the default WebDriver implementation for Firefox.

The process involves 3 base steps.

1. Set webdriver.gecko.driver and its' path as a system property.
2. Set the firefox diver and wait for attribute.

//set firefox webdriver
System.setProperty("webdriver.gecko.driver",GECKODRIVER_PATH);
        WebDriver driver = new FirefoxDriver();
        WebDriverWait wait = new WebDriverWait(driver, Duration.ofSeconds(120));

3. Fire up the driver by using the hyperlink of web form.

//get the firefox browser & Browse the Google form
            String wedFormHyperlink = "https://docs.google.com/forms/d/e/1FAIpQLSd3hbwlnuLuwVVnP3iEAjQwXQuZ7PuiTNMuuYe0TcHHYqZ6uQ/viewform";
            driver.get(wedFormHyperlink);

The following 6 steps will automate the process of form filling and submission:

1. Apply and wait for the DOM to be ready.
2. Identify HTML & CSS selectors of fields in web form.  
3. Click the field section to make it visible for getting the corresponding web element. 
4. Get the web element or a list of web elements by using css-selectors of the corresponding field of the form.   
5. Send the value of the field as key (in case of input fields)
1. In case of drop-down, checkboxes, radio buttons and etc, click the flex element. (Got as web element using css selector)
6. Repeat steps 2-5 to fill and submit the form. 

Let’s see all the above steps in the code. The following function fillTheForm() fills the form using values in the Record object, submits the form, and then redirects to fill against other records.

public static void fillTheForm(Record rc, WebDriverWait wait, WebDriver driver) {

        //wait until document is ready
        wait.until(ExpectedConditions.titleContains("Self-Development Boot Camp"));

        //insert data fom ArrayList to the web form
        List<WebElement> genderElements = wait.until(ExpectedConditions.visibilityOfAllElementsLocatedBy(By.cssSelector("div.nWQGrd.zwllIb")));

        //set Gender
        if (rc.gender.equals("Male"))
            genderElements.get(0).click();
        else
            genderElements.get(1).click();

        //set input fields (id,name,age.goals)
        List<WebElement> inputElements = wait.until(ExpectedConditions.visibilityOfAllElementsLocatedBy(By.cssSelector("input.whsOnd.zHQkBf")));
        inputElements.get(0).click();
        inputElements.get(0).sendKeys(Integer.toString(rc.id));

        inputElements.get(1).click();
        inputElements.get(1).sendKeys(rc.name);

        inputElements.get(2).click();
        inputElements.get(2).sendKeys(Integer.toString(rc.age));

        WebElement textarea = wait.until(ExpectedConditions.visibilityOfElementLocated(By.cssSelector("textarea.KHxj8b.tL9Q4c")));
        textarea.click();
        textarea.sendKeys(rc.goal);


        //open cities drop-drown menu
        driver.findElements(By.cssSelector("span.vRMGwf.oJeWuf")).get(0).click();
        //get dropdown items
        List<WebElement> allCities = driver.findElements(By.cssSelector("span.vRMGwf.oJeWuf"));
        //getting the valid index after removing empty divs
        int allCitiesIndex = allCities.size() - 6;

        //Removing extra divs
        for (int i = 0; i < allCitiesIndex; i++)
            allCities.remove(0);


        //set city-items
        Boolean citiesFlag = false;
        for (WebElement wb : allCities) {

            if (rc.city.equals(wb.getText())) {

                wb.click();
                citiesFlag = true;
                break;
            }
        }
        if (citiesFlag == false)
            allCities.get(5).click();

        //click the courses section to make it available for selenium processing
        driver.findElements(By.cssSelector("div.z12JJ")).get(4).click();

        //Set Courses
        List<WebElement> allCourses = wait.until(ExpectedConditions.visibilityOfAllElementsLocatedBy(By.cssSelector("div.ulDsOb")));
        //remove extra divs
        allCourses.remove(0);
        allCourses.remove(0);

        //set course-checkbox
        for (WebElement course : allCourses) {

            for (String eachCourse : rc.courses) {
                if (eachCourse.equals(course.getText()))
                    course.click();
            }
        }

        WebElement submitDiv = wait.until(ExpectedConditions.visibilityOfElementLocated(By.cssSelector("div.lRwqcd")));
        //submit button click
        submitDiv.findElement(By.cssSelector("span.l4V7wb.Fxmcue")).click();

        //move to the next response - redirect the webdriver to the same form
        WebElement nextResponseForm = wait.until(ExpectedConditions.visibilityOfElementLocated(By.cssSelector("div.c2gzEf")));
        nextResponseForm.findElement(By.tagName("a")).click();

    }

Output:

All resources used in this tutorial are attached:

• source code
• geckdriver.exe
• Excel File
• Jar files to be included in the classpath

Download