import pandas as pd
from netaddr import *

def ip_guihua():
    ip_host_list_ipv4_1 = []
    ip_host_list_ipv4_2 =[]
    ip_host_list_ipv6_1 = []
    ip_host_list_ipv6_2 = []

    ipv4_result_list = [] # 用于存儲ipv4一組互聯地址元祖
    ipv6_result_list =[] # 用于存儲ipv6一組互聯地址元祖
    df_subnet = pd.read_excel("subnet.xlsx")
    df_subnet_dic = df_subnet.to_dict("list")
    subnet_ipv4_list = df_subnet_dic["IPV4"]
    subnet_ipv6_list = df_subnet_dic["IPV6"]
    for subnet_v4 in subnet_ipv4_list:
        try:
            net4 = IPNetwork (subnet_v4)
            net4_host_1 = net4[1].format()
            net4_host_2 = net4 [-2].format()
            ip_host_list_ipv4_1.append(net4_host_1)
            ip_host_list_ipv4_2.append(net4_host_2)

        except TypeError:
            print("IPV4地址不對")
    for subnet_ipv6 in subnet_ipv6_list:
        try:
            net6 = IPNetwork(subnet_ipv6)
            net6_host_1 = net6[1].format()
            net6_host_2 = net6[-2].format()
            ip_host_list_ipv6_1.append(net6_host_1)
            ip_host_list_ipv6_2.append(net6_host_2)
        except TypeError:
            print("IPV6地址不對")
    #print(ip_host_list_ipv4_1)
    #print(ip_host_list_ipv4_2)
    #print(ip_host_list_ipv6_1)
    #print(ip_host_list_ipv6_2)
    ip_host4_zip = zip (ip_host_list_ipv4_1,ip_host_list_ipv4_2)
    ip_host6_zip = zip (ip_host_list_ipv6_1,ip_host_list_ipv6_2)
    for ip_host in ip_host4_zip:
        ipv4_result_list.append(ip_host)
    for ip_host in ip_host6_zip:
        ipv6_result_list.append(ip_host)
    df_ip_host_v4 = pd.DataFrame(data=ipv4_result_list,columns=["ipv4-1","ipv4-2"])
    df_ip_host_v6 = pd.DataFrame(data=ipv6_result_list,columns=["ipv6-1","ipv6-2"])
    #print(df_ip_host_v4)
    #print(df_ip_host_v6)
    df_result = pd.concat([df_ip_host_v4,df_ip_host_v6],axis=1,join="outer")
    print(df_result)
    with pd.ExcelWriter("result.xlsx",mode="w") as writer:
        df_result.to_excel(writer)
if __name__ == '__main__':
    ip_guihua()