作为网工经常遇到在某些文件中查找指定的一个或多个配置，并提取出来到excel中。
举例：根据excel中某列内容作为文件名匹配条件，到文件夹中匹配到该文本文件，读取文件按照正则匹配到所需内容，回填到excel中指定列。
import os
import re
import sys
import xlwings as xlw
import pandas as pd
input_wb = None
work_sheet = None
row_count = 0
# 打开Excel程序，APP不可见，屏幕更新关闭
app = xlw.App(visible=False, add_book=False)
# Excel工作簿显示警告,不显示
app.display_alerts = False
# 工作簿屏幕更新,不更新
app.screen_updating = False
#
# 参数：re.I 使匹配对大小写不敏感, re.M 多行匹配，影响 ^ 和 $, re.S 匹配包括换行在内的所有字符
#
xls_file = "FBB Rezone.xlsx"
xls_file_sheet = "all"
# 打开excel文件
input_file_path = os.path.join(os.getcwd(), xls_file)
#
if os.path.exists(input_file_path):
input_wb = app.books.open(input_file_path)
# 获取输入活动页sheet
work_sheet = input_wb.sheets[xls_file_sheet]
# 获取输入文件的行数
row_count = work_sheet.range('A1').current_region.last_cell.row
#
# 根据FBB业务VLAN和当前归属BNG的NPE配置(dot1q 子接口、接口绑定的vsi、vsi配置的peer pw)
# 找到NPE的所有AC接口，VSI的配置，和2个UPE的IP地址
#
# for i in range(2, row_count+1):
for i in range(2, row_count+1):
# 取网元名称
# chain = work_sheet.range("C" + str(i)).value
# c = list(chain)
# c.pop(0)
# chain_num = ''.join(c)
#
re_eth_trunk = re.compile('^#$\n^interface Eth-Trunk([^\n])*$\n', re.I | re.S | re.M)
re_l2_bind_vsi = re.compile('^ l2 binding vsi ([^\n])*$\n', re.I | re.S | re.M)
re_peer_ip = re.compile('^ peer ([^\n])* ignore-standby-state$\n', re.I | re.S | re.M)
re_vsi_id = re.compile('^ vsi-id ([0-9])+([^\n])*$\n', re.I | re.S | re.M)
# 获取D列的vlan id, float 类型取整(int())，后转为str类型
fbb_vlan = str(int(work_sheet.range("F" + str(i)).value))
# ################查找NPE01########################
#
# 定义输出的查找到的接口
match_interface_list = ''
match_vsi_name = ''
match_vsi_conf = ''
match_peer_ip_list = ''
match_vsi_id = ''
#
# 获取需要搜索网元的文件名01，即C列值
#
npe_name = work_sheet.range("C" + str(i)).value
# 正则 匹配要搜索的文件名
s = '.*_' + npe_name + '\.cfg'
re_npe_name = re.compile(s, re.I | re.S)
#
for root, dirs, files in os.walk(os.path.join(os.getcwd(), "AGG")):
# print("当前目录：", root)
# print("子目录列表：", dirs)
# print("文件列表：", files)
#
# 遍历文件夹内文件，找到cfg文件
#
if any((match := re_npe_name.search(item)) for item in files):
#
# 获取文件路径
cfg_file = os.path.join(root, match.group(0))
# print('Search File Name: ' + match.group(0))
#
# 打开文件 只读模式
f = open(cfg_file, 'r')
#
# 读取所有行
lines = f.read()
#
# 匹配查找所有AC Eth-Trunk子接口的配置脚本
#
s = '^#$\n' + '^interface Eth-Trunk([^\n])*$\n' + '^ vlan-type dot1q ' + fbb_vlan + '$\n' + '(^([^#])*$\n)*' + '^#$\n'
re_ac_interface = re.compile(s, re.I | re.S | re.M)
m = re.finditer(re_ac_interface, lines)
# 此处判断m，是因为 re.finditer 即使匹配失败返回也不是None空，所以不能写成 if m is None:
if not m:
print(str(i) + ' Fail Match vlan-type dot1q: ' + fbb_vlan + ' ' + cfg_file)
continue
else:
#
# 匹配到多个AC Eth-Trunk子接口
#
for x in m:
# re_eth_trunk.search(interface_script)
#
# 在Eth-Trunk子接口配置里面，去匹配绑定的vsi
#
v = re_l2_bind_vsi.search(x.group(0))
if v is None:
print('Match l2 binding vsi Fail! :\n' + x.group(0))
continue
else:
match_interface_list = match_interface_list + x.group(0)
match_vsi_name = v.group(0).replace(' l2 binding vsi ', '').replace('\n', '')
#
# 根据vsi的名字，匹配vsi的配置
#
s = '^#$\n' + '^vsi ' + match_vsi_name + '( static)?$\n' + '(^([^#])*$\n)+' + '^#$\n'
re_vsi_conf = re.compile(s, re.I | re.S | re.M)
v = re_vsi_conf.search(lines)
if v is None:
print('Match vsi Fail! :\n' + match_vsi_name)
continue
else:
match_vsi_conf = match_vsi_conf + v.group(0)
#
# 根据vsi配置，匹配多个peer pw的ip地址
#
p = re.finditer(re_peer_ip, match_vsi_conf)
if not p:
print('Match peer ip Fail! :\n' + match_vsi_conf)
continue
else:
for x in p:
ip = x.group(0).replace(' peer ', '').replace(' ignore-standby-state', '')
ip = ip.replace('\n', '')
if not ip == work_sheet.range("K" + str(i)).value:
match_peer_ip_list = match_peer_ip_list + ip
#
# 根据vsi配置，匹配vsi id
#
d = re.finditer(re_vsi_id, match_vsi_conf)
if not d:
print('Match vsi id Fail! :\n' + match_vsi_conf)
continue
else:
for x in d:
vid = x.group(0).replace(' vsi-id ', '')
vid = vid.replace('\n', '')
match_vsi_id = match_vsi_id + vid
# 关闭文件
f.close()
work_sheet.range("M" + str(i)).value = match_peer_ip_list
work_sheet.range("N" + str(i)).value = match_interface_list
work_sheet.range("O" + str(i)).value = match_vsi_conf
work_sheet.range("P" + str(i)).value = match_vsi_name
work_sheet.range("Q" + str(i)).value = match_vsi_id
# 在UPE1中匹配
#
match_vsi_conf = ''
match_vsi_id = ''
upe_name = ''
# vsi id
if not work_sheet.range("Q" + str(i)).value is None:
match_vsi_id = str(int(work_sheet.range("Q" + str(i)).value))
else:
continue
#
# UPE ne name
if not work_sheet.range("J" + str(i)).value is None:
upe_name = work_sheet.range("J" + str(i)).value
else:
continue
# 正则 匹配要搜索的文件名
s = '.*_' + upe_name + '\.cfg'
re_upe_name = re.compile(s, re.I | re.S)
#
for root, dirs, files in os.walk(os.path.join(os.getcwd(), "AGG")):
# print("当前目录：", root)
# print("子目录列表：", dirs)
# print("文件列表：", files)
#
# 遍历文件夹内文件，找到cfg文件
#
if any((match := re_upe_name.search(item)) for item in files):
#
# 获取文件路径
cfg_file = os.path.join(root, match.group(0))
# print('Search File Name: ' + match.group(0))
#
# 打开文件 只读模式
f = open(cfg_file, 'r')
#
# 读取所有行
lines = f.read()
#
# 匹配查找vsi配置
#
# print(match_vsi_id)
# print(upe_name)
s = '^#$\n' + '^vsi ([^\n])+$\n' + '(^[^\n]*[$\n]?)?' + '^ pwsignal ldp$\n' + '^ vsi-id ' + match_vsi_id + '$\n' + '(^([^#])*$\n)*' + '^#$\n'
re_vsi_conf = re.compile(s, re.I | re.S | re.M)
v = re_vsi_conf.search(lines)
#
if v is None:
print(str(i) + ' Fail Match vsi id: ' + match_vsi_id + ' ' + upe_name)
continue
else:
#
# 匹配到vsi配置
#
match_vsi_conf = match_vsi_conf + v.group(0)
# 关闭文件
f.close()
work_sheet.range("V" + str(i)).value = match_vsi_conf
#
#
# ##
# 根据UPE的vsi配置，匹配出vsi的保护组名称
# ##
#
#
# 在UPE1中匹配
#
upe_vsi_conf = ''
match_upe_vsi_name = ''
match_upe_protect_group = ''
# vsi id
if not work_sheet.range("V" + str(i)).value is None:
upe_vsi_conf = str(work_sheet.range("V" + str(i)).value)
else:
continue
#
#
# 匹配查找vsi名称
#
s = '^vsi ([^\n])+$\n'
re_vsi_name = re.compile(s, re.I | re.S | re.M)
v = re_vsi_name.search(upe_vsi_conf)
#
if v is None:
print(str(i) + ' Fail Match vsi name: ' + upe_vsi_conf)
continue
else:
#
# 匹配到vsi的名称
#
match_upe_vsi_name = match_upe_vsi_name + v.group(0).replace('\n', '')
#
# 匹配查找vsi下保护组名称
#
s = '^ protect-group ([^\n])+$\n'
re_vsi_protect_group = re.compile(s, re.I | re.S | re.M)
v = re_vsi_protect_group.search(upe_vsi_conf)
#
if v is None:
print(str(i) + ' Fail Match vsi protect group: ' + upe_vsi_conf)
continue
else:
#
# 匹配到vsi的保护组名称
#
match_upe_protect_group = match_upe_protect_group + v.group(0).replace('\n', '')
work_sheet.range("AL" + str(i)).value = match_upe_vsi_name
work_sheet.range("AM" + str(i)).value = match_upe_protect_group
#
# 设置列宽 行高
work_sheet.range("A1").expand(mode='table').autofit()
work_sheet.range("A1").expand(mode='table').row_height = 14.3
input_wb.save(input_file_path)
input_wb.close()
#
app.quit()
扫一扫
280
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
