excel - 使用 openpyxl 使用电子表格的行创建对象列表

Question

我在 Excel 中有一个表格，如下所示:

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 

我想从表中读取每一行(不包括标题)并将每一行转换为“Person”类的实例。

为了做到这一点，我试图将每一行读入字典，以“id”为键。我正在使用“openpyxl”从 Excel 文件中读取。一旦我能够将 Excel 文件中的数据存储到字典中，我计划创建“Person”类的实例——尽管我不确定这是最好的方法......

这是我到目前为止的代码:

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)

我收到以下错误消息:

AttributeError: 'int' 对象没有属性 'upper'

我认为这可能是一个索引错误，但我试图解决它但没有运气。

预先感谢您的帮助!

Answer 1

我认为以下应该做你想要的。

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)

请注意，最好不要覆盖 Python 关键字或将它们用作属性以减少混淆。