读取和检查模块

import xlrd
from django.core.exceptions import MultipleObjectsReturned
from django.db.models import Model

from dbbackend.models import ZJcell, LngLat, ZJenb


class Cellinforeader:
    """读取excel的类"""

    def __init__(self, path=r'C:\Users\Administrator\Desktop\嘉兴工参\圈定区域网内邻区关系(1).xlsx', sheetname='圈定小区'):
        try:
            self.workbook = xlrd.open_workbook(path)
            self.sheet = self.workbook.sheet_by_name(sheetname)
        except Exception:
            print(Exception)
        # 列名与列号字典，例如{'小区名称':2}
        self.col_dict = {}
        self.mismatchedcgi = dict()
        self.mismatchedcgi_t = dict()
        self.mismatchedrowid = []
        # sheet.ncols:  Nominal number of columns in sheet.
        for i in range(self.sheet.ncols):
            self.col_dict[self.alltypestrip(self.sheet.cell_value(0, i))] = i
            # ' abc '.strip()去除字符串前后的空格

    # 提取每行数据，导入小区数据库ZJcell中
    def insertcells(self, cgi='CGI', cellname='小区名称', freq='频段', pci='PCID', lng='基站经度', lat='基站纬度',
                    azimuth='扇区方位角', enbname='基站名称', enbid='LTEENODEBID'):
        insertcount = 0
        for i in range(1, self.sheet.nrows):
            row = self.sheet.row_values(i)
            # 下面啰嗦了这么多行，无非是要实现两个功能，一是根据列名寻找数据，避免根据列的位置寻找数据时格式不能改
            # 二是利用strip函数将字符串前后的空格删除，避免基站名后面带空格的情况，这里利用的小技巧有：
            # 1、把列名和列号做成dict，这样就可直接通过列名取列号，比循环查找函数省时间
            # 2、alltypestrip()函数利用了python三目运算
            # 3、因为变量名重复，所以在结果的变量名前加下划线 _ ，加以区分
            # 4、这么多重复的写法，看似可以使用循环来解决，但那样反而复杂，不如使用相同的格式列出来，以后看着清晰，
            #    相当于配置的赋值都应该这样写
            _enbname = self.alltypestrip(row[self.col_dict[enbname]])
            _enbid = self.alltypestrip(row[self.col_dict[enbid]])
            _lng = self.alltypestrip(row[self.col_dict[lng]])
            _lat = self.alltypestrip(row[self.col_dict[lat]])
            _cgi = self.alltypestrip(row[self.col_dict[cgi]])
            _cellname = self.alltypestrip(row[self.col_dict[cellname]])
            _freq = self.alltypestrip(row[self.col_dict[freq]])
            _pci = self.alltypestrip(row[self.col_dict[pci]])
            _azimuth = self.alltypestrip(row[self.col_dict[azimuth]])

            enb, createdornot = ZJenb.objects.get_or_create(enbname=_enbname, enbid=_enbid)
            lnglat, createdornot1 = LngLat.objects.get_or_create(lng=_lng, lat=_lat)
            cell, createdornot2 = ZJcell.objects.get_or_create(cgi=_cgi, cellname=_cellname, freq=_freq, pci=_pci,
                                                               azimuth=_azimuth, lnglat=lnglat, enb=enb)
            # ' abc '.strip()去除字符串前后的空格

            # get_or_create函数返回元组的第二个数据表示是否新创建了一条数据
            if createdornot2:
                insertcount += 1
        print("导入" + str(insertcount) + "条数据")

    # 对enbid 'float' object使用strip函数时报错，所以增加这个函数，使用strip前判断一下是不是str类型。
    @staticmethod
    def alltypestrip(args):
        return args.strip() if type(args) is str else args

    # 插入邻区关系
    def insertadj(self, scgi='本地小区CGI', tcgi='邻小区CGI'):
        insertcount = 0
        for i in range(1, self.sheet.nrows):
            row = self.sheet.row_values(i)
            _scgi = self.alltypestrip(row[self.col_dict[scgi]])
            _tcgi = self.alltypestrip(row[self.col_dict[tcgi]])

            source = None
            try:
                source = ZJcell.objects.get(cgi__exact=_scgi)
            except (ZJcell.DoesNotExist, MultipleObjectsReturned):
                print('邻区表中的源小区cgi在小区表中不存在......:' + _scgi)
                self.mismatchedcgi.update({str(i): _scgi})
            target = None
            try:
                target = ZJcell.objects.get(cgi__exact=_tcgi)
            except (ZJcell.DoesNotExist, MultipleObjectsReturned):
                print('邻区表中的目标小区cgi在小区表中不存在....:' + _tcgi)
                self.mismatchedcgi_t.update({str(i): _tcgi})

            # 如果source和target中有一个为None，则不能添加邻区关系
            if source and target:
                insertcount += 1
                try:
                    source.adj.add(target)
                except Exception:
                    print(Exception)
                    insertcount -= 1
            else:
                self.mismatchedrowid.append(i)
        print('共添加' + str(insertcount) + '条邻区')

    # 将所有不匹配的邻区中的cgi打印出来
    def xlwtmismatchedrow(self):
        pass


class Checkpci:
    """检查pci冲突与混淆，得出可用pci"""
    # cgilist格式：['460-00-325632-129','460-00-325632-131'] D1 331 D1 332
    # freqpcilist格式：[{'cellname': 'Z737423嘉兴线务局LY建委大楼WZD_129', 'freq': 'D1', 'pci': 341},
    #                   {'cellname': 'Z730485嘉兴纺织大厦D_1', 'freq': 'D1', 'pci': 115}]
    # def __init__(self, cgilist=None, freqpcilist=None):
    def __init__(self, freqlist=None, mod3list=None):
        if not freqlist:
            freqlist = ['F1', 'F2', 'D1', 'D2', 'D3']
        if not mod3list:
            mod3list = [0, 1, 2]
        self.freqpcimatrix = dict()
        for f in freqlist:
            self.freqpcimatrix[f] = [i for i in range(504) if i % 3 in mod3list]

    # 根据输入的频点和PCI，从初始化freqpcimatrix频点PCI矩阵中剔除相应频点的PCI
    def delfreqpci(self, freqpcilist=None):
        if freqpcilist:
            for freq, pci in freqpcilist:
                try:
                    self.freqpcimatrix.get(freq).remove(pci)
                except (AttributeError, ValueError):
                    print('预置的频点PCI矩阵中没有' + freq + '频点，或PCI' + str(pci))

    # 根据输入的小区cgi列表，查询对应的小区频点和PCI，从初始化频点PCI矩阵中剔除
    def delbycgi(self, cgilist=None, cellnamelist=None):
        freqpcilist = None
        if cgilist:
            try:
                freqpcilist = ZJcell.objects.filter(cgi__in=cgilist).values_list("freq", "pci")
            except TypeError:
                print('cgilist参数为空，或者格式不对')
        elif cellnamelist:
            try:
                freqpcilist = ZJcell.objects.filter(cellname__in=cellnamelist).values_list("freq", "pci")
            except TypeError:
                print('cellnamelist参数为空，或者格式不对')
        if freqpcilist:
            self.delfreqpci(freqpcilist=freqpcilist)

    # 根据输入的小区，查找该小区邻区表，将这个小区及其邻区的频点PCI从列表中剔除。
    def delbycelladj(self, cgi=None, cellname=None):
        source = None
        freqpcilist = None
        if cgi:
            try:
                source = ZJcell.objects.get(cgi__exact=cgi)
            except (ZJcell.DoesNotExist, MultipleObjectsReturned):
                print('邻区表中的源小区cgi在小区表中不存在......:' + cgi)
        elif cellname:
            try:
                source = ZJcell.objects.get(cellname__exact=cellname)
            except (ZJcell.DoesNotExist, MultipleObjectsReturned):
                print('邻区表中的源小区cgi在小区表中不存在......:' + cgi)
        if source:
            freqpcilist = [(source.freq, source.pci)]
            adjfreqpcilist = source.adj.values_list('freq', 'pci')
            if adjfreqpcilist:
                freqpcilist.extend(adjfreqpcilist)
        if freqpcilist:
            self.delfreqpci(freqpcilist=freqpcilist)
        '''
        self.freqpcimatrix = {'D1': [i for i in range(504) if i%3 in mod3list], 'D2': [i for i in range(504)],
                              'D3': [i for i in range(504)], 'F1': [i for i in range(504)],
                              'F2': [i for i in range(504)]}
        self.freqpcilist = list()
        try:
            self.freqpcilist = freqpcilist.copy()
        except AttributeError:
            print('freqpcilist参数为空，或者格式不对')

        try:
            qlist = ZJcell.objects.filter(cgi__in=cgilist).values("cellname", "freq", "pci")
            self.freqpcilist.extend(qlist)
        except TypeError:
            print('cgilist参数为空，或者格式不对')
        '''
    '''    
    # 将参数中传入的“频点pci”与初始化时给定cgi或“频点pci”列表对比，看是否存在相同的，有相同为True，否则为False
    def comparepci(self, freq='D1', pci=0):
        for freqpci1 in self.freqpcilist:
            if freqpci1['freq'] is freq and freqpci1['pci'] is pci:
                return True
        return False
    '''