# coding = 'utf-8'
# 依据excel表格中所提供的IP，在另一张表中查找其所属网段
import pandas as pd
import IPy

df = pd.read_excel('net.xlsx')
col_name = df.columns.tolist()
index = col_name.index('IP地址') + 1
col_name.insert(index, '所属子网')
df = df.reindex(columns=col_name)

data = df["IP地址"]

df2 = pd.read_excel('net.xlsx', sheet_name='Sheet2')
# nets = ["192.168.0/24", "192.168.1.0/24"]
nets = df2["网段"]

new_col = []
tmp_data = ""

# 新建一列保存用于数据，判断某列是否满足一个列表中的条件，满足保存条件，不满足保存自身。
"""
for i in data:
    for net in nets:
        if IPy.IP(net.strip()).overlaps(i.strip()) == 1:
            tmp_data = net.strip()
        elif tmp_data == "" and IPy.IP(net.strip()).overlaps(i.strip()) != 1:
            tmp_data = i.strip()
    new_col.append(tmp_data)
    tmp_data = ""
df["所属子网"] = new_col
print(df)
"""
# 新建一列保存用于数据，判断某列是否满足一个列表中的条件，满足保存条件，不满足保存自身。

tmp_list = []
flag = False
for net in nets:
    for i in data:
        if not flag:
            if IPy.IP(net.strip()).overlaps(i.strip()) == 1:
                tmp_list.append(net.strip())
            else:
                tmp_list.append(i.strip())
        else:
            if IPy.IP(net.strip()).overlaps(i.strip()) == 1:
                tmp_list = [net.strip() if tmp.strip() == i.strip() else tmp.strip() for tmp in tmp_list]
    flag = True
df["所属子网"] = tmp_list

pd.DataFrame(df).to_excel("result_net.xlsx", sheet_name="Sheet1", index=False)