How to iterate through excel file grabbing cell content and writing it in a form on a website

Question

Here is what I have so far:

Selenium and my python script can basically launch the browser, select the fields I want, input the text and click submit.

What I would like to do:

What I said above but instead of inputting the text based on hard coded text, I want the python script to loop through an excel sheet and plug in a cell value for each field.

excel file would look like (first name, last name, age):

bob smith 50

joe smith 60

What I have so far:

import xlrd
workbook = xlrd.open_workbook("people.xls")
worksheet = workbook.sheet_by_name('Sheet1')

from here I would like to select the first cell (bob) and do this:

fname = browser.find_element_by_id("id-of-field") 
fname.send_keys(fname_text + Keys.RETURN)

where fname_text would be a cell value on the worksheet. i can do this manually by fname_text = worksheet.cell_value(1,1) but that obviously doesn't fit my main purpose. it does work, just for testing though.

and then this (and so on):

lname = browser.find_element_by_id("id-of-field") 
lname.send_keys(lname_text + Keys.RETURN)

the data will always be in the same order and will always fill in the form in the same order.

This is my selenium line of code: (after following advice)

comment = browser.find_element_by_id("x-widget-1-input")
comment.send_keys(cmt + Keys.RETURN)

it got upset and showed this error: builtins.TypeError: unsupported operand type(s) for +: 'Cell' and 'str'

so i change it to:

comment = browser.find_element_by_id("x-widget-1-input")
comment.send_keys(str(cmt) + Keys.RETURN)

which types the following in the comment box:

text:'this is my 2nd comment'

it should just show this is my 2nd comment.

still needs some formatting love.

Answer 1

My interpretation is that you're looking for iteration so that it can set your fname_text, lname_text, and age, as it goes through each row in your sheet. To do this, use worksheet.nrows to iterate through each row and use worksheet.row to ultimately access your column data in each row.

import xlrd
workbook = xlrd.open_workbook("people.xls")
worksheet = workbook.sheet_by_name('Sheet1')
for current_row in range(worksheet.nrows):
    fname_text = worksheet.row(current_row)[0]
    lname_text = worksheet.row(current_row)[1]
    age = worksheet.row(current_row)[2]
    print fname_text, lname_text, age

This assumes your excel file is always 1st column fname, 2nd column lname, 3rd column age. At the end of the forloop, you can run whatever Selenium code that you'd like, using the fname, lname, and age data from that unique row in the spreadsheet.