CODEWITHSUNDEEP
pythoncssseleniumopenpyxl
nfordhk
nfordhk

Reputation: 31

Selenium and Excel - Import Excel Data and Send Keys

I am working on a project where I need to import an excel sheet into a website form. I'm having trouble with properly reading the data from the sheet and send it using the send_keys.

entries in the form

load_rows = 0
while load_rows < 101:
    add_element = driver.find_element_by_css_selector('.form-footer')
    add_element.click()
    load_rows = load_rows + 1
    driver.execute_script("window.scrollTo(0, 1000);")

excel sheet

# Loading the Excel Info
filepath = "trusted_ips.xlsx"
wb = load_workbook(filepath)
ws = wb.active
max_row = sheet.max_row
max_column = sheet.max_column
print("Total columns : ", max_column)
print("Total rows : ", max_row)

It seems like it's storing all of column A into name_elements.. So, the send_keys function is sending all of column A before moving to the next field.

I want it to only send each element to a field, and I think that a list would fix this. But I'm not too sure. 

for name_elements in driver.find_elements_by_xpath ('//*[contains(@id, 
    "_name")]'):
    for row in ws['A2':'A100']:
        for cell in row:
            name_elements.send_keys(cell.value)
    print(name_elements)

Upvotes: 3

Views: 3215

Answers (2)

nfordhk
nfordhk

Reputation: 31

I was able to solve this with the following -

I created a list cells[] to store the value. Followed by using the variable name_elements to store them. Settings the counter=0 

cells=[]
counter=0
name_elements = driver.find_elements_by_xpath ('//*[contains(@id, "_cidr")]')

The first loop iterates over Column A of the excel sheet and stores the values in the list cells[]. 

for row in ws['C2':'C100']:
    for cell in row:
        cells.append(cell.value)

The second loop takes the list, for each value performs a send key to the next name_elements using the counter to loop through it. 

for cell in cells:
    name_elements[counter].send_keys(cell)
    counter+=1

Upvotes: 0

cgclip
cgclip

Reputation: 322

you probably want to use pandas or else work on formatting your excel file from openpyxl into a more manageable dictionary.

I've done similar kinds of work, where I need to scrape off of the website where information on an excel file is an input.

My set up is somewhat like the following

import pandas as pd

#load an excel file into a pandas dataframe
df = pd.read_excel("myfile.xlsx",
                    sheet_name=1, # pick the first sheet
                    index_col='client') # set the pandas index to be the column labeled 'client'

# .to_dict converts the dataframe to a dictionary with 'client' being the key, and the remaining rows being converted to dictionary with the column label as key and row as value
for client, record in df.to_dict(orient='records'):
    print(client) # use these print statements if what you're looping over is unfamiliar 
    print(record)
    driver.find_element('#client_input).send_keys(client)
    driver.find_element('#address_input').send_keys(record['address'])

Upvotes: 1

Related Questions