'None' value being imported from Excel into Python/Openpyxl/selenium when it shouldn't be

user_12707441 0
Viewed 67

This is the code I am using which is functioning as it should:

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_index, row in enumerate(ws.iter_rows(min_row=2, max_col=2, max_row=10000, 
values_only=True)):
if str(row) == None:
        wb.save('data.xlsx')
        driver.quit()

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

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

        main = WebDriverWait(driver, 10).until(
            EC.presence_of_element_located((By.ID, "wizard_vehicle_enquiry_capture_vrn_vrn"))
            )

        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
                )

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

        finally:
            wb.save('data.xlsx')
            driver.quit()

This is using the values in the data.xlsx sheet to search a web element and then output the colour of the vehicle to the spreadsheet in the next column. The iteration is working. However, this section is not functioning:

if str(row) == None:
        wb.save('data.xlsx')
        driver.quit()

I've tried a few different ways of typing this, but once the program completes the last reg number on the spreadsheet, it is inputting 'None' into the search bar for the next empty cell in the column causing the program to stop and require human attention. How do I get this to skip empty cells and then quit once it reaches the end of the spreadsheet? Thanks.

Sample Sheet Data Source:

enter image description here

openpyxl selenium-chromedriver selenium html python
1 Answers

I don't have that much experience with openpyxl, but this should solve the issue. Using enumerate() inside of another loop has in my experience caused only issues.

for row_index, row in ws.iter_rows(min_row=2, max_col=2, max_row=10000):
    for cell in enumerate(row):
        if cell == None:
            wb.save('data.xlsx')
            driver.quit()

This also only checks one cell at a time, narrowing down the scope allowing a smaller margin of error. (If it fails on one cell, it won't fail the whole row.)

for a bit more insight;

try:
    for row_index, row in ws.iter_rows(min_row=2, max_col=2, max_row=10000):
        for cell in enumerate(row):
            if cell == None:
                wb.save('data.xlsx')
                driver.quit()
except Exception as e:
    print(e)

TLDR: You're currently checking if the whole row is None, that will pass the whole row even if only 1 cell is None. In some instances this will be fine. But rather safe than sorry

user_183234840
Related