使用表名称作为键从Python中的Excel Workbook创建字典

Question

我在工作簿中有多个excexlels表，名称为sheet1，sheep2，sheep3，我将每个纸中的数据转换为使用标题作为键的字典。但是现在，我想创建一个嵌套的词典，在其中将表名称作为钥匙添加到上述词典中。我的桌子有多个形式的纸：Sheet1

IP Address     prof     type
xx.xx.xx       abc      xyz
xx.xx.xx       efg      ijk

sheet2

IP Address     prof     type
xx.xx.xx       abc      xyz
xx.xx.xx       efg      ijk

现在我尝试过这样的尝试：

from xlrd import open_workbook
book = open_workbook('workbook.xls')
sheet = book.sheet_by_index(0)
sheet_names = book.sheet_names()
# reading header values 
keys = [sheet.cell(0, col_index).value for col_index in range(sheet.ncols)]
dict_list = []
for row_index in range(1, sheet.nrows):
    d = {keys[col_index]: sheet.cell(row_index, col_index).value
         for col_index in range(sheet.ncols)}
    dict_list.append(d)
print (dict_list)

打印以下内容：

[{'IP Address': 'xx.xx.xx', 'prof': 'abc', 'type': 'xyz'}, {'IP Address': 
'xx.xx.xx', 'prof': 'efg', 'type': 'ijk'}]
what I need is :
    [{'Sheet1':{'IP Address': 'xx.xx.xx', 'prof': 'abc', 'type': 'xyz'}, {'IP 
    Address': 'xx.xx.xx', 'prof': 'efg', 'type': 'ijk'}},
    {'Sheet2':{'IP Address': 'xx.xx.xx', 'prof': 'abc', 'type': 'xyz'}, {'IP 
      Address': 'xx.xx.xx', 'prof': 'efg', 'type': 'ijk'}},
       ]

我在工作簿中的多张纸中添加表名称作为键。

任何帮助将不胜感激。

Answer 1

from xlrd import open_workbook
book = open_workbook('Workbook1.xlsx')
pointSheets = book.sheet_names()
full_dict = {}
for i in pointSheets:
    # reading header values 
    sheet = book.sheet_by_name(i)
    keys = [sheet.cell(0, col_index).value for col_index in range(sheet.ncols)]
    dict_list = []
    for row_index in range(1, sheet.nrows):
        d = {keys[col_index]: sheet.cell(row_index, col_index).value
             for col_index in range(sheet.ncols)}
        dict_list.append(d)
    full_dict[i] = dict_list
    dict_list = {}
print (full_dict)

此代码在每个表格上迭代，并将其附加到``full_dict´sheep_name之后''，然后是您的代码已经返回的每个表。名称的含水是指"如何使用xlrd中的python获取excel表名"