#WorkBuddy# 实战：我把融合IP网络的 IP 规划从 8 小时手工填表，压到了 3 分钟

表2.3 站点设备清单（站点/设备名称/设备角色/接入环/区域）
        │
        ├─→ 拓扑推导：环内互联 → 汇聚上联核心 → 核心间全互联
        │
        ├─→ 地址分配：Loopback v4 / Loopback v6 / 互联 /30 / SRv6 Locator
        │
        ├─→ 冲突校验：重复检测 + 越界检测 + 地址池容量检测
        │
        └─→ 回填输出：2.4 / 2.5 / 2.6 / 2.7 + 校验报告

PLAN = {
    "area_id": 10,                      # 区域编号，参与拼装 IPv4 第二段与 IPv6 区域位
    "loopback_v4": "10.10.0.0/16",      # IPv4 Loopback 地址池
    "loopback_v4_start": 1,             # 起始主机位
    "loopback_v6": "fd00:a::/32",       # IPv6 Loopback 地址池
    "link_v4": "10.10.200.0/24",        # 设备互联地址池，按 /30 划分
    "srv6_locator": "2001:db8:a::/48",  # SRv6 Locator 前缀
    "uplink_mask": 30,                  # 互联链路掩码
}

python -m venv .venv
.venv\Scripts\activate        # Linux/macOS 用 source .venv/bin/activate
pip install openpyxl

HEADER_ALIAS = {
    "site": ["站点", "站点名称", "机房", "所属站点"],
    "name": ["设备名称", "网元名称", "设备", "NE名称"],
    "role": ["设备角色", "角色", "层级", "网络层次"],
    "ring": ["接入环", "所属环", "环", "环网"],
    "area": ["区域", "区域编号", "所属区域"],
}

def _match_header(cell_value):
    """按别名长度降序匹配，避免「设备名称」被短别名「设备」抢先命中"""
    v = re.sub(r"\s+", "", str(cell_value)) if cell_value is not None else ""
    flat = [(a, std) for std, aliases in HEADER_ALIAS.items() for a in aliases]
    for alias, std in sorted(flat, key=lambda x: -len(x[0])):
        if alias in v:
            return std
    return None

def build_topology(devices):
    links, seen = [], set()
    rings = defaultdict(list)
    for d in devices:
        if d.ring:
            rings[d.ring].append(d)

    for ring, devs in rings.items():          # 环内互联，3 个点以上闭合
        devs = sorted(devs, key=lambda x: x.name)
        pairs = list(zip(devs, devs[1:]))
        if len(devs) >= 3:
            pairs.append((devs[-1], devs[0]))
        for a, b in pairs:
            key = frozenset((a.name, b.name))
            if key not in seen:
                seen.add(key)
                links.append(Link("接入环", ring, a, b))

    cores = [d for d in devices if d.role == "核心"]
    aggs = [d for d in devices if d.role == "汇聚"]
    for ag in aggs:                            # 汇聚双上联核心
        for c in cores:
            key = frozenset((ag.name, c.name))
            if key not in seen:
                seen.add(key)
                links.append(Link("上联", f"{ag.ring or ag.site}-上联", ag, c))
    for a, b in combinations(sorted(cores, key=lambda x: x.name), 2):
        links.append(Link("核心互联", "核心", a, b))
    return links

def allocate(devices, links):
    lo4_pool = ipaddress.ip_network(PLAN["loopback_v4"])
    lo6_net  = ipaddress.ip_network(PLAN["loopback_v6"])
    link_pool = ipaddress.ip_network(PLAN["link_v4"])
    loc_net  = ipaddress.ip_network(PLAN["srv6_locator"])

    for i, d in enumerate(devices, start=PLAN["loopback_v4_start"]):
        d.idx = i
        d.lo_v4 = f"{lo4_pool.network_address + i}/32"
        d.lo_v6 = f"{lo6_net.network_address + i}/128"
        # Locator：/48 前缀 + 16bit 节点号 → /64；End SID 固定 function ::1
        d.locator = f"{loc_net.network_address + (i << 64)}/64"
        d.end_sid = str(ipaddress.ip_network(d.locator, strict=False).network_address + 1)

    subnets = list(link_pool.subnets(new_prefix=PLAN["uplink_mask"]))
    if len(links) > len(subnets):
        raise RuntimeError(f"互联地址池 {link_pool} 仅可划分 {len(subnets)} 段 /30，实际需要 {len(links)} 段")

    for n, lk in enumerate(links):
        net = subnets[n]
        hosts = list(net.hosts())          # /30 只有两个可用地址，恰好一端一个
        lk.net, lk.a_ip, lk.b_ip = str(net), f"{hosts[0]}/30", f"{hosts[1]}/30"
        lk.a_if, lk.b_if = lk.a.next_iface(), lk.b.next_iface()

def write_sheet(wb, title, headers, rows, widths=None):
    ws = wb.create_sheet(title)
    ws.append(headers)
    for c in ws[1]:
        c.fill, c.font = HEAD_FILL, HEAD_FONT
        c.alignment, c.border = Alignment(horizontal="center"), BORDER
    for r in rows:
        ws.append(r)
    for i, w in enumerate(widths or [16] * len(headers), start=1):
        ws.column_dimensions[get_column_letter(i)].width = w
    ws.freeze_panes = "A2"
    return ws

def validate(devices, links):
    issues = []
    # 1. Loopback / Locator 全局唯一性
    # 2. 地址是否落在预设地址池内（越界检测）
    # 3. 每条链路的两端地址是否真属于该 /30 子网
    return issues

$ python ip_plan.py --demo
[demo] 已生成示例基线：表2.3_站点设备清单_示例.xlsx
设备 10 台 / 链路 13 条
校验：全部通过
已输出：切片网络规划_已填充.xlsx

# 跑通示例
python ip_plan.py --demo

# 用真实模板
python ip_plan.py -i 切片网络规划模板.xlsx -o 切片网络规划_已填充.xlsx