Trouble with looping through a column of IP addresses using Selenium, Python and Openpyxl

Question

I am fairly new to Python and have gotten stuck. I have written a script that will read an IP address from column A of an excel file, open a headless browser with that IP, scrape the device Mac address, and paste it into column B and save the file. I've been able to successfully get that piece working by hard coding the cells where the IP/MAC (A2,B2) is read/written to. I would like to have 100s of IP addresses to go through but am having trouble looping through the cells. I'm not sure where the loop should be or how to increment the cells. Also any tips on how to make what I have written more efficient/pythonic would be greatly appreciated.

def mac_attack():
    from openpyxl import load_workbook
    # set file path
    filepath="C:\Users\myFile.xlsx"
    wb = load_workbook(filepath)
    sheet = wb["Sheet1"]
    ip = sheet["A2"].value
    from selenium import webdriver
    from selenium.webdriver.chrome.options import Options
    chrome_options = Options()
    chrome_options.add_argument("--headless")
    driverPath = "C:\Users\chromedriver.exe"
    browser = webdriver.Chrome(executable_path=driverPath, options=chrome_options)
    browser.get(ip)
    browser.implicitly_wait(10)
    mac = browser.find_elements_by_xpath('/html/body/div[2]/table[2]/tbody/tr[1]/td[2]')[0].text
    #writing
    sheet["B2"].value = mac
    wb.save("C:\Users\myFile.xlsx")
    print(mac)
    print(ip)

mac_attack()

question from:https://stackoverflow.com/questions/65851934/trouble-with-looping-through-a-column-of-ip-addresses-using-selenium-python-and

Answer 1

So one tip about openpyxl and Excel is that you need to make absolutely sure there is no extraneous data or else openpyxl will read that in when you don't want it to do so. So use clear contents in all cells around the data you want to read in.

Here is my suggested edit with comments so you can see what I am doing.

def mac_attack():
    from openpyxl import load_workbook
    # set file path
    filepath="C:\Users\myFile.xlsx"
    wb = load_workbook(filepath)
    sheet = wb["Sheet1"]
    # Read In the Entire Column of Data, not Just One Value
    sourceIPCol = sheet['A'] 
    # This Should Give you an Immutable 'Tuple' Data Type
    # You can confirm that by print(type(sourceIPCol))
    # Assuming I am right, Convert it to a List next
    sourceIPCol = list(sourceIPCol)
    # Iterate Through Each Address in your list of IP addresses
    # Note that you may prefer to have this as a function above this
    # function which you 'call' from this function.  It may be cleaner.
    B2row = 0  # Keep track of which row you want to print to in B2
    for address in sourceIPCol:
         from selenium import webdriver
         from selenium.webdriver.chrome.options import Options
         chrome_options = Options()
         chrome_options.add_argument("--headless")
         driverPath = "C:\Users\chromedriver.exe"
         browser = webdriver.Chrome(executable_path=driverPath, options=chrome_options)
         browser.get(ip)
         browser.implicitly_wait(10)
         mac = browser.find_elements_by_xpath('/html/body/div[2]/table[2]/tbody/tr[1]/td[2]')[0].text
         #Write Corresponding Value to Column B in SAME SHEET
         sheet[B2row]["B"].value = mac
         # NOTE THAT SO LONG AS YOU ITERATE THROUGH THE ENTIRE COLUMN
         # YOU SHOULDN'T NEED TO SAVE UNTIL YOU GET THROUGH THE WHOLE 
         # LIST, BUT I LEFT THE LINE BELOW HERE AS I DON'T THINK IT HURTS ANYTHING.  IT WILL JUST MAKE YOUR CODE SLOWER.  TECHNICALLY YOU JUST NEED TO SAVE ONCE AT THE END BEFORE YOU CLOSE THE FILE STREAM.
         wb.save("C:\Users\myFile.xlsx")
         print(mac)
         print(ip)
         B2row = B2row + 1

mac_attack()