电信5GLampsite扩容脚本.py
import os
import openpyxl
import pandas as pd
from win32com.client import Dispatch
from openpyxl.styles import Font#字体模块
from openpyxl.styles import PatternFill#导入填充模块
df = pd.DataFrame(pd.read_excel(r‘C:\Users\xoadmin\Desktop\name.xlsx‘,skiprows=1))
newdf = df[[‘设备名称‘,‘BBU编号‘,‘HUBID‘,‘HUB名称‘,‘HUB下联端口（与PRRU端口）‘,‘5GPRRUID‘,‘5GPRRU名称‘,‘CUDU下联槽位/端口‘]]
for i in range(0,len(newdf)):
newdf.loc[i,‘CUDU端口‘] = ‘‘‘=IF(LEFT(B{0},3)="HUB",VLOOKUP(B{0},A:H,8,0),H{0})‘‘‘.format(i+2)
newdf.to_excel(r‘C:\Users\xoadmin\Desktop\Newname.xlsx‘,index=False)
def color(file_name, title):
wk = openpyxl.load_workbook(file_name)#加载已存在的Excel
sheet1 = wk[title]#wk[wk_name[0]]
for i in range(0,len(newdf)):
sheet1.cell(row=1,column=1,value="HUB编号")
sheet1.cell(row=1,column=5,value="HUB端口")
sheet1.cell(row=1,column=6,value="PRRUID")
sheet1.cell(row=1,column=7,value="PRRU名称")
sheet1.cell(row=1,column=8,value="CUDU下联槽位")
sheet1.cell(row=i+2,column=1,value=‘‘‘=LEFT(D{0},8)‘‘‘.format(i+2))
wk.save(file_name)
file_name,title = r‘C:\Users\xoadmin\Desktop\Newname.xlsx‘,‘Sheet1‘
color(file_name,title)
print("---------------------------------------------\n正在大力解析中！.！.！.！.！.！.\n---------------------------------------------")
def openexcel(file_name):
xlApp =Dispatch("Excel.Application")
xlApp.DisplayAlerts = False#不显示警告
xlApp.Visible = False
xlBook =xlApp.Workbooks.Open(file_name)
xlBook.SaveAs(file_name)
xlBook.Close()
openexcel(file_name)
newdf = pd.DataFrame(pd.read_excel(file_name))
newdf.sort_values(by = [‘CUDU端口‘,‘BBU编号‘],inplace=True,ascending=True)
newdf.drop(["HUB编号","CUDU下联槽位"],axis=1,inplace=True)
print(newdf)
newdf.to_excel(file_name,index=False)
writer = pd.ExcelWriter(r‘C:\Users\xoadmin\Desktop\NNewname.xlsx‘)
data1 = pd.DataFrame(pd.read_excel(file_name))
data2 = pd.DataFrame(pd.read_excel(file_name))
for i in range(0,len(data1)):
data1.loc[:,‘本地小区ID‘] =""
data1.loc[i,‘加HUB‘] =‘‘‘=IF(COUNTIF($G$2:$G{0},G{0})=1,"ADD RRUCHAIN:RCN="&LEFT(G{0},1)&RIGHT(G{0},1)&",TT=CHAIN,BM=COLD,AT=LOCALPORT,HSRN=0,HSN="&LEFT(G{0},1)&",HPN="&RIGHT(G{0},1)&",CR=AUTO,USERDEFRATENEGOSW=OFF;","")&IF(AND(COUNTIF($B$2:$B{0},B{0})=1,LEFT(A{0},4)="CUDU"),"ADD RHUB:CN=1,SRN="&B{0}&",RCN="&LEFT(G{0},1)&RIGHT(G{0},1)&",PS=0,RN="""&c{0}&""",MNTMODE=NORMAL;","")&IF(AND(COUNTIF($B$2:$B{0},B{0})=1,LEFT(A{0},3)="HUB"),"ADD RHUB:CN=1,SRN="&B{0}&",RCN="&LEFT(G{0},1)&RIGHT(G{0},1)&",PS=1,RN="""&c{0}&""",MNTMODE=NORMAL;","")‘‘‘.format(i+2)
data1.loc[i,‘加RRU链‘] = ‘‘‘="ADD RRUCHAIN:RCN="&E{0}&",TT=CHAIN,BM=COLD,AT=LOCALPORT,HCN=1,HSRN="&B{0}&",HSN=0,HPN="&MID(D{0},3,2)-1&",CR=AUTO;"‘‘‘.format(i+2)
data1.loc[i,‘加RRU‘] = ‘‘‘="ADD RRU:CN=0,SRN="&E{0}&",SN=0,TP=BRANCH,RCN="&E{0}&",PS=0,RT=MPMU,RS=NO,RN="""&F{0}&""",RXNUM=4,TXNUM=4,MNTMODE=NORMAL,RFTXSIGNDETECTSW=OFF,DORMANCYSW=OFF;"‘‘‘.format(i+2)
data1.loc[i,‘加扇区‘] = ‘‘‘="ADD SECTOR:SECTORID="&E{0}&",ANTNUM=4,ANT1CN=0,ANT1SRN="&E{0}&",ANT1SN=0,ANT1N=R0A,ANT2CN=0,ANT2SRN="&E{0}&",ANT2SN=0,ANT2N=R0B,ANT3CN=0,ANT3SRN="&E{0}&",ANT3SN=0,ANT3N=R0C,ANT4CN=0,ANT4SRN="&E{0}&",ANT4SN=0,ANT4N=R0D,CREATESECTOREQM=TRUE, SECTOREQMID="&E{0}&";"‘‘‘.format(i+2)
data1.loc[i,‘绑定‘] = ‘‘‘="ADD NRDUCELLCOVERAGE:NRDUCELLTRPID="&H{0}&",NRDUCELLCOVERAGEID="&E{0}&",SECTOREQMID="&E{0}&";"‘‘‘.format(i+2)
data2.loc[:,‘本地小区ID‘] =""
data2.loc[:,‘小区ID‘] =""
data2.loc[:,‘TACID‘] =""
data2.loc[:,‘TAC‘] =""
data2.loc[:,‘PCI‘] =""
data2.loc[:,‘根序列‘] =""
data2.loc[0,‘小区名称‘] ="lampsite-3.5G_1"
for j in range(0,6):
data2.loc[j,‘加NRCELL‘] = ‘‘‘="ADD NRCELL:NRCELLID="&A{0}&",CELLNAME="""&G{0}&""",CELLID="&B{0}&",FREQUENCYBAND=N78,DUPLEXMODE=CELL_TDD;ADD GNBTRACKINGAREA:TRACKINGAREAID="&C{0}&",TAC="&D{0}&";"‘‘‘.format(j+2)
data2.loc[j,‘加DUCELL‘] = ‘‘‘="ADD NRDUCELL:NRDUCELLID="&A{0}&",NRDUCELLNAME="""&G{0}&""",DUPLEXMODE=CELL_TDD,CELLID="&B{0}&",PHYSICALCELLID="&E{0}&",FREQUENCYBAND=N78,ULNARFCN=630000,DLNARFCN=630000,ULBANDWIDTH=CELL_BW_100M,DLBANDWIDTH=CELL_BW_100M,CELLRADIUS=800,SLOTASSIGNMENT=7_3_DDDSUDDSUU,SLOTSTRUCTURE=SS104,LAMPSITECELLFLAG=YES,TRACKINGAREAID="&C{0}&",SSBFREQPOS=7783,LOGICALROOTSEQUENCEINDEX="&F{0}&";"‘‘‘.format(j+2)
data2.loc[j,‘流量上报‘] = ‘‘‘="MOD NRCELLNSADCCONFIG:NRCELLID="&A{0}&",NRDATAVOLUMERPTCFG=600;ADD NRDUCELLOP:NRDUCELLID="&A{0}&",OPERATORID=0;ADD NRDUCELLOP:NRDUCELLID="&A{0}&",OPERATORID=1;MOD NRDUCELLUEPWRSAVING:NRDUCELLID="&A{0}&",NRDUCELLDRXALGOSWITCH=BASIC_DRX_SW-1&LNR_DRX_SYNCHRONIZE_SW-0;"‘‘‘.format(j+2)
data2.loc[j,‘TAC绑定切片‘] = ‘‘‘="ADD GNBTANS:TRACKINGAREAID="&C{0}&",OPERATORID=0,SLICESERVICETYPE=1,SLICEDIFFERENTIATOR=0;ADD GNBTANS:TRACKINGAREAID="&C{0}&",OPERATORID=0,SLICESERVICETYPE=1,SLICEDIFFERENTIATOR=262144;ADD GNBTANS:TRACKINGAREAID="&C{0}&",OPERATORID=1,SLICESERVICETYPE=1,SLICEDIFFERENTIATOR=0;ADD GNBTANS:TRACKINGAREAID="&C{0}&",OPERATORID=1,SLICESERVICETYPE=1,SLICEDIFFERENTIATOR=4194304;"‘‘‘.format(j+2)
data2.loc[j,‘TRP‘] = ‘‘‘="ADD BRD:SN="&M{0}&",BT=UBBP,BBWS=GSM-0&UMTS-0<E_FDD-0<E_TDD-0&NBIOT-0&NR-1;ADD BASEBANDEQM:BASEBANDEQMID="&M{0}&",BASEBANDEQMTYPE=ULDL,UMTSDEMMODE=NULL,SN1="&M{0}&";ADD NRDUCELLTRP:NRDUCELLTRPID="&A{0}&",TRPTYPE=DEFAULT,NRDUCELLID="&A{0}&",TXRXMODE=4T4R,BASEBANDEQMID="&M{0}&",POWERCONFIGMODE=TRANSMIT_POWER,MAXTRANSMITPOWER=210,CPRICOMPRESSION=3DOT2_COMPRESSION;"‘‘‘.format(j+2)
data2.loc[j,‘槽位‘] =0
data2.drop(["BBU编号","HUBID","HUB名称","HUB端口","PRRUID","PRRU名称","CUDU端口"],axis=1,inplace=True)
data1.to_excel(writer, sheet_name="Lampsite加PRRU",index=False)
data2.to_excel(writer, sheet_name="Lampsite加小区",index=False)
writer.save()
# data3 = pd.DataFrame(np.arange(1,13).reshape((3,4)))
os.system(r‘del C:\Users\xoadmin\Desktop\Newname.xlsx‘)
print("---------------------------------------------\n数据提取成功已输出到桌面！！！\n按Enter键退出。。。。。。\n---------------------------------------------")
input()
原文：https://www.cnblogs.com/xoadmin/p/15353836.html