Send values from excel to input fields on webpage with selenium/python and openpyxl

Question

I have the following code:

from selenium import webdriver
import sys
import time
import os
import pyautogui
import webbrowser
import openpyxl
from selenium.webdriver.common.keys import Keys
from selenium.webdriver.support.ui import WebDriverWait
from selenium.webdriver.support import expected_conditions as EC
from selenium.webdriver.common.by import By
from selenium.webdriver.support.select import Select
from openpyxl import Workbook
from openpyxl import load_workbook

chrome_path =r"C:\Users\Desktop\webdriver\chromedriver.exe"
driver = webdriver.Chrome(chrome_path)
driver.get("url string")


def login(driver):

    elem = driver.find_element_by_xpath("""//*[@id="usernameField"]""")
    elem.send_keys("username")
    elem2 = driver.find_element_by_xpath("""//*[@id="passwordField"]""")
    elem2.send_keys("password")
    driver.find_element_by_xpath("""//*        [@id="loginForm"]/table/tbody/tr[4]/td[2]/input""").click()
    driver.find_element_by_xpath("""//*[@id="nav"]/ul/li[2]/a""").click()
    driver.find_element_by_xpath("""//*[@id="check1"]""").click()

def sendvalues(driver):

    wb = load_workbook('prueba.xlsx')
    coma = ","
    ws = wb.active
    buscar = driver.find_element_by_xpath(""" //*[@id="wrapper"]/form/div[2]/input[1]""")
    rut = driver.find_element_by_xpath("""//*[@id="rut"]""")
    dv = driver.find_element_by_xpath("""//*[@id="wrapper"]/form/table[1]/tbody/tr[3]/td[2]/input""")
    nombre = driver.find_element_by_xpath("""//*[@id="wrapper"]/form/table[1]/tbody/tr[4]/td[2]/input""")


    rutvalue = ws.cell(1,1).value
    dvvalue = ws.cell(1,2).value
    nombrevalue = ws.cell(1,3).value


    rut.send_keys(rutvalue)
    dv.send_keys(dvvalue)
    nombre.send_keys(nombrevalue)
    buscar.click()  
    table_elements = WebDriverWait(driver, 10).until(EC.presence_of_all_elements_located((By.XPATH, "//table[@class = 'grilla']")))
    for table_element in table_elements:
        for row in table_element.find_elements_by_xpath(".//tr"):
            text_file = open("Output2.txt", "a")
            text_file.write(str(rutvalue)+str(coma)+str(row.text)+'\n')
            text_file.close()

    clear(driver)

def clear(driver):

    rut = driver.find_element_by_xpath("""//*[@id="rut"]""")
    dv = driver.find_element_by_xpath("""//*[@id="wrapper"]/form/table[1]/tbody/tr[3]/td[2]/input""")
    nombre = driver.find_element_by_xpath("""//*[@id="wrapper"]/form/table[1]/tbody/tr[4]/td[2]/input""")
    rut.clear()
    dv.clear()
    nombre.clear()

login(driver)
sendvalues(driver)

The code extracts a table after sending an Excel file's single row values ​​to a website. I have three columns rutvalue, dvvalue and nombrevalue in the .xlsx file and I need to send those values for each row ​​to the specific input fields (rut value, dv value, name value) on the web page and get the table of results. I need to send the values ​​of each Excel row to the input fields.

Is there a method to develop a cycle iterating each row and get the results?

Answer 1

i changed and improved the code with satisfactory results:

from selenium import webdriver
import sys
import time
import os
import pyautogui
import webbrowser
import openpyxl
from selenium.webdriver.common.keys import Keys
from selenium.webdriver.support.ui import WebDriverWait
from selenium.webdriver.support import expected_conditions as EC
from selenium.webdriver.common.by import By
from selenium.webdriver.support.select import Select
from openpyxl import Workbook
from openpyxl import load_workbook

chrome_path =r"C:\Users\Desktop\webdriver\chromedriver.exe"
driver = webdriver.Chrome(chrome_path)
driver.get("myurl")


def login(driver):


    elem = driver.find_element_by_xpath("""//*[@id="usernameField"]""")
    elem.send_keys("myusername")
    elem2 = driver.find_element_by_xpath("""//*[@id="passwordField"]""")
    elem2.send_keys("mypassword")
    driver.find_element_by_xpath("""//*[@id="loginForm"]/table/tbody/tr[4]/td[2]/input""").click()
    driver.find_element_by_xpath("""//*[@id="nav"]/ul/li[2]/a""").click()
    driver.find_element_by_xpath("""//*[@id="check1"]""").click()

def sendvalues(driver):

    wb = load_workbook('prueba.xlsx')
    ws = wb.active
    buscar = driver.find_element_by_xpath(""" //*[@id="wrapper"]/form/div[2]/input[1]""")
    rut = driver.find_element_by_xpath("""//*[@id="rut"]""")
    dv = driver.find_element_by_xpath("""//*[@id="wrapper"]/form/table[1]/tbody/tr[3]/td[2]/input""")
    nombre = driver.find_element_by_xpath("""//*[@id="wrapper"]/form/table[1]/tbody/tr[4]/td[2]/input""")
    rutvalue=  ws.cell(row=x, column=1).value
    dvvalue= ws.cell(row=x, column=2).value
    nombrevalue= ws.cell(row=x, column=3).value
    rut.send_keys(rutvalue)
    dv.send_keys(dvvalue)
    nombre.send_keys(nombrevalue)
    buscar.click()
    table(driver)

def table(driver):
    rut = driver.find_element_by_xpath("""//*[@id="rut"]""")
    dv = driver.find_element_by_xpath("""//*[@id="wrapper"]/form/table[1]/tbody/tr[3]/td[2]/input""")
    nombre = driver.find_element_by_xpath("""//*[@id="wrapper"]/form/table[1]/tbody/tr[4]/td[2]/input""")
    coma = ","
    wb = load_workbook('prueba.xlsx')
    ws = wb.active
    rutvalue=  ws.cell(row=x, column=1).value
    table_elements = WebDriverWait(driver, 10).until(EC.presence_of_all_elements_located((By.XPATH, "//table[@class = 'grilla']")))

    for table_element in table_elements:
        for row in table_element.find_elements_by_xpath(".//tr"):
            text_file = open("Output2.txt", "a")
            text_file.write(str(rutvalue)+str(coma)+str(row.text)+'\n')
            text_file.close()
            rut.clear()
            dv.clear()
            nombre.clear()


login(driver)
for x in range(1,1000):
   sendvalues(driver)

I defined the "for loop" at the end of the code and defined in the sendvalues ​​function the factor x as seen in the following part:

rutvalue=  ws.cell(row=x, column=1).value
dvvalue= ws.cell(row=x, column=2).value
nombrevalue= ws.cell(row=x, column=3).value

Thanks for the comments!

Answer 2

You can create a class for the scraping code, and in another file, read the xlsx file and call the class in each iteration. Look this example.

class ScrapingCode():
   def __init__(self, rut, dv, name):
       self.rut = rut
       self.dv = dv
       self.name = name

  def run(self):
      #for use the values here, use self.name_var
      rut.send_keys(self.rut)  
      ...code scraping...

** another file

from app.folder.file import ScrapingCode
# read csv
for row in rows:
    scrapingCode = ScrapingCode(row[0], row[1], row[2])
    scrapingCode.run()