Help with Excel, Python and XLRD

Question

Relatively new to programming hence why I've chosen to use python to learn.

At the moment I'm attempting to read a list of Usernames, passwords from an Excel Spreadsheet with XLRD and use them to login to something. Then back out and go to the next line. Log in etc and keep going.

Here is a snippit of the code:

import xlrd
wb = xlrd.open_workbook('test_spreadsheet.xls')

# Load XLRD Excel Reader

sheetname = wb.sheet_names() #Read for XCL Sheet names
sh1 = wb.sheet_by_index(0) #Login

def readRows():
        for rownum in range(sh1.nrows):
                rows = sh1.row_values(rownum)
                userNm = rows[4]
                Password = rows[5]
                supID = rows[6]
                print userNm, Password, supID

print readRows()

I've gotten the variables out and it reads all of them in one shot, here is where my lack of programming skills come in to play. I know I need to iterate through these and do something with them but Im kind of lost on what is the best practice. Any insight would be great.

Thank you again

Answer 1

couple of pointers:

i'd suggest you not print your function with no return value, instead just call it, or return something to print.

def readRows():
    for rownum in range(sh1.nrows):
        rows = sh1.row_values(rownum)
        userNm = rows[4]
        Password = rows[5]
        supID = rows[6]
        print userNm, Password, supID

readRows()

or looking at the docs you can take a slice from the row_values:

row_values(rowx, start_colx=0, end_colx=None) [#]

Returns a slice of the values of the cells in the given row.

because you just want rows with index 4 - 6:

def readRows():
    # using list comprehension
    return [ sh1.row_values(idx, 4, 6) for idx in range(sh1.nrows) ]

print readRows()

using the second method you get a list return value from your function, you can use this function to set a variable with all of your data you read from the excel file. The list is actually a list of lists containing your row values.

L1 = readRows()
for row in L1:
    print row[0], row[1], row[2]

After you have your data, you are able to manipulate it by iterating through the list, much like for the print example above.

def login(name, password, id):
    # do stuff with name password and id passed into method
    ...

for row in L1:
    login(row)

you may also want to look into different data structures for storing your data. If you need to find a user by name using a dictionary is probably your best bet:

def readRows():
    rows = [ sh1.row_values(idx, 4, 6) for idx in range(sh1.nrows) ]
    # using list comprehension
    return dict([ [row[4], (row[5], row[6])] for row in rows ])

D1 = readRows()
print D['Bob']
('sdfadfadf',23)

import pprint

pprint.pprint(D1)
{'Bob': ('sdafdfadf',23),
 'Cat': ('asdfa',24),
 'Dog': ('fadfasdf',24)}

one thing to note is that dictionary values returned arbitrarily ordered in python.