如何使用for循环将Excel文件保存到字典中？

Question

这是一个Excel文件，应该在几个字典中读取和保存，其中一个是列表。其他两个工作得很好，但“列表”一个保存不了任何东西。Excel文件如下所示：

woof    jpg js     gif  css png 
0        0  45      0   11  6   
total_time  ip_packets_num  http_packets_num  avg_http_size  packet_num   tcp_packets_num   
76.11243916 395             200               378            1217         395   
srcip           host                     dstip            referer       server      
10.183.195.140  edigitalsurvey.com  108.160.162.38       http://static.bbci.co.uk.css           
                ichef.bbci.co.uk    212.58.244.69        http://static.bbci.co.uk.css           
                notify3.dropbox.com 46.236.9.36          http://static.bbci.co.uk.css           
                sa.bbc.co.uk        77.72.112.168        http://static.bbci.co.uk/.css          
                static.bbci.co.uk   81.23.53.170         http://static.bbci.co.uk.css           
                www.bbc.co.uk       81.23.53.171         http://static.bbci.co.uk.css           
                                                         http://www.bbc.co.uk/      

带列表的词典保存在5行词尾，它的缩写为：

DAlllists={'scrip':[],'dstip':[],'host':[],'referer':[],'server':[]}

我使用的代码是：

for caption in range(len(DAlllists)):
if Dworksheet.cell_value(4,caption)=='srcip':
    for row in range(len(DAlllists['srcip'])):
        DAlllists['srcip'].append(Dworksheet.cell_value(5+row,caption))
if Dworksheet.cell_value(4,caption)=='dstip':
    for row in range(len(DAlllists['dstip'])):
        DAlllists['dstip'].append(Dworksheet.cell_value(5+row,caption))
if Dworksheet.cell_value(4,caption)=='host':
    for row in range(len(DAlllists['host'])):
        DAlllists['host'].append(Dworksheet.cell_value(5+row,caption))
if Dworksheet.cell_value(4,caption)=='referer':
    for row in range(len(DAlllists['referer'])):
        DAlllists['referer'].append(Dworksheet.cell_value(5+row,caption))
if Dworksheet.cell_value(4,caption)=='server':
    for row in range(len(DAlllists['server'])):
        DAlllists['server'].append(Dworksheet.cell_value(5+row,caption))

但是输出似乎没有将任何内容保存到字典中，只是在初始化时给出一个空白字典。 有人有改进代码的想法吗？你知道吗

Answer 1

尝试删除所有代码并使用以下命令：

def read_xc_column(sheet, column, startrow=0):
    row = startrow
    value = sheet.cell_value(row, column)
    answer = []
    while value:
        answer.append(value)
        row = row + 1
        value = sheet.cell_value(row, column)
    return answer

DAlllists = {}
for x in range(5):
   this_col = read_xc_column(Dworksheet, x, 4)
   DAlllists[this_col[0]] = this_col[1:]