这个想法是,首先,将标题读入列表。然后,迭代工作表行(从标题后的下一个开始),基于标题键和适当的单元格值创建新字典,并将其附加到词典列表中:
from xlrd import open_workbook
book = open_workbook('forum.xlsx')
sheet = book.sheet_by_index(3)
# read header values into the list
keys = [sheet.cell(0, col_index).value for col_index in xrange(sheet.ncols)]
dict_list = []
for row_index in xrange(1, sheet.nrows):
d = {keys[col_index]: sheet.cell(row_index, col_index).value
for col_index in xrange(sheet.ncols)}
dict_list.append(d)
print dict_list
对于包含以下内容的工作表:
A B C D
1 2 3 4
5 6 7 8
它打印:
[{'A': 1.0, 'C': 3.0, 'B': 2.0, 'D': 4.0},
{'A': 5.0, 'C': 7.0, 'B': 6.0, 'D': 8.0}]
UPD(扩展字典理解):
d = {}
for col_index in xrange(sheet.ncols):
d[keys[col_index]] = sheet.cell(row_index, col_index).value