Automate filling web form with excel data

In the web automation, it is a common scenario that we need to fill data on the web page. Here I would like to use two different ways (Selenium and Clicknium) to automate filling data in web form.

The steps overview are as below:

Read data from excel file with Pandas
Fill data on the web page with Selenium and Clicknium

Here is the data in the excel file that we need to fill on the web page:

excel data

Read data from excel

Install pandas with the following command:

pip install pandas

Read data from excel with the file path and the sheet name:

import pandas

def read_excel(excel_file:str, sheet_name: str = 'Sheet1'):
    excel_date_df = pandas.read_excel(excel_file, sheet_name)
    excel_date_df = excel_date_df.where(excel_date_df.notnull(), None)
    dicts = excel_date_df.to_dict(orient='records')
    return dicts

Fill data on the web page

Selenium

Here is the video for complete execution:
Install selenium python library with the following command:

pip install selenium

Download the Chrome web driver, we use the Chrome browser in this section.
Identify the HTML document to find the locator of each element as below:
Open the browser with the web page URL:

from selenium import webdriver
driver = webdriver.Chrome("Location-Of-Your-Web-Driver")
driver.get("https://forms.office.com/r/contoso")
sleep(2)

Fill web page for single record:

records = read_excel(Setting.excel_file, Setting.sheet_name)
for record in records:
    job_title = driver.find_element(By.XPATH, "//*[@id=\"form-container\"]/div/div/div[1]/div/div[1]/div[2]/div[2]/div[1]/div/div[3]/div/div/input")
    job_title.send_keys(record['Job Title'] if record['Job Title'] else "")

    company_name = driver.find_element(By.XPATH, "//*[@id=\"form-container\"]/div/div/div[1]/div/div[1]/div[2]/div[2]/div[2]/div/div[3]/div/div/input")
    company_name.send_keys(record['Company Name'] if record['Company Name'] else "")

    company_size = driver.find_element(By.XPATH, "//*[@id=\"form-container\"]/div/div/div[1]/div/div[1]/div[2]/div[2]/div[3]/div/div[3]/div/div/input")
    company_size.send_keys(record['Company Size'] if record['Company Size'] else "")

    job_type = driver.find_element(By.XPATH, "//*[@id=\"form-container\"]/div/div/div[1]/div/div[1]/div[2]/div[2]/div[5]/div/div[3]/div/div/input")
    job_type.send_keys(record['Job Type'] if record['Job Type'] else "")

    post_date = driver.find_element(By.XPATH, "//*[@id=\"form-container\"]/div/div/div[1]/div/div[1]/div[2]/div[2]/div[4]/div/div[3]/div/div/input")
    post_date.send_keys(record['Post Date'] if record['Post Date'] else "")

    link = driver.find_element(By.XPATH, "//*[@id=\"form-container\"]/div/div/div[1]/div/div[1]/div[2]/div[2]/div[6]/div/div[3]/div/div/input")
    link.send_keys(record['Job Link'] if record['Job Link'] else "")

    # click submit button
    submit = driver.find_element(By.XPATH, "//*[@id=\"form-container\"]/div/div/div[1]/div/div[1]/div[2]/div[3]/div[1]/button/div")
    submit.click()
    sleep(2)
    
    # click submit another button
    submit_another = driver.find_element(By.XPATH, "//*[@id=\"form-container\"]/div/div/div[1]/div/div[1]/div[2]/div[2]/div[2]/a")
    submit_another.click()
    sleep(1)

Clicknium

Here is the video for complete execution:
Install Clicknium python library with the following command:

pip install clicknium

Follow Clicknium Getting Started to set up develop environment. And install Edge Extension, we use the Edge browser in this section.
Get elements with Clicknium Extension :
Open the browser with the web page URL:

from clicknium import clicknium as cc, locator
_tab = cc.edge.open("https://forms.office.com/r/contoso")

Fill web page for single record:

_tab.find_element(locator.edge.forms.title).set_text(record['Job Title'] if record['Job Title'] else "")
_tab.find_element(locator.edge.forms.company_name).set_text(record['Company Name'] if record['Company Name'] else "")
_tab.find_element(locator.edge.forms.company_size).set_text(record['Company Size'] if record['Company Size'] else "")
_tab.find_element(locator.edge.forms.type).set_text(record['Job Type'] if record['Job Type'] else "")
_tab.find_element(locator.edge.forms.post_date).set_text(record['Post Date'] if record['Post Date'] else "")
_tab.find_element(locator.edge.forms.link).set_text(record['Job Link'] if record['Job Link'] else "")
_tab.find_element(locator.edge.forms.submit).click()
_tab.wait_appear(locator.edge.forms.submitanother, wait_timeout=5).click()

Go to Github for complete source code.

Read data from excel​

Fill data on the web page​

Selenium​

Clicknium​

Read data from excel

Fill data on the web page

Selenium

Clicknium