比较Excel单元格中的值 [英] Comparing values in Excel cells
问题描述
我有两本工作簿:
- wkbk1在特定列的每个单元格中都有一个IP地址.
- wkbk2没有IP地址,一个IP或多个IP在特定的每个单元格中用新行(相对于逗号)分隔列.
我正在尝试将wkbk1中的单元格值与wkbk2中的值进行比较.
I am trying to compare the value of cell in wkbk1 to the values in wkbk2.
我遇到的问题是,搜索将比较wkbk1 IP = 10.10.10.16等于wkbk2 IP = 10.10.10.168(以及其他任何变体).
The problem I am having is that the search will compare a wkbk1 IP = 10.10.10.16 as equal to wkbk2 IP = 10.10.10.168 (and any other variant).
如果我通过(wkbk1 IP + \ n)搜索,它将无法比较单个行单元格.
If I search by (wkbk1 IP + \n), it fails to compare single line cells.
以下是我的代码:
#variable top store the highest row number
mRow = str(mapIP.get_highest_row())
eRow = str(assetSheet.get_highest_row())
i = 2 #variable for row number output, skips the first row (b/c it is the header row, duh)
#create data by comparing IP in map to IP in CMDB
for mapIpRow in mapIP['A1':'A' + mRow]:
for mapIpCell in mapIpRow:
for assetIpRow in assetSheet['E1':'E' + eRow]:
for assetIpCell in assetIpRow:
assetIp = str(assetIpCell.value)
mapIp = str(mapIpCell.value)
if mapIp in assetIp:
outSheet['A' + str(i)].value = mapIp
print(mapIp) #just for feedback that the program is running
dnsM = mapIP['B' + str(mapIpCell.row)].value
owner = assetSheet['F' + str(assetIpCell.row)].value
dnsQ = assetSheet['B' + str(assetIpCell.row)].value #cishort
dnsQ2 = assetSheet['C' + str(assetIpCell.row)].value #cialias
dnsQ3 = assetSheet['D' + str(assetIpCell.row)].value #ciDesc
ciIP = assetSheet['E' + str(assetIpCell.row)].value #ciIP
ciID = assetSheet['A' + str(assetIpCell.row)].value #ciID
outSheet['B' + str(i)].value = dnsM
outSheet['C' + str(i)].value = owner
outSheet['D' + str(i)].value = dnsQ
outSheet['E' + str(i)].value = dnsQ2
outSheet['F' + str(i)].value = dnsQ3
outSheet['G' + str(i)].value = ciIP
outSheet['H' + str(i)].value = ciID
print owner #just for feedback that the program is running
i = i + 1
else:
pass
推荐答案
获取每个单元格值(可能包含0、1或多个IP),然后使用 .split('\ n')
.您的列表比较如果assetIp中的mapIp
应该仍然有效.
Take each cell value (which may contain 0, 1, or multiple IPs) and put it into a list using .split('\n')
. Your list comparison if mapIp in assetIp
should still work.
assetIp = assetIpCell.value.split('\n') if assetIpCell.value else ''
mapIp = str(mapIpCell.value)
if mapIp in assetIp:
这应该可以解决您的问题:
This should resolve your problem:
我遇到的问题是,搜索将比较wkbk1 IP = 10.10.10.16等于wkbk2 IP = 10.10.10.168(以及其他任何变体).
The problem I am having is that the search will compare a wkbk1 IP = 10.10.10.16 as equal to wkbk2 IP = 10.10.10.168 (and any other variant).
比较字符串时的 in
运算符将返回子字符串结果,即:
The in
operator when comparing strings will return substring results, i.e.:
'a' in 'David' == True
'10.10.10.16` in `10.10.10.168` == True
仅当列表中存在完全匹配项时,与列表一起使用的 in
运算符才返回 True
;
The in
operator when used with a list will return True
only if an exact match exists in the list;
'10.10.10.16`' in ['10.52.1.1', '99.32.9.0', '168.152.1.1', `10.10.10.168`] == False
这篇关于比较Excel单元格中的值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!