Read from Excel list and iterate/action Python Selenium

Question

I have a particular use case I am trying to figure out and would love some advice/useful resources.

I am trying to iterate through an excel file with two columns. The first column will hold a number that will be searched for in the website. The second columns number will be the target id for the actions I am trying to take. I'd ideally love it to print out in column C if it has successfully performed the action too but I can work towards that later.

So for example, start search in column A2, input that number into the search bar. Then once that number has been searched, search for the number in B2 and perform the action defined then move onto the next cell down A3 and repeat the cycle.

Here is my code I'm working with:

import pandas as pd
import unittest
import time
from selenium import webdriver
from selenium.webdriver.support.select import Select
from selenium.webdriver.common.by import By
from selenium.webdriver.support.ui import WebDriverWait
from selenium.webdriver.support import expected_conditions as EC
from selenium.webdriver.common.keys import Keys
from selenium.webdriver.support import expected_conditions
from selenium.webdriver.common.action_chains import ActionChains
from selenium.webdriver import ActionChains
from openpyxl import Workbook, load_workbook
import pyautogui


PATH = "C:\Chrome Driver\chromedriver.exe"
driver = webdriver.Chrome(PATH)

email = 'test@test.com'
password = 'test123'

EMAILFIELD = (By.ID, "i0116")
PASSWORDFIELD = (By.ID, "i0118")
NEXTBUTTON = (By.ID, "idSIButton9")


driver.get("exampleURL")
driver.set_window_position(1500, 0)
driver.maximize_window()

# LOG IN

WebDriverWait(driver, 10).until(EC.element_to_be_clickable(EMAILFIELD)).send_keys(email)
WebDriverWait(driver, 10).until(EC.element_to_be_clickable(NEXTBUTTON)).click()
WebDriverWait(driver, 10).until(EC.element_to_be_clickable(PASSWORDFIELD)).send_keys(password)
WebDriverWait(driver, 10).until(EC.element_to_be_clickable(NEXTBUTTON)).click()
WebDriverWait(driver, 10).until(EC.element_to_be_clickable(NEXTBUTTON)).click()

# SEARCH FOR SN

SearchBOX = (By.ID, "post-search-input")
TestSN = '8643967'
ENTER = (By.ID, "search-submit")

WebDriverWait(driver, 10).until(EC.element_to_be_clickable(SearchBOX)).send_keys(TestSN)
WebDriverWait(driver, 10).until(EC.element_to_be_clickable(ENTER)).send_keys(Keys.RETURN)

# SET OPTIONS

Options = (By.XPATH, "//*[@id='show-settings-link']")
Page_Number = (By.XPATH, "//*[@id='edit_rs_document_per_page']")
Apply_Button = (By.XPATH, "//*[@id='screen-options-apply']")

WebDriverWait(driver, 10).until(EC.element_to_be_clickable(Options)).click()
WebDriverWait(driver, 10).until(EC.element_to_be_clickable(Page_Number)).send_keys("999")
WebDriverWait(driver, 10).until(EC.element_to_be_clickable(Apply_Button)).click()
WebDriverWait(driver, 10).until(EC.element_to_be_clickable(Options)).click()

# FIND & RETIRE

before_XPath = "//*[@class='wp-list-table widefat fixed striped table-view-list pages']/tbody/tr["
aftertd_XPath_1 = "]/td[1]"
aftertd_XPath_2 = "]/td[2]"
aftertd_XPath_3 = "]/td[3]"
before_XPath_1 = "//*[@class='wp-list-table widefat fixed striped table-view-list pages']/tbody/tr[1]/th["
before_XPath_2 = "//*[@class='wp-list-table widefat fixed striped table-view-list pages']/tbody/tr[2]/td["
aftertd_XPath = "]/td["
after_XPath = "]"
aftertr_XPath = "]"

time.sleep(10)
search_text = ('0900766b813a9d80')

num_rows = len(driver.find_elements(By.XPATH, "//*[@class='wp-list-table widefat fixed striped table-view-list pages']/tbody/tr"))
num_columns = len(driver.find_elements(By.XPATH, "//*[@class='wp-list-table widefat fixed striped table-view-list pages']/tbody/tr[2]/td"))

elem_found = False


for t_row in range(2, (num_rows + 1)):
    for t_column in range(1, (num_columns + 1)):
        FinalXPath = before_XPath + str(t_row) + aftertd_XPath + str(t_column) + aftertr_XPath
        cell_text = driver.find_element(By.XPATH, FinalXPath).text

        #if ((cell_text.casefold()) == (search_text.casefold())):
            
        if search_text[:3] == "A70":
            retire_xpath = driver.find_element(By.XPATH, "//*[@id='retire-"+ str(search_text[-7:])+"']")
            time.sleep(6)
            driver.execute_script("arguments[0].click();", retire_xpath)
            time.sleep(5)
            #driver.switch_to.alert.accept()
            break
        elif search_text[:3] == "090":            
            print(search_text)
            post_parent_num = driver.find_element(By.XPATH, f"//div[@class='post_name' and text()='{search_text}']//following-sibling::div[@class='post_parent']").get_attribute('innerText')
            print(post_parent_num)
            retire_xpath = driver.find_element(By.XPATH, "//*[@id='retire-"+ post_parent_num +"']")
            time.sleep(6)
            driver.execute_script("arguments[0].click();", retire_xpath)
            time.sleep(5)
            #driver.switch_to.alert.accept()
            break
        else:
            print("retire/already retired")

'TestSN' is where column As values would go, through iteration and 'search_text' will be column Bs values. I'm not sure where to start, I feel this would be a big for loop? Can anyone help guide me?

Thanks in advance,

Stew

--------------------------EDIT--------------------------------

import pandas as pd
import unittest
import time
from selenium import webdriver
from selenium.webdriver.support.select import Select
from selenium.webdriver.common.by import By
from selenium.webdriver.support.ui import WebDriverWait
from selenium.webdriver.support import expected_conditions as EC
from selenium.webdriver.common.keys import Keys
from selenium.webdriver.support import expected_conditions
from selenium.webdriver.common.action_chains import ActionChains
from selenium.webdriver import ActionChains
from openpyxl import Workbook, load_workbook
import pyautogui
import xlrd
import openpyxl

path = 'C://Users/E0651690/Python Scripts/test_file.xlsx'

def getRowCount (file,sheetName):
    workbook = openpyxl.load_workbook(file)
    sheet = workbook.get_sheet_by_name(sheetName)
    return(sheet.max_row)

def getColumnCount(file,sheetName):
    workbook = openpyxl.load_workbook(file)
    sheet = workbook.get_sheet_by_name(sheetName)
    return(sheet.max_column)

def readData(file,sheetName,rownum,columnno):
    workbook = openpyxl.load_workbook(file)
    sheet = workbook.get_sheet_by_name(sheetName)
    return sheet.cell(row=rownum, column=columnno).value

def writeData(file,sheetName,rownum,columnno,data):
    workbook = openpyxl.load_workbook(file)
    sheet = workbook.get_sheet_by_name(sheetName)
    
rows=getRowCount(path, "Sheet1")

driver = webdriver.Chrome(executable_path="C:\Chrome Driver\chromedriver.exe")

email = 'username'
password = 'password'

EMAILFIELD = (By.ID, "i0116")
PASSWORDFIELD = (By.ID, "i0118")
NEXTBUTTON = (By.ID, "idSIButton9")


driver.get("URL")
driver.set_window_position(1500, 0)
driver.maximize_window()

WebDriverWait(driver, 10).until(EC.element_to_be_clickable(EMAILFIELD)).send_keys(email)
WebDriverWait(driver, 10).until(EC.element_to_be_clickable(NEXTBUTTON)).click()
WebDriverWait(driver, 10).until(EC.element_to_be_clickable(PASSWORDFIELD)).send_keys(password)
WebDriverWait(driver, 10).until(EC.element_to_be_clickable(NEXTBUTTON)).click()
WebDriverWait(driver, 10).until(EC.element_to_be_clickable(NEXTBUTTON)).click()

SearchBOX = (By.ID, "post-search-input")
ENTER = (By.ID, "search-submit")

for r in range (2,rows+1):
    stock_number = readData(path,"Sheet1",r,1)
    document_id = readData(path, "Sheet1",r,2)

    WebDriverWait(driver, 10).until(EC.element_to_be_clickable(SearchBOX)).send_keys(stock_number)
    WebDriverWait(driver, 10).until(EC.element_to_be_clickable(ENTER)).send_keys(Keys.RETURN)

    Options = (By.XPATH, "//*[@id='show-settings-link']")
    Page_Number = (By.XPATH, "//*[@id='edit_rs_document_per_page']")
    Apply_Button = (By.XPATH, "//*[@id='screen-options-apply']")

    WebDriverWait(driver, 10).until(EC.element_to_be_clickable(Options)).click()
    WebDriverWait(driver, 10).until(EC.element_to_be_clickable(Page_Number)).send_keys("999")
    WebDriverWait(driver, 10).until(EC.element_to_be_clickable(Apply_Button)).click()
    WebDriverWait(driver, 10).until(EC.element_to_be_clickable(Options)).click()


    before_XPath = "//*[@class='wp-list-table widefat fixed striped table-view-list pages']/tbody/tr["
    aftertd_XPath_1 = "]/td[1]"
    aftertd_XPath_2 = "]/td[2]"
    aftertd_XPath_3 = "]/td[3]"
    before_XPath_1 = "//*[@class='wp-list-table widefat fixed striped table-view-list pages']/tbody/tr[1]/th["
    before_XPath_2 = "//*[@class='wp-list-table widefat fixed striped table-view-list pages']/tbody/tr[2]/td["
    aftertd_XPath = "]/td["
    after_XPath = "]"
    aftertr_XPath = "]"

    time.sleep(10)

    num_rows = len(driver.find_elements(By.XPATH, "//*[@class='wp-list-table widefat fixed striped table-view-list pages']/tbody/tr"))
    num_columns = len(driver.find_elements(By.XPATH, "//*[@class='wp-list-table widefat fixed striped table-view-list pages']/tbody/tr[2]/td"))

    for t_row in range(2, (num_rows + 1)):
        for t_column in range(1, (num_columns + 1)):
            FinalXPath = before_XPath + str(t_row) + aftertd_XPath + str(t_column) + aftertr_XPath
            cell_text = driver.find_element(By.XPATH, FinalXPath).text

        if document_id[:3] == "A70":
            retire_xpath = driver.find_element(By.XPATH, "//*[@id='retire-"+ str(document_id[-7:])+"']")
            time.sleep(6)
            driver.execute_script("arguments[0].click();", retire_xpath)
            time.sleep(5)
            #driver.switch_to.alert.accept()
            writeData(path, "Sheet1", r, 3, "Retired")

        elif document_id[:3] == "090":            
            print(document_id)
            post_parent_num = driver.find_element(By.XPATH, f"//div[@class='post_name' and text()='{document_id}']//following-sibling::div[@class='post_parent']").get_attribute('innerText')
            print(post_parent_num)
            retire_xpath = driver.find_element(By.XPATH, "//*[@id='retire-"+ post_parent_num +"']")
            time.sleep(6)
            driver.execute_script("arguments[0].click();", retire_xpath)
            time.sleep(5)
            #driver.switch_to.alert.accept()
            writeData(path, "Sheet1", r, 3, "Retired")

        else:
            writeData(path, "Sheet1", r, 3, "Failed")

screenshot of the excel I'm working with:

user_6172089 · Answer

It would be easy to use pandas for handling the excel file.

First you will need to install pandas and openpyxl using pip or Anaconda depending on how you handle your environments. (If they are not already installed)

Then read the excel file using pandas:

import pandas as pd

df = pd.read_excel('./path_to_your_excel.xlsx')

Let's say this is part of your excel:

TestSN  Action
123     read
456     delete
789     update

You then will go through the the rows using pandas.iterrows. When iterating code will go through each row and row data will be available using column name, as seen in example below. meanwhile you can keep a list of action results which the result will be appended to it for each row.

action_results = []
for idx, row in df.iterrows():
    # Code here will run for each row
    # The loop will stop when reaching the last row of excel
    # Using row['TestSN'] and row['Action']
    # run your selenium code here
    ...
    TestSN = row['TestSN']
    ...

    action_results.append(True) # Or False depending on how it went

Finally you will add the action result columns to your dataframe and save it.

df['Action result'] = action_results
df.to_excel('./path_to_new_excel.xlsx')

It might also be a good idea to define your selenium code inside a function with serial number and action as input and result as output and use it when iteration over excel rows.

Full code:

import pandas as pd

df = pd.read_excel('./path_to_your_excel.xlsx')

action_results = []
for idx, row in df.iterrows():
    # Code here will run for each row
    # The loop will stop when reaching the last row of excel
    # Using row['TestSN'] and row['Action']
    # run your selenium code here
    ...
    TestSN = row['TestSN']
    ...
    
    action_results.append(True) # Or False depending on how it went

df['Action result'] = action_results
df.to_excel('./path_to_new_excel.xlsx')

Read from Excel list and iterate/action Python Selenium

1 Answers