wiss

from ipaddress import ip_network, ip_address

from openpyxl import load_workbook


def get_hosts_adresses(line):
    ip = line[2].value
    mask = line[3].value
    try:
        cidr = sum(bin(int(x)).count('1') for x in mask.split('.'))
        full_ip_address = ip + '/' + str(cidr)
        net4 = ip_network(full_ip_address, False)
        return net4.hosts()

    except ValueError as e:
        print("The string you suplied is not a valid ip address :: {}".format(e))
        return None

def ip_in_hosts(line, hosts):
    try:
        ip = ip_address(line[3].value)
        return ip in hosts
    except ValueError as e:
        print("The string you suplied is not a valid ip address :: {}".format(e))
        return False

if __name__ == "__main__":
    filename = "wiss.xlsx"

    wb = load_workbook(filename)
    msan_sheet = 'Msan'
    ipran_sheet = 'IPRAN-MASAN'
    result_sheet = 'result'

    msan_ranges = wb[msan_sheet]
    ipran_ranges = wb[ipran_sheet]
    result_ranges = wb[result_sheet]

    for msan_line in msan_ranges:
        iprans = []
        last_row = result_ranges.max_row
        host_gen = get_hosts_adresses(msan_line)

        if host_gen:
            host_list = list(host_gen)
            for ipran_line in ipran_ranges:
                if ip_in_hosts(ipran_line, host_list):
                    iprans.append(ipran_line)

            iprans_cells = [item for sublist in iprans for item in sublist]

            print(iprans_cells)
            entry = list(map(lambda x: x.value, list(msan_line) + iprans_cells))
            # #
            print(entry)
            result_ranges.append(entry)
            wb.save("wiss2.xlsx")