欢迎不吝资助和交流
-- coding: utf-8 --
import os
import openpyxl
import time
def measGroup(g):
measGrou = {}
for line in range(6, ws.max_row + 1):
c_value = ws.cell(row=line, column=2).value
d_value = ws.cell(row=line, column=3).value
e_value = ws.cell(row=line, column=4).value
f_value = ws.cell(row=line, column=5).value
l_value = ws.cell(row=line, column=10).value.split(';')[0] # A1测量配置号
ad_value = ws.cell(row=line, column=11).value.split(';')[0] # A2测量配置号
ae_value = ws.cell(row=line, column=15).value.split(';') # 基于覆盖的异频切换测量配置
ae1_value = ws.cell(row=line, column=15).value
af_value = f"{d_value}-{e_value}-{f_value}-{l_value}"
ag_value = f"{d_value}-{e_value}-{f_value}-{ad_value}"
cell_key = c_value
cell_value = [af_value, ag_value, l_value, ad_value, ae_value, ae1_value]
measGrou[cell_key] = cell_value
return measGrou
def measurement(m): # 读取CGI，小区中文名，服务小区频点
mea = {}
for line in range(6, ws.max_row + 1):
c_value = ws.cell(row=line, column=3).value
e_value = ws.cell(row=line, column=4).value
g_value = ws.cell(row=line, column=5).value
k_value = ws.cell(row=line, column=8).value.replace('cellLocalId=', '') # 剔除'cellLocalId='获得小区CI
k1_value = k_value.strip()
l_value = ws.cell(row=line, column=9).value # 分割取最后一位，获得测量配置索引组ID
ad_value = ws.cell(row=line, column=112).value.split(';') # 异频载频列表
ad1_value = ws.cell(row=line, column=112).value
af = f"{c_value}-{e_value}-{g_value}-{l_value}" # 连接子网，网元，站号，测量配置索引组ID
cell_key = f"{c_value}-{e_value}-{g_value}-{k1_value}"
cell_value = [l_value, ad_value, af, ad1_value]
mea[cell_key] = cell_value
return mea
def ueEUtranMea(u):
event = {'0': 'A1', '1': 'A2', '2': 'A3', '3': 'A4', '4': 'A5', '5': 'A6'}
ue = {}
for line in range(6, ws.max_row + 1):
c = ws.cell(row=line, column=3).value
e = ws.cell(row=line, column=4).value
g = ws.cell(row=line, column=5).value
k = ws.cell(row=line, column=8).value # UE系统内测量参数ID
l = event[ws.cell(row=line, column=13).value] # 事件标识
ad = ws.cell(row=line, column=14).value # 事件判决的RSRP门限(dBm)
ae = ws.cell(row=line, column=16).value # A5事件判决的RSRP绝对门限2
ar = ws.cell(row=line, column=18).value # 判决迟滞范围
af = ws.cell(row=line, column=19).value # 事件发生到上报的时间差
ag = ws.cell(row=line, column=26).value # A3事件偏移(dB)
cell_key = f"{c}-{e}-{g}-{k}"
cell_value = [l, ad, ae, ar, af, ag]
ue[cell_key] = cell_value
return ue
start_time = time.time()
path = 'source'
print('读入文件夹数据-->')
pathlist = os.listdir(path) # 读取文件夹及子文件夹路径
files = []
win_date = []
for file in pathlist: # 遍历文件夹及文件夹里的内容
if file.endswith(".xlsx"): # 如果后缀为xlsx，则存储文件的完整路径到files里
files.append(os.path.join(path, file))
sum_date = {}
sum_Mea = {}
sum_MeasGroup = {}
sum_UeEUtranMea = {}
遍历
for files_path in files:
wb = openpyxl.load_workbook(files_path)
sh = wb.sheetnames
if 'EUtranCellFDD' in sh:
ws = wb['EUtranCellFDD']
fdd_date = {}
count=1
for line in range(6, ws.max_row + 1):
c_value = ws.cell(row=line, column=3).value # 子网ID
e_value = ws.cell(row=line, column=4).value # 网元ID
g_value = ws.cell(row=line, column=5).value # 站号
k_value = ws.cell(row=line, column=11).value # 小区ID
l_value = ws.cell(row=line, column=8).value # 小区中文名
ad_value = ws.cell(row=line, column=32).value # 服务小区频点
fdd_key = f"{c_value}-{e_value}-{g_value}-{k_value}"
fdd_value = [l_value, ad_value]
fdd_date[fdd_key] = fdd_value
sum_date.update(fdd_date)
print(f"------FDD小区数据读取成功:{count}")
count += 1
else:
ws1 = wb['EUtranCellTDD']
tdd_date = {}
count = 1
for line1 in range(6, ws1.max_row + 1):
c1_value = ws1.cell(row=line1, column=3).value # 子网ID
e1_value = ws1.cell(row=line1, column=4).value # 网元ID
g1_value = ws1.cell(row=line1, column=5).value # 站号
k1_value = ws1.cell(row=line1, column=11).value # 小区ID
l1_value = ws1.cell(row=line1, column=8).value # 小区中文名
ad1_value = ws1.cell(row=line1, column=31).value # 服务小区频点
tdd1_key = f"{c1_value}-{e1_value}-{g1_value}-{k1_value}"
tdd1_value = [l1_value, ad1_value]
tdd_date[tdd1_key] = tdd1_value
sum_date.update(tdd_date)
print(f"------FDD小区数据读取成功:{count}")
count += 1
# print(tdd_date)
if 'EUtranCellMeasurement' in sh: # 测量配置号，异频频点
ws = wb['EUtranCellMeasurement']
c = measurement(sh)
sum_Mea.update(c)
print('-----FDD小区测量配置索引组ID读取成功-----')
else:
if 'EUtranCellMeasurementTDD' in sh:
ws = wb['EUtranCellMeasurementTDD']
c = measurement(sh)
sum_Mea.update(c)
print('-----TDD小区测量配置索引组ID读取成功-----')
if 'CellMeasGroup' in sh:
ws = wb['CellMeasGroup']
c = measGroup(sh)
sum_MeasGroup.update(c)
print('-----FDD测量配置索引集读取成功-----')
else:
if 'CellMeasGroupTDD' in sh:
ws = wb['CellMeasGroupTDD']
c = measGroup(sh)
sum_MeasGroup.update(c)
print('-----TDD测量配置索引集读取成功-----')
if 'UeEUtranMeasurement' in sh:
ws = wb['UeEUtranMeasurement']
z = ueEUtranMea(sh)
sum_UeEUtranMea.update(z)
print('----UE系统内测量参数读取成功FDD----')
else:
if 'UeEUtranMeasurementTDD' in sh:
ws = wb['UeEUtranMeasurementTDD']
z = ueEUtranMea(sh)
sum_UeEUtranMea.update(z)
print('-----UE系统内测量参数读取成功TDD----')
print(sum_UeEUtranMea)
sum = {} # cgi,小区中文名。。。。
sum1 = {} # 创建A1 字典
sum2 = {} #创建 A2字典
sum3 = {} # CGI:测量配置 ID组{'1927-174336-174336-101': ['880', '880', '881', '882', '881', '880', '882', '682', '881', '882', '880', '870', '870', '870', '870', '870', '849', '849', '849', '849', '849', '849', '849', '849', '849', '849', '849', '849', '849', '849', '849', '849']}
sum4 = {} #CGI:分解的测量配置号
sum6={} #A1,ID，门限
sum7={} #A2,ID，门限
hb= []
hb1=[]
for y in sum_date.keys():
y1=sum_date[y][0]
y2=sum_date[y][1]
sum[y]=[y1,y2]
for i in sum_Mea.keys(): #遍历频点
i1=sum_Mea[i][1]
sum_earf={i:i1}
for r in sum_Mea.keys(): #遍历A1，创建字典
r1=sum_MeasGroup[sum_Mea[r][0]]
sum1[r]=[r1[0],r1[2]] #A1
sum2[r]=[r1[1],r1[3]] #A2
sum3[r]=r1[4],r1[5] #原始测量配置ID
for t in sum_Mea.keys(): #A1 ID 门限
t1=sum_UeEUtranMea[sum1[t][0]]
sum6[t]=[t,t1[0],t1[1]]
for q in sum_Mea.keys(): #A2 ID 门限
q1 = sum_UeEUtranMea[sum2[q][0]]
sum7[q] = [q, q1[0], q1[1]]
for e in sum_date.keys():
for i in sum_Mea[e][1]: #分解频点
event1=[e,sum[e][0],sum[e][1],sum_Mea[e][3],i,sum3[e][1],sum_MeasGroup[sum_Mea[e][0]][2],sum6[e][1],sum6[e][2],sum2[e][1],sum7[e][1],sum7[e][2]]
hb.extend([event1])
for w in sum_Mea.keys(): # 创建A3A4A5字典
w1 = w[0:19]
l = len(sum_Mea[w][1])
for i in sum3[w][0][0:l]:
w11 = f"{w1}{i}"
# event2 = [w[12:], i, sum_UeEUtranMea[w11][0], sum_UeEUtranMea[w11][1], sum_UeEUtranMea[w11][2]]
if w11 in sum_UeEUtranMea:
event2 = [w[12:], i, sum_UeEUtranMea[w11][0], sum_UeEUtranMea[w11][1], sum_UeEUtranMea[w11][2]]
hb1.extend([event2])
else:
event2=[w[12:],'none','none','none','none']
hb1.extend([event2])
wb1 = openpyxl.Workbook()
ws1 = wb1.create_sheet("合并结果")
tit = ['CGI', '小区中文', '主频', '异频载频集','异频频点','测量配置索引组ID集', 'A1-测量配置索引ID','A1标识','A1门限', 'A2-测量配置索引ID', 'A2标识','A2门限','ECI', '频点对应异频测量索引ID', '事件标识', 'A3/A4/A5-1门限', 'A5-2门限']
for col_num, header_value in enumerate(tit, start=1):
ws1.cell(row=1, column=col_num, value=header_value)
for i,j in zip(hb,hb1):
k=i+j
ws1.append(k)
wb1.save("多频网参数合并.xlsx")
print("文件已生成")
end_time = time.time()
running_time = end_time - start_time
print('运行时间:{}分{}秒 '.format(round(running_time//60,0), round(running_time/60,2)))