主要功能说明:加载61850icd文件,生成模型定义excel表格
天天改来改去,手动整理excel表格烦死,所以写了一个python,自动生成excel表格。
注:不一定适合所有厂家
生成的excel表格内容展示:
import os
import xml.etree.ElementTree as ET
import pandas as pd
import re
print(' ______________________________________________________________________________ ')
print('| |')
print('| Python 3.8.5 (tags/v3.8.5:580fbb0, Jul 20 2020, 15:57:54) on win32 |')
print('| 注1:仅限南网测控模型,其他没测试 |')
print('| 注2:跳过了GOOSE订阅、发布,仅保留上送监控的数据点 |')
print('| 注3:在数据集中的双点遥信,如果没有遥控功能,四遥类别需要手动修改 |')
print('| 注4:随手写的,不关注条理性、效率 |')
print('|______________________________________________________________________________|\n\n')
os.system('pause')
icdFilePath = '测控装置模型.icd'
(file, ext) = os.path.splitext(icdFilePath)
excelPath = file+'.xlsx'
print(icdFilePath)
print(excelPath)
ET.register_namespace('','http://www.iec.ch/61850/2003/SCL')
tree = ET.ElementTree(file = icdFilePath)
######<打开excel>
writer=pd.ExcelWriter(excelPath)
form_header=['序号','信息描述','DO名称','LN','LD','数据集','四遥类别']
df1 = pd.DataFrame(columns=form_header)
#df1.loc[0] = ['序号0','信息描述0','DO名称0','LN0','LD0','数据集0','四遥类别0']
#df1.loc[1] = ['序号1','信息描述1','DO名称1','LN1','LD1','数据集1','四遥类别1']
######</打开excel>
#CurrPath = os.getcwd() #获取当前目录
root = tree.getroot()
#print(root)
#print("root.tag=%s"%root.tag)
#print("root.attrib=%s"%root.attrib)
same=re.findall(r"{(.+?)}",root.tag)
same='{'+same[0]+'}'
print(same)
#获取数据集下面数据点的ldInst、prefix、lnClass、lnInst、doName
def GetDataRefrence(FCDA_data):
ldInst = ''
prefix = ''
lnClass = ''
lnInst = ''
doName = ''
ldInst = FCDA_data.attrib['ldInst']
if 'prefix' in FCDA_data.attrib:
prefix = FCDA_data.attrib['prefix']
lnClass = FCDA_data.attrib['lnClass']
if 'lnInst' in FCDA_data.attrib:
lnInst = FCDA_data.attrib['lnInst']
doName = FCDA_data.attrib['doName']
return ldInst,prefix,lnClass,lnInst,doName
#获取LN下面各个LN的prefix、lnClass、inst
def GetLNAllName(LNNode):
prefix=''
lnClass=''
inst=''
if 'prefix' in LNNode.attrib:
prefix = LNNode.attrib['prefix']
if 'lnClass' in LNNode.attrib:
lnClass = LNNode.attrib['lnClass']
if 'inst' in LNNode.attrib:
inst = LNNode.attrib['inst']
return prefix,lnClass,inst
#去LLN0或者LN下的DOI里面,找到匹配的prefix,lnClass,lnInst,doName,获取描述
def SearchRefrence(IEDNode,AccessPointName,LDinstName,prefixName,lnClassName,lnInstName,doName):
desc=''
for AccessPointNode in IEDNode.iter(tag="%sAccessPoint"%same): #AccessPointNode
if AccessPointName == AccessPointNode.attrib['name']: #查找同名的访问点
for LDeviceNode in AccessPointNode.iter(tag="%sLDevice"%same): #LDeviceNode
if LDinstName == LDeviceNode.attrib['inst']: #查找同名的逻辑设备
if 'LLN0' == lnClassName:
for LN0Node in LDeviceNode.iter(tag="%sLN0"%same): #到LD0里面
for DOINode in LN0Node.iter(tag="%sDOI"%same): #到LD0的DOI里面
if doName == DOINode.attrib['name']:
desc = DOINode.attrib['desc']
return desc
else:
for LNNode in LDeviceNode.iter(tag="%sLN"%same): #到LN节点
prefix,lnClass,inst=GetLNAllName(LNNode)
if prefixName==prefix and lnClassName==lnClass and lnInstName==inst:
for DOINode in LNNode.iter(tag="%sDOI"%same): #到DOI节点
if '.' in doName:
DoName=doName.split('.')[0]
SdName=doName.split('.')[1]
if DoName==DOINode.attrib['name']:
for SDNode in DOINode.iter(tag="%sSDI"%same):
if SdName == SDNode.attrib['name']:
desc = SDNode.attrib['desc']
return desc
else:
if doName == DOINode.attrib['name']:
desc = DOINode.attrib['desc']
return desc
return desc
#获取四遥类型
#在数据集中,只有遥信没有遥控的双点,会识别错误,请手动修改
def GetSiYaoType(LDdevice,DataSet,prefix,lnClass,doName,DOINode):
if None != DOINode:
doName = DOINode.attrib['name']
if 'MEAS' == LDdevice:
return '遥测'
if 'dsAin' in DataSet:
return '遥测'
if 'dsRelayEna' in DataSet:
return '遥信+遥控'
if 'Parame' in DataSet:
return '定值参数'
if 'ATCC' in lnClass:
if 'ChgA' in doName or 'ChgB' in doName or 'ChgC' in doName:
return '遥信'
else:
return '遥信+遥控'
if 'GGIO' in lnClass:
return '遥信'
if 'LTSM' in lnClass:
return '遥信'
if 'SCLI' in lnClass:
return '遥信'
if 'CILO' in lnClass:
return '遥信'
if 'LPHD' in lnClass:
return '_'
if 'CSWI' in lnClass:
#ctlModel = ''
if '' == DataSet:
return '遥控'
if 'PosA' in doName or 'PosB' in doName or 'PosC' in doName:
return '遥信'
#if None != DOINode:
# for DAINode in DOINode.iter(tag="%sDAI"%same):
# #如果ctlModel== status-only 就是纯遥信
# #如果ctlModel== sbo-with-enhanced-security,并且没有stVal,就是纯遥控
# #如果ctlModel== sbo-with-enhanced-security,并且有stVal,就是遥信+遥控
# if 'ctlModel' == DAINode.attrib['name']:
# ctlModel = DAINode.iter(tag="%sVal"%same).text
# print('ctlModel=',ctlModel)
# os.system('pause')
return '遥信+遥控'
if '' != DataSet and 'GAPC' in lnClass:
return '遥信+遥控'
elif 'GAPC' in lnClass:
return '遥控'
return '_'
#添加到excel数据行
def AddExcelLoc(df,i,LDeviceNode,LNNode,DOINode,Type):
prefix,lnClass,inst = GetLNAllName(LNNode)
#print('##########',prefix,lnClass,inst)
#os.system('pause')
df.loc[i] = [i+1, DOINode.attrib['desc'],DOINode.attrib['name'],prefix+lnClass+inst,LDeviceNode.attrib['inst'],'',Type]
return 0
#搜索表格里面存在的 prefix+lnClass+inst, LDevice_instName,
def SearchExcelDf(df,LDevice_instName,prefix,lnClass,inst):
#print(df)
#os.system('pause')
result = df.query("LD==@LDevice_instName & LN==@prefix+@lnClass+@inst") #numexpr 方式 A,B,C为列名
#print(result)
if result.empty:
#print('未找到:',LDevice_instName,prefix,lnClass,inst)
return False
else:
#print('找到:',LDevice_instName,prefix,lnClass,inst)
return True
#排除的LN列表
#False 排除的
#True 不排除的
def ExcludeLnList(LDevice,prefix,lnClass):
tables =[ \
['CTRL', '', 'LPHD'], \
['CTRL', 'wf', 'GGIO'], \
['CTRL', 'GOIN', 'GGIO'], \
['MEAS', '', 'LPHD'], \
['MEAS', 'GOIN', 'GGIO'], \
['MEAS', 'Ana', 'GGIO'], \
['PIGO', '', '']]
for table in tables:
if table[1] == '' and table[2] == '':
if table[0] == LDevice:
return True
elif table[1] == '':
if table[0] == LDevice and table[2] == lnClass :
return True
elif table[2] == '':
if table[0] == LDevice and table[1] == prefix :
return True
elif table[0] == LDevice and table[1] == prefix and table[2] == lnClass :
return True
return False
#排除的DO列表
def ExcludeDoList(DoName):
tables = [ \
'PhyHealth', \
'Mod', \
'Beh', \
'Health', \
'OpOpn', \
'OpCls', \
'Str', \
'Op', \
'Loc']
if DoName in tables:
return True
else:
return False
#从指定访问点,调到指定的LN节点
def GotoLNNode(AccessPointNode,LDeviceName,prefixName,lnClassName,instName,same):
for LDeviceNode in AccessPointNode.iter(tag="%sLDevice"%same):
if(LDeviceName == LDeviceNode.attrib['inst']):
if 'LLN0' == lnClassName:
for LN0Node in LDeviceNode.iter(tag="%sLN0"%same): #到LD0里面
return LN0Node.iter(tag="%sDOI"%same) #到LD0的DOI里面
else:
for LNNode in LDeviceNode.iter(tag="%sLN"%same): #到LN节点
prefix,lnClass,inst=GetLNAllName(LNNode)
if prefixName==prefix and lnClassName==lnClass and lnInstName==inst:
return LNNode
break;
print("未找到LN节点:",LDeviceName,prefixName,lnClassName,instName)
return None
#从指定访问点,调到指定的DO节点,如果到SD(含符号.),则跳到SD节点
def GotoDOINode(AccessPointNode,LDeviceName,prefixName,lnClassName,instName,doName,same):
LNNode = GotoLNNode(AccessPointNode,LDeviceName,prefixName,lnClassName,instName,same)
if LNNode == None:
return None
if '.' in doName:
DoName_s=doName.split('.')[0]
SdName_s=doName.split('.')[1]
for DOINode in LNNode.iter(tag="%sDOI"%same): #到DOI节点
if DoName_s==DOINode.attrib['name']:
for SDINode in DOINode.iter(tag="%sSDI"%same): #到SDI节点
if SdName_s == SDINode.attrib['name']:
return SDINode
else:
for DOINode in LNNode.iter(tag="%sDOI"%same): #到DOI节点
if doName==DOINode.attrib['name']:
return DOINode
print("未找到DO/SD节点:",LDeviceName,prefixName,lnClassName,instName,doName)
return None
#找不在数据集里面的特殊点
def GetSpecialDO(AccessPointNode,LDeviceNode,same,df,i):
LDevice_instName = LDeviceNode.attrib['inst']
for LNNode in LDeviceNode.iter(tag="%sLN"%same):
prefix,lnClass,inst = GetLNAllName(LNNode)
if False == SearchExcelDf(df,LDevice_instName,prefix,lnClass,inst): #如果未找到
if False == ExcludeLnList(LDevice_instName,prefix,lnClass): #不是要忽略的
print(LDevice_instName,prefix,lnClass,inst)
for DOINode in LNNode.iter(tag="%sDOI"%same): #到DOI节点
for DAINode in DOINode.iter(tag="%sDAI"%same): #到DAI节点
if 'sAddr' in DAINode.attrib:
if DAINode.attrib['sAddr'] != '':
Type = GetSiYaoType(LDevice_instName,'',prefix,lnClass,DOINode.attrib['name'],DOINode)
AddExcelLoc(df,i,LDeviceNode,LNNode,DOINode,Type)
i = i+1
break;
################
#DoNode = GotoLNNode(AccessPointNode,LDevice_instName,prefix,lnClass,inst,same):
#if DoNode == None:
# continue
return i
i=0
for IEDNode in tree.iter(tag="%sIED"%same): #IEDNode
#print("ele.tag=%s ele.attrib=%s"%(IEDNode.tag,IEDNode.attrib))
for AccessPointNode in IEDNode.iter(tag="%sAccessPoint"%same): #AccessPointNode
#print(AccessPointNode.tag,AccessPointNode.attrib)
AccessPointName = AccessPointNode.attrib['name'] #到S1访问点
print('\n##AccessPointName='+AccessPointName)
#if AccessPointName == 'S1':
# continue
#print(AccessPointNode.iter)
for LDeviceNode in AccessPointNode.iter(tag="%sLDevice"%same): #LDeviceNode
LDevice_instName = LDeviceNode.attrib['inst']
print('####LDevice:%s'%LDevice_instName)
for DataSetNode in LDeviceNode.iter(tag="%sDataSet"%same): #DataSetNode
if 'dsLog' in DataSetNode.attrib['name']: #跳过日志
continue
if 'dsGOOSE' in DataSetNode.attrib['name']: #跳过GOOSE输出
continue
DataSet_name = DataSetNode.attrib['name']
DataSet_desc = DataSetNode.attrib['desc']
#print('%s\t%s'%(DataSet_name,DataSet_desc))
for FCDANode in DataSetNode.iter(tag="%sFCDA"%same): #FCDANode
ldInst,prefix,lnClass,lnInst,doName = GetDataRefrence(FCDANode)
#print('%s/%s%s%s/%s '%(ldInst,prefix,lnClass,lnInst,doName),end='')
desc = SearchRefrence(IEDNode,AccessPointName,ldInst,prefix,lnClass,lnInst,doName)
Type = GetSiYaoType(LDevice_instName,DataSet_name,prefix,lnClass,doName,None)
df1.loc[i] = [i+1,desc,doName,prefix+lnClass+lnInst,ldInst,'%s(%s)'%(DataSet_name,DataSet_desc),Type]
i=i+1
#print('%s'%desc)
#df1.to_excel(writer,sheet_name='第一表',index=0)
i=GetSpecialDO(AccessPointNode,LDeviceNode,same,df1,i)
######<写,并关闭excel>
df1.to_excel(writer,sheet_name='第一表',index=0)
#df2.to_excel(writer,sheet_name='第二表',index=0)
writer.close()
######</写,并关闭excel>
#os.system('pause')