Python自动化：Excel根据IP匹配网段获取所属源端口
需求
现在有两个文件：
1. 【NTP.xlsx】：有name、IP、fenzhihang、vendor、source这五列
2. 【IP.xlsx】：有daqu、fenzhihang、duankou、IP、mask、gateway、subnet、yongtu、miaoshu这九列
现在更新基线，每台设备都需要增加ntp source-interface xxx。
需要填写NTP.xlsx的source列，它的值应该是IP.xlsx的duankou的值。
解析
难点：
- 由于环境特殊，不允许自行添加python库，因此我的环境是spyder5.5，python3.8这样一个环境。
- 同时，由于每台设备的IP并非是网段的网关，因此无法直接使用excel的vlookup函数。
- 代码能力偏弱，很难实现。
分析：
- 分析发现，应该可以根据
subnet列的，计算得到所有属于整个网段的主机地址 - 通过匹配NTP.xlsx的IP与IP.xlsx的subnet计算而来的网段范围，可以确认到对应的网段，然后将IP.xlsx的duankou列填写到NTP.xlsx的source列中
提示词
由于代码能力弱，因此对需求分析后，进行提示词编写，交由ChatGPT-4o编写，最终效果实现了需求
现在有两个文件：
- 【NTP.xlsx】：有name、IP、fenzhihang、vendor、source这五列
- 【IP.xlsx】：有daqu、fenzhihang、duankou、IP、mask、gateway、subnet、yongtu、miaoshu这九列
现在我的编译环境是spyder5.5，python3.8，没有办法增加额外的库，请为我编写python代码实现我的需求：
- 仅读取【NTP.xlsx】的IP和source列即可，source列是需要填写数据的列。
- 读取【IP.xlsx】的duankou、subnet列，这两列是用于填写【NTP.xlsx】的数据参考。
- 编写一个函数，处理【IP.xlsx】的subnet列，这一列的数据是x.x.x.x/x的形式，你需要读取/，然后以IP地址段的处理方式，返回属于这个IP地址段的所有地址。例如10.170.131.64/26，你需要返回属于这个地址段范围的所有主机地址10.170.131.65 至 10.170.131.126
- 【NTP.xlsx】的IP的值，去匹配上一步获取到的地址范围，当匹配到这个IP属于某个地址段后，返回【IP.xlsx】中对应的duankou的值并填入【NTP.xlsx】的IP对应的source列中。例如有一个IP是10.170.131.71，它匹配了10.170.131.64/26这个网段，返回对应的duankou列的值为Vlan100
代码
由于没有办法添加库，所以无法使用ipaddress这个python库直接计算网段
通过ChatGPT-4o，进行代码编写，实现了替换这个ipaddress库的依赖
from openpyxl import load_workbookdef ip_to_int(ip):"""将IP地址转换为整数。"""return sum([int(num) << (8 * i) for i, num in enumerate(reversed(ip.split('.')))])def int_to_ip(ip_int):"""将整数转换为IP地址。"""return '.'.join([str((ip_int >> (8 * i)) & 0xFF) for i in range(4)][::-1])def expand_subnet(subnet):"""根据子网返回所有主机地址（排除网络地址和广播地址）。"""if subnet is None:return []ip, cidr = subnet.split('/')cidr = int(cidr)# 将IP转换为整数ip_int = ip_to_int(ip)# 计算子网掩码mask = (1 << 32) - (1 << (32 - cidr))# 计算网络地址和广播地址network_int = ip_int & maskbroadcast_int = network_int | (~mask & 0xFFFFFFFF)# 生成网络地址范围内的所有主机地址（排除网络地址和广播地址）return [int_to_ip(i) for i in range(network_int + 1, broadcast_int)]def fill_ntp_source(ntp_file, ip_file):# 加载Excel文件ntp_wb = load_workbook(ntp_file)ip_wb = load_workbook(ip_file)# 读取相应的工作表ntp_ws = ntp_wb.activeip_ws = ip_wb.active# 创建一个用于存储subnet对应duankou的字典subnet_dict = {}# 遍历IP.xlsx的每一行，读取subnet和duankou列，并生成IP地址列表for row in ip_ws.iter_rows(min_row=2, values_only=True):duankou = row[2] # duankou在第3列subnet = row[6] # subnet在第7列if subnet: # 确保subnet不为空all_hosts = expand_subnet(subnet)for ip in all_hosts:subnet_dict[ip] = duankou# 遍历NTP.xlsx的每一行，读取IP列，匹配subnet_dict中的IP，填充source列for row in ntp_ws.iter_rows(min_row=2):ip = row[1].value # IP在第2列if ip in subnet_dict:row[4].value = subnet_dict[ip] # source在第5列# 保存修改后的NTP.xlsxntp_wb.save(ntp_file)# 使用方法
fill_ntp_source("NTP.xlsx", "IP.xlsx")