python解析智能变电站SCD文件，获取虚端子和软压板信息。
from lxml import etree
import xlwt
import os
import time
import sys
def gainNS(filename):
# 首先获取xml文件的命名空间
currentDir = os.getcwd()
ns = ''
absDir = currentDir + '\\' + filename
with open(absDir, 'r', encoding='utf-8') as f:
# print('正在获取命名空间...')
for i in range(100): # 读取前十行
line = f.readline()
items = line.split() # 以空格分割
for i in items:
if 'xmlns=' in i:
ns = i.split('"')[-2]
break
ns = '{' + ns + '}'
# print('命名空间获取成功:' + ns)
return ns
def removeNull(str): #去除identify中的null，已经用不到了
if 'null' in str:
return ''.join(str.split('null'))
else:
return str
def gainID(x): #寻找LN节点的三个属性值组成ID
try:
res=x.get('prefix','') + x.get('lnClass','') + x.get('inst','' )
except Exception:
print('未知错误', "逻辑节点：" +str(x))
return res
def addIEDdesc(string,dictionary): #给IED名字添加描述信息
name=string.split('.')[0]
desc=dictionary[name]
res=string.replace(name,name+'.'+desc)
return res
def gainStrapsAndVT(filename):
ns=gainNS(filename)
tree=etree.parse(filename)
root=tree.getroot()
IEDs=root.findall(ns+'IED') #找到所有的IED节点
print("IED总数："+str(len(IEDs)))
IEDandDesc={} #字典
for i in IEDs:
IEDandDesc[i.get('name')]=i.get('desc')
# print('字典长度为：'+str(len(IEDandDesc)))
if len(IEDs)!=len(IEDandDesc):
print('在给IED名字添加描述信息时出错，请检查字典长度。')
worbook = xlwt.Workbook()
sheet1 = worbook.add_sheet('VT', cell_overwrite_ok=True)
sheet2 = worbook.add_sheet('strap', cell_overwrite_ok=True)
i = 0 #对虚端子计数
j = 0 #对软压板计数
print("正在读取虚端子和软压板...")
for ied in IEDs:
iedName=ied.get('name') #获取当前IED的名字
links=ied.iter(ns+'ExtRef') #找到每条链路
for item in links:
intAddr = iedName+'.'+item.get('intAddr').split(':')[-1]#增加了IED名字属性
identifyLN=item.get('intAddr').split('/')[-1].split('.')[0] # intAddr里面的LN值可以唯一确定对应LDevice下的LN节点
identifyLD=item.get('intAddr').split(':')[-1].split('/')[0] #从intAddr里解析出逻辑设备名
identifyDO=item.get('intAddr').split('.')[1] #从intAddr里解析出数据对象名
# LDevice=item.find('.......') #先寻找当前LN0的父节点LDevice,这里使用了xpath，..表示父节点，......表示三级父节点，七个点表示当前元素的三级父节点
LDevice = [x for x in ied.iter(ns + 'LDevice') if x.get('inst') == identifyLD][0]
try:
ln = [x for x in LDevice.findall(ns+"LN0") +LDevice.findall(ns+"LN") if gainID(x) == identifyLN] # 找到LN节点
except Exception as e:
print('未知错误', "IED:"+iedName,"逻辑设备："+LDevice.get('inst'),e)
# sys.exit()
if len(ln)!=1:
print('寻找输入虚端子desc属性时符合条件的逻辑节点有%d个，当前IED为%s,虚端子intAddr为%s'%(len(ln),iedName,item.get('intAddr')))
ln=ln[0]
DOIs=[x for x in ln if x.get('name')==identifyDO]
if len(DOIs)!=1:
print('寻找intAddr对应的数据对象时出错，找到%d个DOI。' % len(DOIs))
DOI=DOIs[0]
intAddr = intAddr + '/' + ln.get('desc', '无描述') + '/' + DOI.get('desc', '无描述')
outAddr = item.get('iedName', 'null') + '.' + item.get('ldInst', 'null') + '/' +item.get('prefix', '')+\
item.get('lnClass','null') + item.get('lnInst', '')+ '.' + item.get('doName', 'null') + '.' + item.get('daName', 'null')
#找对应IED,标识是iedName
outAddrIED=[x for x in IEDs if x.get('name')==item.get('iedName')][0]
#找对应LDevice，标识是ldInst逻辑设备实例
outAddrLDevice=[x for x in outAddrIED.iter(ns+'LDevice') if x.get('inst')==item.get('ldInst')]
if len(outAddrLDevice)!=1:
print('寻找outAddr对应的逻辑设备时出错，找到%d个逻辑设备。'%len(outAddrLDevice))
outAddrLDevice=outAddrLDevice[0]
#找对应LN，标识是prefix+lnClass+lnInst
identifyLN = item.get('prefix', '') + item.get('lnClass', '') + item.get('lnInst', '')
# outAddrLN=[x for x in outAddrLDevice.iter(ns+'LN') if gainID(x)==identify] #outAddrLDevice.iter(ns+'LN')报错，因为要找的LN有可能是LN0
outAddrLN = [x for x in outAddrLDevice.findall(ns + "LN0") + outAddrLDevice.findall(ns + "LN") if gainID(x) == identifyLN]
if len(outAddrLN)!=1:
print('寻找outAddr对应的逻辑节点时出错，找到%d个逻辑节点。' % len(outAddrLN))
print('当前intAddr为%s'%intAddr)
print('当前outAddrIED为%s，outAddrLDevice为%s，标识为%s'%(outAddrIED.get('name'),outAddrLDevice.get('inst'),identifyLN))
outAddrLN=outAddrLN[0]
#找对应的DOI，获取中文描述信息，标识是doName
outAddrDOI=[x for x in outAddrLN if x.get('name')==item.get('doName')]
if len(outAddrDOI)!=1:
print('寻找outAddr对应的数据对象时出错，找到%d个DOI。' % len(outAddrDOI))
outAddrDOI=outAddrDOI[0]
outAddrDesc=outAddrLN.get('desc','无描述')+ '/'+ outAddrDOI.get('desc','无描述')
outAddr=outAddr+ '/' + outAddrDesc
# intAddr=addIEDdesc(intAddr,IEDandDesc)
# outAddr=addIEDdesc(outAddr,IEDandDesc)
sheet1.write(i, 0, outAddr)
sheet1.write(i, 1, intAddr)
i += 1
dataSet = [x for x in ied.iter(ns+'DataSet') if 'dsRelayEna' in x.get('name', 'noname')] # 这里name是属性，所以不用加上名字空间，只有tag才需要加上名字空间
#dataSet里面可能不止一个dsRelayEna数据集，还有dsRelayEna2数据集
for data in dataSet: #找到软压板数据集，因为使用了列表生成式，所以dataSet是一个列表
for item in data: #这里item是每行软压板数据FCDA节点,data是DataSet节点
LDevice=[x for x in ied.iter(ns+'LDevice') if x.get('inst')==item.get('ldInst')][0]
# LDevice = data.find('.....')
identifyLN = item.get('prefix', '') + item.get('lnClass', '') + item.get('lnInst', '') # 使用此标识寻找LDevice下的LN
ln = [x for x in LDevice.findall(ns+"LN0") +LDevice.findall(ns+"LN") if gainID(x) == identifyLN] # 找到LN节点
if len(ln) != 1:
print('当前IED为%s，软压板标识为%s.' % (iedName, identifyLN + item.get("doName")))
print('找到%d个LN' % len(ln))
ln = ln[0]
DOIs = [x for x in ln if x.get('name') == item.get('doName')]
if len(DOIs)!=1:
print('结果可能错误，当前DOI数量不唯一。')
DOI = DOIs[0]
desc=ln.get('desc','无描述') + '/' + DOI.get('desc','无描述')
strap_info = iedName+'.'+item.get("ldInst", "null") + '/' + item.get('prefix', '') + \
item.get('lnClass', 'null') + item.get('lnInst', '') + '.' + item.get("doName", "null")+'/'+desc
#软压板包含多个属性，strap_info没有列举完
# strap_info=addIEDdesc(strap_info,IEDandDesc)
sheet2.write(j, 0, strap_info)
j+=1
print("此文件共有%d个虚端子，已经写入Excel文件VT工作表中" % i)
print("此文件共有%d个软压板，已经写入Excel文件strap工作表中"%j)
worbook.save(filename.split('.')[0] + '虚端子和软压板.xls')
if __name__ == '__main__':
beginTime=time.time()
gainStrapsAndVT('files\\xxx.scd')
endTime=time.time()
print("共耗时："+str(endTime-beginTime)+"秒。")
解析excel文件，获得全局连接矩阵：
import xlrd
from lxml import etree
import extractNew
import numpy as np
def readExcel(filename):
wb = xlrd.open_workbook(filename) # 打开文件
sheetVT = wb.sheet_by_name('VT') # 通过名字获取表格
sheetStraps=wb.sheet_by_name('strap')
sender = sheetVT.col_values(0) # 获取列内容,第一列是发送虚端子
receiver=sheetVT.col_values(1)
straps=sheetStraps.col_values(0)
return sender,receiver,straps
def getIEDFromFile(scdFile): #从scd文件中读取IED
tree=etree.parse(scdFile)
root=tree.getroot()
ns=extractNew.gainNS(scdFile)
IEDs=root.iter(ns+'IED')
IEDs=[x.get('name') for x in IEDs]
return IEDs
def getMissingIED(a,b): #找到不含虚端子的IED（对比从excel文件和scd文件读取到的IED即可）
res=[]
for i in a:
if i not in b:
res.append(i)
return res
def getIEDGlobalMat(filename): #获取全局IED连接矩阵
sender,receiver,straps = readExcel(filename)
senderIED = [x.split('.')[0] for x in sender]
receiverIED=[x.split('.')[0] for x in receiver]
# IEDs=[] #从excel文件中读取IED
# for i in receiverIED + senderIED:
# IEDs.append(i)
# IEDs=list(set(IEDs))
IEDs=getIEDFromFile(filename.split('虚端子和软压板')[-2]+'.scd')
# misingIED= getMissingIED(IEDFromFile,IEDs) #并非所有IED都有输入输出虚端子
lengthOfIEDs=len(IEDs)
IEDIndex=list(zip(range(lengthOfIEDs),IEDs)) #将IEDs中的顺序固定下来
IEDGlobalMat=np.zeros([lengthOfIEDs,lengthOfIEDs])
links = list(zip(senderIED, receiverIED)) # IED之间的联系
# 遍历links，填充ied矩阵
for link in links:
sender = link[0] # 发送IED
receiver=link[1] #link[1]是接收IED
indexX=IEDs.index(sender) #x表示发送IED，y表示接收IED
indexY=IEDs.index(receiver)
IEDGlobalMat[indexX,indexY]+=1 #每一行表示以对应IED为发送IED的连接关系，每一列表示以对应IED为接收IED的连接关系
writeResToFile(IEDGlobalMat,IEDIndex,filename.replace('虚端子和软压板.xls','全局IED连接矩阵(m).txt'))
print("全局IED连接矩阵已经写入txt文件中。")
return IEDGlobalMat,IEDIndex #IED连接矩阵不一定是对角阵
def getBoundary(filename,iedName): #找检修边界
IEDGlobalMat, IEDIndex=getIEDGlobalMat(filename)
for i in range(len(IEDIndex)):
if IEDIndex[i][1]==iedName:
index=i
listX = IEDGlobalMat[index] #第i行
listY = IEDGlobalMat[:,index] #第i列
connectedIED = []
for i in range(len(listX)):
if listX[i] >0 or listY[i] >0:
connectedIED.append(IEDIndex[i][1])
# print(connectedIED)
return
def matchVTandStraps(filename): #虚端子软压板匹配
sender, receiver, straps= readExcel(filename)
IEDs=getIEDFromFile(filename.split('虚端子和软压板')[-2]+'.scd')
return
def writeResToFile(res,index,filename):
with open(filename, 'w', encoding='utf-8') as f:
f.write(str(index)+'\n')
for i in res:
for j in i:
# f.write(str(j)[0]+' ') #这三种方法都可以
# f.write(str('%.0f'%j) + ' ')
f.write(str('%d'%j) + ' ')
f.write('\n')
if __name__ == '__main__':
# res = getIEDGlobalMat('files\\xxx.xls')
其他程序：
import numpy as np
import getIEDMatrix2
import xlwt
import getPeiTing
import xlrd
def getVTandStraps(filename): #提取每个IED的软压板和虚端子对，保存在新的表单里
IEDIndex, IEDGlobalMat=getPeiTing.readTXT(filename)
sender, receiver, straps = getIEDMatrix2.readExcel(filename.split('全局IED连接矩阵')[0] + '虚端子和软压板.xls')
links = list(zip(sender,receiver))
worbook = xlwt.Workbook()
for index,iedName in IEDIndex.items():
linksToMatch=[]
strapsToMatch=[]
for i in range(len(sender)): #先是以IED为发送的链路，再是接收
if sender[i].split('.')[0]==iedName:
linksToMatch.append(links[i])
for i in range(len(receiver)):
if receiver[i].split('.')[0]==iedName:
linksToMatch.append(links[i])
for i in range(len(straps)):
if straps[i].split('.')[0]==iedName:
strapsToMatch.append(straps[i])
sheet = worbook.add_sheet(iedName, cell_overwrite_ok=True)
sheet.write(0, 0, iedName+'的软压板')
sheet.write(0, 1, iedName+'的虚端子对')
for i in range(len(strapsToMatch)):
sheet.write(i+1, 0, strapsToMatch[i])
for i in range(len(linksToMatch)):
sheet.write(i+1, 1, linksToMatch[i][0])
sheet.write(i+1, 2, linksToMatch[i][1])
worbook.save(filename.split('全局IED连接矩阵')[0] + '待匹配虚端子和软压板new.xls')
print("文件写入成功。")
if __name__ == '__main__':
filename = 'files\\xxx.txt'
getVTandStraps(filename)
import xlrd
import xlwt
import numpy as np
def mergeExcel(filename): #把虚端子对应表融合成一个表单
wbRead = xlrd.open_workbook(filename) # 打开文件
sheetNames = wbRead.sheet_names()
resArray = [] # 先声明一个空list
for i in sheetNames:
table = wbRead.sheet_by_name(i)
for j in range(table.nrows): # table.nrows表示总行数
line = table.row_values(j) # 读取每行数据，保存在line里面，line是list
resArray.append(line) # 将line加入到resArray中，resArray是二维list
# resArray = np.array(resArray) # 将resArray从二维list变成数组
wbWrite = xlwt.Workbook()
table = wbWrite.add_sheet('工作表1', cell_overwrite_ok=True)
i = 0
for j in resArray:
for k in range(len(j)):
table.write(i, k, j[k])
i += 1
wbWrite.save('test.xls')
return
if __name__ == '__main__':
filename = 'files\\虚端子对应表.xlsx'
mergeExcel(filename)
版权声明：本文为zhhy236400原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接和本声明。