Append data in Excel using Python Selenium Openpyxl

Question

I have a sheet with 5 registration numbers, which are successfully being entered into the webpage search, and the code runs without errors while iterating using a 'For' statement for each row in the 'A' column (Col=0).

I am now trying to append the colour returned by the code into the corresponding row in the 'B' column (col=1)

The code I have written (With previous help here) is below:

    from openpyxl import Workbook, load_workbook
    from selenium import webdriver
    from selenium.webdriver.chrome.service import Service
    from webdriver_manager.chrome import ChromeDriverManager
    from selenium.webdriver.common.keys import Keys
    from selenium.webdriver.common.by import By
    from selenium.webdriver.support.ui import WebDriverWait
    from selenium.webdriver.support import expected_conditions as EC
    import time

    wb = load_workbook('data.xlsx')
    ws = wb.active

    for row in ws.iter_rows(min_row=2, max_col=0, max_row=6, values_only=True):

    driver = webdriver.Chrome(service=Service(ChromeDriverManager().install()))
    driver.get("https://www.google.com")

    driver.get ("https://vehicleenquiry.service.gov.uk/")

    time.sleep(5)

    search = driver.find_element(By.ID , "wizard_vehicle_enquiry_capture_vrn_vrn")
    search.send_keys(str(row))
    search.send_keys(Keys.RETURN)

    try:
        main = WebDriverWait(driver, 10).until(
            EC.presence_of_element_located((By.CLASS_NAME, "summary-no-action"))
            )
        
        print(WebDriverWait(driver,10).until(
            EC.visibility_of_element_located((By.XPATH, 
        "//dt[text()='Colour']/following::dd[1]"))).text
            )
        time.sleep(5)
        
    finally:
        driver.quit()

The code which brings back the colour and prints is:

        print(WebDriverWait(driver,10).until(
            EC.visibility_of_element_located((By.XPATH, 
        "//dt[text()='Colour']/following::dd[1]"))).text
            )

Is there a simple way to get this Character value into the adjacent row that I'm not seeing? I'm assuming I just something similar to:

for col in ws.iter_cols(min_row=2, max_col=2, max_row=6):
    for cell in col:
        append(cell)

However, I don't think this will work as the colour value hasn't been set to a variable. Any ideas on a working solution?

Ciric Aleksandar · Accepted Answer · 2022-04-22 13:44:48Z

1

In excel, first cell (first row and first column) is 1,1 there is not 0 column or 0 row

Is this helpful

wb = openpyxl.load_workbook('data.xlsx')
ws = wb.active

for row in range(2,7):

    YOUR_CODE

    ws.cell(row=row,column=2).value = YOUR_OUTPUT_DATA

as you can see, you can use ws.cell(row=row,column=1).value

answered Apr 22, 2022 at 13:44

Ciric Aleksandar

1564 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Joshua bailey Over a year ago

so for ws.cell(row=row,column=2).value = YOUR_OUTPUT_DATA. I should put - value = str(driver.find_element(By.XPATH, "//dt[text()='Colour']/following::dd[1]").text) or value = ("//dt[text()='Colour']/following::dd[1]").text ?

Ciric Aleksandar Over a year ago

you can write ws.cell(row=row,column=2).value=str(driver.find_element(By.XPATH, "//dt[text()='Colour']/following::dd[1]").text)

Joshua bailey Over a year ago

For some reason those changes are inputting the number 2 into the vehicle registration search? It's something to do with this line I'm guessing? search.send_keys(str(row))

Joshua bailey Over a year ago

also getting this error: File "C:\Users\josh.bailey\Documents\test4.py", line 35, in <module> ws.cell(row=row,column=2).value=str(driver.find_element File "C:\Program Files\Utils\Python38\lib\site-packages\openpyxl\worksheet\worksheet.py", line 237, in cell if row < 1 or column < 1: TypeError: '<' not supported between instances of 'tuple' and 'int'

Collectives™ on Stack Overflow

Append data in Excel using Python Selenium Openpyxl

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related