Is there anyway to turn this into a loop so that everytime it loops it uses the next Excel cell valu-CodePudding

Currently trying to create a Python script that uses Selenium to Google Search a list of locations and report back their addresses. For context this is the code:

try:
    driver.get("https://www.google.com.au/search?q="   sh["B1"].value   " Address")
    Address = driver.find_element(By.CSS_SELECTOR, 'div.BRoiGe')
    print(Address.text)
except NoSuchElementException:
    print("FAIL "   sh["B1"].value)

try:
    driver.get("https://www.google.com.au/search?q="   sh["B2"].value   " Address")
    Address = driver.find_element(By.CSS_SELECTOR, 'div.BRoiGe')
    print(Address.text)
except NoSuchElementException:
    print("FAIL "   sh["B2"].value)

For example, B1 contains 'Eiffel Tower' and B2 contains 'Leaning Tower of Pisa' if you search those in Google the first result should look like such:

The except is in place for when a location does not return an address so the script can continue. My Excel sheet currently has over 100 locations I wish to scan therefore, I want to know if it is possible to set up a loop with each iteration of the loop moving to the next cell on the sheet or will I have to copy and paste the function 100 times?

CodePudding user response：

It's not quite clear from your question whether your "next cell" is down a row or across the columns, so here's a generic example of how you might iterate over both (assumes you're using openpyxl):

import openpyxl
from openpyxl.utils import get_column_letter

def load_excel(filename, sheet_number=1):
    contents = []
    book = openpyxl.load_workbook(filename)
    sheet = book.worksheets[sheet_number -1]
    for row_number in range(1, sheet.max_row  1): # <-- loop down the rows
        row_content = []
        for col_number in range(1, sheet.max_column  1): <-- loop accross the columns
            address = f'{get_column_letter(col_number)}{row_number}' # <-- e.g. if col_number=1 & row_number=1 you get "A1"
            row_content.append(sheet[address].value)
        contents.append(row_content)
    return contents

CodePudding user response：

You can pull in your spreadsheet as a pandas dataframe, and then loop through each value, like below:

import pandas as pd
locations = pd.read_excel('filename.xlsx')

for loc in locations['col'].values.tolist()
    try:
        driver.get("https://www.google.com.au/search?q="   loc   " Address")
        Address = driver.find_element(By.CSS_SELECTOR, 'div.BRoiGe')
        print(Address.text)
    except NoSuchElementException:
        print("FAIL "   loc)

Note: without knowing what your spreadsheet looks like, and how the values you want to iterate through are displayed, this may need some adaptions to work for your specific case.