删除ONU:
from datetime import datetime
import xlrd,xlwt
from xlutils.copy import copy
#___________________________________________________________________________#
# 获取时间戳
Timesuffixa= datetime.now().strftime(" %Y-%m-%d %H-%M-%S")
Timesuffix= datetime.now().strftime(" %m-%d %H-%M-%S")
# 输入文件
PATH_I=input("\n输入[ONU在线情况]:")
PATH_I=PATH_I.strip('"')
# 制定输出文件名
FG_X = PATH_I.rfind("\\")
Du_P = PATH_I[:FG_X+1]
Du_F = PATH_I[FG_X+1:]
PATH_O =Du_P+'命令输出'+Timesuffix+'.xls'
#___________________________________________________________________________#
# 读写工作簿
wb=xlrd.open_workbook(PATH_I) # 获取工作簿对象
ws=wb.sheet_by_name('sheet1') # 获取工作表对象
# 复制工作簿、新建工作表
nwb=copy(wb);
nws=nwb.add_sheet('编辑')
#___________________________________________________________________________#
nR=ws.nrows # 读取工作表的行数
j=2 # 写入工作表的起始行序
for i in range(1,nR):
ShuJu=ws.row_values(i)
ML_1="interface gpon_olt-1/"+ShuJu[7]+"/"+ShuJu[9]
ML_2="no onu "+ShuJu[10]
nws.write(j,0,ShuJu[17])##
nws.write(j,2,ML_1)
nws.write(j+1,2,ML_2)
nws.write(j+2,2,"exit")
ML1="show running-config-interface gpon_onu-1/"+ShuJu[7]+"/"+ShuJu[9]+":"+ShuJu[10]
ML2="remote-unit update-and-reboot F620V2P6T1.bin gpon_olt-1/"+ShuJu[7]+"/"+ShuJu[9]+" "+ShuJu[10]
ML3="show remote-unit update-status gpon_olt-1/"+ShuJu[7]+"/"+ShuJu[9]+" "+ShuJu[10]
nws.write(j,5,ML1)
nws.write(j+1,5,ML2)
nws.write(j+2,5,ML3)
j=j+3
#___________________________________________________________________________#
nws.write(0,0,"认证值")
nws.write(0,2,"删猫命令")
nws.write(0,5,"升级相关命令")
# 输出工作簿
nwb.save(PATH_O)
print('\n\n恭喜!程序执行完毕!')
print('······')
print('······')
print('······')
print("\n提示输出文件:")
print(' 命令输出'+Timesuffix+'.xls\n')
号码数据:
import xlrd,xlwt
from xlutils.copy import copy
from datetime import datetime
#___________________________________________________________________________#
# 获取时间戳
Timesuffixa= datetime.now().strftime(" %Y-%m-%d %H-%M-%S")
Timesuffix= datetime.now().strftime(" %m-%d %H-%M-%S")
# 输入文件
PATH_I=input("\n输入Excel名称:")
PATH_I=PATH_I.strip('"')
# 制定输出文件名
FG_X = PATH_I.rfind("\\")
Du_P = PATH_I[:FG_X+1]
Du_F = PATH_I[FG_X+1:]
PATH_O =Du_P+'命令输出'+Timesuffix+'.xls'
#___________________________________________________________________________#
# 获取工作簿对象
Input_wb=xlrd.open_workbook(PATH_I) # 存储着 号码、命令模板
# 获取工作表对象
SJ_ws=Input_wb.sheet_by_name("输入数据")
MB_ws=Input_wb.sheet_by_name("网管模板")
nR=SJ_ws.nrows
TiHuan=['领','寺','罢','而','留','屋','的','散','起','意','室','就','分','秒']
HML=[]
for i in range(1,nR):
ShuJu=SJ_ws.row_values(i)
if (ShuJu[6]=="" and ShuJu[4]==""):
MoBan=MB_ws.col_values(0)
for mb in MoBan[1:]:
j=0
for sj in ShuJu:
mb=mb.replace(TiHuan[j],sj)
j=j+1
HML.append(mb)
HML.append("")
HML.append("")
elif (ShuJu[6]=="" and ShuJu[4]!=""):
MoBan=MB_ws.col_values(1)
for mb in MoBan[1:]:
j=0
for sj in ShuJu:
mb=mb.replace(TiHuan[j],sj)
j=j+1
HML.append(mb)
HML.append("")
HML.append("")
elif (ShuJu[6]!="" and ShuJu[4]==""):
MoBan=MB_ws.col_values(2)
for mb in MoBan[1:]:
j=0
for sj in ShuJu:
mb=mb.replace(TiHuan[j],sj)
j=j+1
HML.append(mb)
HML.append("")
HML.append("")
else:
MoBan=MB_ws.col_values(3)
for mb in MoBan[1:]:
j=0
for sj in ShuJu:
mb=mb.replace(TiHuan[j],sj)
j=j+1
HML.append(mb)
HML.append("")
HML.append("")
# 复制工作簿、新建工作表
Output_wb=copy(Input_wb)
SCML_ws=Output_wb.get_sheet("输出命令")
# 将生成的列表写到xls表格中
x=0
for hml in HML:
SCML_ws.write(x,0,hml)
x=x+1
# 获取时间戳
Timesuffixa= datetime.now().strftime(" %Y-%m-%d %H-%M-%S")
Timesuffix= datetime.now().strftime(" %m-%d %H-%M-%S")
# 输出工作簿
Output_wb.save(PATH_O)
print('\n\n恭喜!程序执行完毕!')
print('······')
print('······')
print('······')
print("\n提示输出文件:")
print(' 命令输出'+Timesuffix+'.xls\n')
input("请按任意键退出程序……")
查询光猫VLAN:
import xlrd,xlwt
from xlutils.copy import copy
# 输入文件
PATH_I=input("\n输入NAME:")
PATH_I=PATH_I.strip('"')
# 制定输出文件名
FG_X = PATH_I.rfind("\\")
Du_P = PATH_I[:FG_X+1]
Du_F = PATH_I[FG_X+1:]
PATH_O =Du_P+"OutPut_"+Du_F
# 读写工作簿
wb=xlrd.open_workbook(PATH_I) # 获取工作簿对象
ws=wb.sheet_by_name('ONU VLAN资源查询') # 获取工作表对象
# 复制工作簿、新建工作表
nwb=copy(wb);
nws=nwb.add_sheet('编辑')
# 获取原始SN列值
SN_Y=ws.col_values(10) # SN列值
# 筛选SN的唯一值
SN_N=[]
for sn in SN_Y:
if sn not in SN_N:
SN_N.append(sn) # 唯一的SN码存在SN_N列表
# 将唯一的SN码写入工作表
j=0 # 行号索引
for sn in SN_N:
nws.write(j,0,sn)
j=j+1
# 逐行填表
only_nR=len(SN_N) # 唯一SN码的长度(含有两行标题)
ws_nR=ws.nrows # 输入表格的行数
for i_sn in range(2,only_nR):
temp=[""]*1000 # 临时列表,存储当前行所填的信息(以核验指定单元格是否有值)
for i_ws in range(2,ws_nR):
ShuJu=ws.row_values(i_ws)
# 扫描此行,填写提供的值
if (ShuJu[10]==SN_N[i_sn]):
if (ShuJu[14]=="3998"):
i=0
if (temp[i]==""):
temp[i]="3998"
nws.write(i_sn,i+4,"3998")
else:
while (temp[i]!=""):
i=i+5
temp[i]="3998"
nws.write(i_sn,i+4,"3998")
if (ShuJu[14]=="588"):
i=1
if (temp[i]==""):
temp[i]="588"
nws.write(i_sn,i+4,"588")
else:
while (temp[i]!=""):
i=i+5
temp[i]="588"
nws.write(i_sn,i+4,"588")
if (ShuJu[17]!="--"):
i=2
if (temp[i]==""):
temp[i]=ShuJu[14]
nws.write(i_sn,i+4,ShuJu[14])
temp[i]=ShuJu[17]
nws.write(i_sn,i+5,ShuJu[17])
else:
while (temp[i]!=""):
i=i+5
temp[i]=ShuJu[14]
nws.write(i_sn,i+4,ShuJu[14])
temp[i]=ShuJu[17]
nws.write(i_sn,i+5,ShuJu[17])
if (ShuJu[17]=="--" and ShuJu[14]!="588" and ShuJu[14]!="3998"):
i=4
if (temp[i]==""):
temp[i]="语音"
nws.write(i_sn,i+4,"语音")
else:
while (temp[i]!=""):
i=i+5
temp[i]="语音"
nws.write(i_sn,i+4,"语音")
# 保存工作簿
nwb.save(PATH_O)
print("\n\n恭喜!程序执行完毕!")
print("······")
print("······")
print("······")
print("\n提示输出文件:")
print(" "+"OutPut_"+Du_F+"\n")
print(" ")
print(" ")
print("------------------------------------")
print(" ")
input("请按任意键退出程序……")
报表自动化:
import xlrd,xlwt
from xlutils.copy import copy
Suffix=input("\n请输入日期后缀【例:0314】:")
# 获取工作簿对象
tj_wb=xlrd.open_workbook("【勿删】数字统计(原表).xls")
xi_wb=xlrd.open_workbook("工单查询修机单列表"+Suffix+".xls")
zg_wb=xlrd.open_workbook("工单查询装机单列表"+Suffix+".xls")
# 获取工作表对象
tj_ws=tj_wb.sheet_by_name("报表")
xi_ws=xi_wb.sheet_by_name("工单查询修机单列表"+Suffix)
zg_ws=zg_wb.sheet_by_name("工单查询装机单列表"+Suffix)
xi_nR=xi_ws.nrows
zg_nR=zg_ws.nrows
ry_nR=tj_ws.nrows
ry=tj_ws.col_values(0) # 人员
# 复制工作簿、新建工作表
Output_wb=copy(tj_wb)
Output_ws=Output_wb.get_sheet("报表")
for i_ry in range(1,ry_nR): # 处理装机表格
zg_val=0
yi_val=0
fr_val=0
for i_zg in range(1,zg_nR):
ShuJu=zg_ws.row_values(i_zg)
if (ShuJu[5]==ry[i_ry] and ShuJu[4]=="装"):
zg_val=zg_val+1
if (ShuJu[5]==ry[i_ry] and ShuJu[4]=="移"):
yi_val=yi_val+1
if (ShuJu[5]==ry[i_ry] and ShuJu[31]=="是"):
fr_val=fr_val+1
Output_ws.write(i_ry,1,zg_val)
Output_ws.write(i_ry,2,yi_val)
Output_ws.write(i_ry,5,fr_val)
for i_ry in range(1,ry_nR): # 处理修机表格
xi_val=0
for i_xi in range(1,xi_nR):
ShuJu=xi_ws.row_values(i_xi)
if (ShuJu[4]==ry[i_ry]):
xi_val=xi_val+1
Output_ws.write(i_ry,3,xi_val)
# 输出工作簿
Output_wb.save("输出 · "+Suffix+" · 数字统计报表.xls")
print("\n\n恭喜!程序执行完毕!")
print("······")
print("······")
print("······")
print("\n提示输出文件:")
print(" 输出 · "+Suffix+" · 数字统计报表.xls\n")
print(" ")
print(" ")
print("------------------------------------")
print(" ")
input("请按任意键退出程序……")