Write to Excel using Python Selenium

Question

I am trying to write the output of a for loop in my code back into an Excel file column. I have followed some tutorials already but not sure I am understanding the logic correctly.

I have defined a function like so:

def writeData(file,sheetName,rownum,columnno,data):
    workbook = openpyxl.load_workbook(file)
    sheet = workbook.get_sheet_by_name(sheetName)

I've then tried to call the function (writeData) later on in a IF statement (inside a for loop):

if  document_id[:3] == "A70":
        print(document_id)
        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()
        print("success1")
        writeData(path, "Sheet1",r,3, "Retired")

But nothing happens after I check the Excel file upon successful completion. I would like it to say 'retired' if the action is successful, or 'failed' if not.

Here is all my code if that is needed:

path = 'filepathhere'

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 = 'emailhere'
password = 'password here'

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


driver.get("https://test.rs-dms.com/wp-admin/edit.php?post_type=rs_document")
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")
Restart = (By.XPATH, "/html/body/div[1]/div[1]/div[2]/ul/li[4]/ul/li[2]/a")

for r in range (2,rows+1):
    stock_number = readData(path,"Sheet1",r,1)
    document_id = readData(path, "Sheet1",r,2)
    print(document_id)
    
    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":
        print(document_id)
        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()
        print("success1")
        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')
        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()
        print("success2")
        writeData(path, "Sheet1",r,3, "Retired")

    else:
        writeData(path, "Sheet1",r,3, "Failed")
        
    WebDriverWait(driver, 10).until(EC.element_to_be_clickable(Restart)).click()

I feel I'm definitely off here with the logic and/or structure of my code, I would appreciate any support!! :)

Why don't you use pandas? You should check this :-https://stackoverflow.com/questions/69350194/while-opening-a-xlsx-file-written-through-python-an-error-pops-up-file-form — Bhavya Lodaya, Apr 07 '22 at 12:25

score 0 · Answer 1 · answered Apr 07 '22 at 12:22

Your writeData function is incomplete

def writeData(file,sheetName,rownum,columnno,data):
    workbook = openpyxl.load_workbook(file)
    sheet = workbook.get_sheet_by_name(sheetName)

This function is not writting anything in the excel Just opening the excel sheet

def writeData(file,sheetName,rownum,columnno,data):
    workbook = openpyxl.load_workbook(file)
    sheet = workbook.get_sheet_by_name(sheetName)
    # set data value to cell
    sheet.cell(row=rownum, column=columnno).value = data
    # save workbook 
    workbook.save(file)

Replace your function with This function It will write your data to excel sheet and also save the excel

Hope This code will Help you

Write to Excel using Python Selenium

1 Answers1