excel - 如何将excel行放入列表

问题描述

我有一个带有参数的 excel 表，我试图将其导出到文本板中。

site name    site      site1     site2     site2
host       hostname  hostname1 hostname2 hostname2
ip          x.x.x.x    x.x.x.x  x.x.x.x   x.x.x.x
sh#             1          1        1         2
ILAN in       yes         yes      no        no
ilan out       no          no      no        no

我想要的是获取每个站点/主机的信息并放入一个文本键盘，每个主机名一个文本键盘。在此示例中，有 4 个主机，因此有 4 个文本键盘。

文本键盘名称 site-hostname-SH# 并在文本键盘中：

host = hostname
ip = x.x.x.x 
SH# = 1 or 2
ILAN IN = yes or no
ILAN out = yes or no

我能够找到诸如主机之类的关键字，但无法将该行放入列表中。我正在使用文本键盘作为另一个脚本的全局变量。excel 是信息的来源，我的其他脚本远程登录到设备以进行配置。现在我必须手动更改每个架子的所有变量，并希望自动化 Excel 表中的变量。

import openpyxl
from openpyxl import load_workbook
from openpyxl import Workbook
from Tkinter import Tk
from tkFileDialog import askopenfilename
import sys
import warnings

if not sys.warnoptions:
    warnings.simplefilter("ignore")
#opens filechooser for excel workbook
Tk().withdraw()
filename = askopenfilename()
print ("Working, please be patient")
wb = openpyxl.load_workbook(filename)

#lists worksheets in workbook to pick which worksheet to import
print ("\r")
count = 0
for ws in wb.worksheets:
    tlist = ws.title
    print count,tlist
    count = count + 1

x = input ("\nchoose number:\n")
ws = wb.worksheets[x]
print (ws)

#will eventually put into a function
#finds keyword, in this case site name
for row in ws.iter_rows():
    for cell in row:
        try:
            if cell.value == "Site Name":
                print ("yes")
                print ("TRUE")
                print cell.row, cell.column
                row = cell.row
                column = cell.column
                print column

                site = ws.cell(row = row, column = column)
                print ("this is: %s" %(site.value) )

                print ("this is row %s" %row)
                print row
                print column
        except (AttributeError):
            continue

have tried:
#print ("this is row: %s" %(row) )
##print(row)
#print (SiteName[1])

##x = ws.max_row + 1
##x = int(row)
##for r in range(x, column + 1):
##    d=ws.cell(row=x,column=r)
##    print(d.value)
##print ("this is the row number: %s" %(row) )
##print str(row)
##print ("this is: %s" %(site.value) )
##for i in range(row, col + 1): 
##    cell_obj = ws.cell(row = row, column = i) 
##    print(cell_obj.value)

标签： excelpython-2.7listrow

for row in ws.iter_rows(): keyword = row[0].value values = [item.value for item in row[1:]] if keyword == 'site name': print(keyword) print(values) print('-----\n') # do whatever you want here

excel - 如何将excel行放入列表

问题描述

解决方案

推荐阅读