Creating a list of objects using the rows of a spreadsheet using openpyxl

Question

I have a table in Excel as follows:

ID  Address         Type 
1   2 John Avenue   family 
2   3 John Avenue   single woman 
3   4 John Avenue   single man 
4   5 John Avenue   mixed couple 
5   6 John Avenue   youth 
6   7 John Avenue   family 
7   8 John Avenue   single woman 

I want to read each line from the table (excluding the header) and turn each row into an instance of a "Person" class.

In order to do this, I am trying to read each row into a dictionary, with "id" as the key. I am using "openpyxl" to read from the Excel file. Once I am able to store the data from the Excel file into a dictionary, I plan to create instances of the "Person" class - although I am not sure that this is the best approach...

This is the code that I have so far:

from openpyxl import load_workbook  

class Person(object):
    def __init__(self, id, address, type):
        self.id = id
        self.address = address
        self.type = type 

wb = load_workbook('sample.xlsx') 
sheet = wb.worksheets[0] 

row_count = sheet.max_row        
column_count = sheet.max_column     

header = []                 

for col in range (column_count):
    header.append(sheet.cell(0, col))

data = []
for row in range(1, row_count):
dict = {}
    for col in range(column_count):
        dict[header[row]]=sheet.cell(row,col)
    data.append(dict)

print (data)

I get the following error message:

AttributeError: 'int' object has no attribute 'upper'

I think it might be an indexing error but I've tried to resolve it with no luck.

Thank you in advance for your help!

Answer 1

I think the following should do what you want.

from openpyxl import load_workbook

class Person(object):
    def __init__(self, id=None, address=None, type=None):
        self.id = id # avoid using Python keywords where possible
        self.address = address
        self.type = type


wb = load_workbook('sample.xlsx')
sheetname = "Addresses"
ws = wb[sheetname]

# openpyxl >= 2.4
header = [cell.value for cell in ws[1]]
# openpyxl < 2.4
header = [cell.value for cell in ws.rows[0]]
people = []

for row in ws.rows[1:]:
    values = {}
    for key, cell in zip(header, row):
        values[key] = cell.value
    person = Person(**values)
    people.append(person)


# alternatively if the order is fixed

for row in ws.rows[1:]:
    args = [cell.value for cell in row]
    person = Person(*args)
    people.append(person)

Note that it's good practice not to overwrite Python keywords or use them as attributes to reduce confusion.