import pandas as pd
import openpyxl
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))
df = df.dropna(axis= 'columns', how= 'all')#删除一列都是NaN的列
newdf = df[['设备名称','CUDU槽位端口编号','AAU名称','AAUID','经度.2','纬度.2']]
newdf.rename(columns={'经度.2':'经度','纬度.2':'纬度'},inplace=True)#修改列名
newdf.loc[:,'设备名称'] = newdf['设备名称'].str.replace('_','')#替换某字符
newdf['gNodeB标识'] = ""
newdf['TAC,ID'] = ""
newdf['TAC'] = ""
newdf['PCI'] = ""
newdf['根序列'] = ""
newdf['本地小区ID'] = ""
newdf['小区ID'] = ""
newdf['扇区ID'] = ""
for i in range(0,len(newdf)):
newdf.loc[i,'加NRCELL'] = '''="ADD NRCELL:NRCELLID="&L{0}&",CELLNAME="""&LEFT(A{0},11)&MID(C{0},12,LEN(C{0})-15)&"-3.5G_"&RIGHT(C{0},1)&""",CELLID="&M{0}&",FREQUENCYBAND=N78,DUPLEXMODE=CELL_TDD;ADD GNBTRACKINGAREA:TRACKINGAREAID="&H{0}&",TAC="&I{0}&";"'''.format(i+2)
newdf.loc[i,'加NRDUCELL'] = '''="ADD NRDUCELL:NRDUCELLID="&L{0}&",NRDUCELLNAME="""&LEFT(A{0},11)&MID(C{0},12,LEN(C{0})-15)&"-3.5G_"&RIGHT(C{0},1)&""",DUPLEXMODE=CELL_TDD,CELLID="&M{0}&",PHYSICALCELLID="&J{0}&",FREQUENCYBAND=N78,ULNARFCN=630000,DLNARFCN=630000,ULBANDWIDTH=CELL_BW_100M,DLBANDWIDTH=CELL_BW_100M,CELLRADIUS=5000,SLOTASSIGNMENT=7_3_DDDSUDDSUU,SLOTSTRUCTURE=SS104,TRACKINGAREAID="&H{0}&",SSBFREQPOS=7783,SSBPERIOD=MS20,SIB1PERIOD=MS20,LOGICALROOTSEQUENCEINDEX="&K{0}&";"'''.format(i+2)
newdf.loc[i,'加基带板'] = '''="ADD BRD:SN="&LEFT(B{0},1)&",BT=UBBP,BBWS=GSM-0&UMTS-0&LTE_FDD-0&LTE_TDD-0&NBIOT-0&NR-1;ADD BASEBANDEQM:BASEBANDEQMID="&LEFT(B{0},1)&",BASEBANDEQMTYPE=ULDL,UMTSDEMMODE=NULL,SN1="&LEFT(B{0},1)&";"'''.format(i+2)
newdf.loc[i,'加TRP'] = '''="ADD NRDUCELLTRP:NRDUCELLTRPID="&L{0}&",NRDUCELLID="&L{0}&",TXRXMODE=64T64R,BASEBANDEQMID="&LEFT(B{0},1)&",POWERCONFIGMODE=TRANSMIT_POWER,MAXTRANSMITPOWER=349,CPRICOMPRESSION=3DOT2_COMPRESSION;"'''.format(i+2)
newdf.loc[i,'加AAU'] = '''="ADD RRUCHAIN:RCN="&LEFT(B{0},1)&RIGHT(B{0},1)&",TT=CHAIN,BM=COLD,AT=LOCALPORT,HSRN=0,HSN="&LEFT(B{0},1)&",HPN="&RIGHT(B{0},1)&",PROTOCOL=eCPRI,CR=AUTO,USERDEFRATENEGOSW=OFF;ADD RRU:CN=0,SRN="&D{0}&",SN=0,TP=TRUNK,RCN="&LEFT(B{0},1)&RIGHT(B{0},1)&",PS=0,RT=AIRU,RS=NO,RN="""&C{0}&""",RXNUM=64,TXNUM=64,MNTMODE=NORMAL,RFDCPWROFFALMDETECTSW=OFF,RFTXSIGNDETECTSW=OFF,DORMANCYSW=OFF;"'''.format(i+2)
newdf.loc[i,'加扇区'] = '''="ADD SECTOR: SECTORID="&N{0}&", ANTNUM=0, CREATESECTOREQM=FALSE;ADD SECTOREQM: SECTOREQMID="&N{0}&", SECTORID="&N{0}&", ANTCFGMODE=BEAM, RRUCN=0, RRUSRN="&D{0}&", RRUSN=0, BEAMSHAPE=SEC_120DEG, BEAMLAYERSPLIT=None, BEAMAZIMUTHOFFSET=None;"'''.format(i+2)
newdf.loc[i,'绑定'] = '''="ADD NRDUCELLCOVERAGE:NRDUCELLTRPID="&L{0}&",NRDUCELLCOVERAGEID="&L{0}&",SECTOREQMID="&N{0}&";"'''.format(i+2)
newdf.loc[i,'流量上报'] = '''="MOD NRCELLNSADCCONFIG:NRCELLID="&L{0}&",NRDATAVOLUMERPTCFG=600;MOD NRDUCELLUEPWRSAVING:NRDUCELLID="&L{0}&",NRDUCELLDRXALGOSWITCH=BASIC_DRX_SW-1&LNR_DRX_SYNCHRONIZE_SW-0;"'''.format(i+2)
newdf.loc[i,'NRDUCELLOP'] = '''="ADD NRDUCELLOP:NRDUCELLID="&L{0}&",OPERATORID=0;ADD NRDUCELLOP:NRDUCELLID="&L{0}&",OPERATORID=1;"'''.format(i+2)
newdf.loc[i,'TAC绑定切片'] = '''="ADD GNBTANS:TRACKINGAREAID="&H{0}&",OPERATORID=0,SLICESERVICETYPE=1,SLICEDIFFERENTIATOR=0;ADD GNBTANS:TRACKINGAREAID="&H{0}&",OPERATORID=0,SLICESERVICETYPE=1,SLICEDIFFERENTIATOR=262144;ADD GNBTANS:TRACKINGAREAID="&H{0}&",OPERATORID=1,SLICESERVICETYPE=1,SLICEDIFFERENTIATOR=0;ADD GNBTANS:TRACKINGAREAID="&H{0}&",OPERATORID=1,SLICESERVICETYPE=1,SLICEDIFFERENTIATOR=4194304;"'''.format(i+2)
newdf.loc[i,'位置'] = '''="ADD LOCATION:LOCATIONNAME="""&G{0}&"_AAU_0_"&D{0}&"_0"",MODE=MANUAL,GCDF=Degree,LATITUDEDEGFORMAT=31"&RIGHT(F{0},LEN(F{0})-3)&",LONGITUDEDEGFORMAT=121"&RIGHT(E{0},LEN(E{0})-4)&",LOCATIONTYPE=RFSITE;MOD RRU:CN=0,SRN="&D{0}&",SN=0,LOCATIONNAME="""&G{0}&"_AAU_0_"&D{0}&"_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]]
fille = PatternFill('solid',fgColor='ff7f00')#设置填充色为珊瑚红
fillee = PatternFill('solid',fgColor='FFFF00')#设置填充色为黄色
for j in range(7,15):
sheet1.cell(row=1,column=j).fill = fille
for k in range(15,26):
sheet1.cell(row=1,column=k).fill = fillee
#font = Font(u'宋体',size=11,bold=True,italic=False,strike=False,color='9c0006')#设置字体为黄色
#sheet1.cell(row=1,column=2,value='哈哈').font = font
wk.save(file_name)
file_name,title = r'C:\Users\xoadmin\Desktop\Newname.xlsx','Sheet1'
color(file_name,title)
print("---------------------------------------------\n数据提取成功已输出到桌面！！！\n按Enter键退出。。。。。。\n---------------------------------------------")
input()