🍺 Join Clicknium for quality support, growth opportunities, and a supportive community

｜Join our community 🙋‍♂️🚀✨

All Blogs

Automate filling web form with excel data

July 25, 2022 · 3 min read

Dicey Dicey

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: selenium 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: web element
  • 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: clicknium complete execution
  • Install Clicknium python library with the following command:
pip install clicknium
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.

Tags:

Table of Contents

Table of contents
What are your feelings