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的值。

解析

难点：

1. 由于环境特殊，不允许自行添加python库，因此我的环境是spyder5.5，python3.8这样一个环境。
2. 同时，由于每台设备的IP并非是网段的网关，因此无法直接使用excel的vlookup函数。
3. 代码能力偏弱，很难实现。

分析：

1. 分析发现，应该可以根据subnet列的，计算得到所有属于整个网段的主机地址
2. 通过匹配NTP.xlsx的IP与IP.xlsx的subnet计算而来的网段范围，可以确认到对应的网段，然后将IP.xlsx的duankou列填写到NTP.xlsx的source列中

提示词

由于代码能力弱，因此对需求分析后，进行提示词编写，交由ChatGPT-4o编写，最终效果实现了需求

现在有两个文件：

1. 【NTP.xlsx】：有name、IP、fenzhihang、vendor、source这五列
2. 【IP.xlsx】：有daqu、fenzhihang、duankou、IP、mask、gateway、subnet、yongtu、miaoshu这九列

现在我的编译环境是spyder5.5，python3.8，没有办法增加额外的库，请为我编写python代码实现我的需求：

1. 仅读取【NTP.xlsx】的IP和source列即可，source列是需要填写数据的列。
2. 读取【IP.xlsx】的duankou、subnet列，这两列是用于填写【NTP.xlsx】的数据参考。
3. 编写一个函数，处理【IP.xlsx】的subnet列，这一列的数据是x.x.x.x/x的形式，你需要读取/，然后以IP地址段的处理方式，返回属于这个IP地址段的所有地址。例如10.170.131.64/26，你需要返回属于这个地址段范围的所有主机地址10.170.131.65 至 10.170.131.126
4. 【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_workbook

def 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 & mask
    broadcast_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.active
    ip_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.xlsx
    ntp_wb.save(ntp_file)

# 使用方法
fill_ntp_source("NTP.xlsx", "IP.xlsx")