import datetime
import time
import pymssql
import xlwt
from xlwt import *
import os
today=datetime.date.today().strftime('%Y%m%d')
pathMAC = "/root/QCMC/EXCEL/"+today
if os.access(pathMAC,os.F_OK):
print('目录存在')
else:
os.mkdir(pathMAC,777)
print("新建目录")
class database(object):#定义数据库类
def __init__(self, host, user, password,database):
self.host = host
self.user = user
self.password = password
self.database = database
def sql_con(self):
self.conn = pymssql.connect(host=self.host, user=self.user, password=self.password, database=self.database, timeout=1,login_timeout=1, charset='utf8')
self.cursor = self.conn.cursor()
def MACSQL_Auto(self):#匹配DHCP,获取自动获取MAC的信息
sql = "select Area,Hostname,mac,ip,port,Switchname,Floor,AddressState,LeaseExpiryTime from [dbo].[QCMCMAC] join [dbo].[DHCP] on MAC = MACADD order by Switchname,Port,mac"
self.cursor.execute(sql)
M = self.cursor.fetchall()
return M
def MACSQL_Manual(self):#匹配DHCP,获取手动绑定MAC信息
sql = "select * from (select Area,MACADD,port,ip,Switchname from [dbo].[QCMCMAC] left join [dbo].[DHCP] on MAC = MACADD ) a where a.ip is null order by Switchname,Port"
self.cursor.execute(sql)
N = self.cursor.fetchall()
return N
def SQL_DHCP(self):#DHCP数据库是否有END信息(DHCP数据抓取完成后,表格会写入END判断是否抓取完成)
sql = "select * from [dbo].[DHCP] where MAC = 'END'"
self.cursor.execute(sql)
O = self.cursor.fetchall()
return O
def SQL_Close(self):#关闭数据库
self.conn.close()
class Excel_Table(object):#自动获取excel表格类
def __init__(self):
self.workbook = xlwt.Workbook()
self.style = XFStyle()
self.pattern = Pattern()
self.pattern.pattern = Pattern.SOLID_PATTERN
self.pattern.pattern_fore_colour = Style.colour_map['red'] # 设置单元格背景色为红色
self.borders = xlwt.Borders()
self.borders.left = 1
self.borders.right = 1
self.borders.top = 1
self.borders.bottom = 1
self.style.borders = self.borders
self.font = xlwt.Font()
self.font.name = 'Arial' # 字体类型
self.font.colour_index = 0 # 字体颜色
self.font.height = 280 # 字体大小
self.style.font = self.font
self.style.pattern = self.pattern
self.style1 = XFStyle()
self.borders = xlwt.Borders()
self.borders.left = 1
self.borders.right = 1
self.borders.top = 1
self.borders.bottom = 1
self.style1.borders = self.borders
def sheetstyle(self,style,sheetname):#sheet的格式
if style == 'Auto':#自动获取的表格格式
Autosheet = self.workbook.add_sheet(sheetname, cell_overwrite_ok=True)
first_col = Autosheet.col(0)
sec_col = Autosheet.col(1)
thr_col = Autosheet.col(2)
for_col = Autosheet.col(3)
fir_col = Autosheet.col(4)
six_col = Autosheet.col(5)
sev_col = Autosheet.col(6)
first_col.width = 340 * 25
sec_col.width = 220 * 25
thr_col.width = 160 * 25
for_col.width = 220 * 25
fir_col.width = 220 * 25
six_col.width = 180 * 25
sev_col.width = 240 * 25
Autosheet.write(0, 0, 'HostName', self.style )
Autosheet.write(0, 1, 'MACAddress', self.style)
Autosheet.write(0, 2, 'IPAddress', self.style)
Autosheet.write(0, 3, 'Port', self.style)
Autosheet.write(0, 4, 'SwitchName', self.style)
Autosheet.write(0, 5, 'AddressState', self.style)
Autosheet.write(0, 6, 'LeaseExpiryTime', self.style)
return Autosheet
if style == 'Manual':#手动绑定的表格格式
Manualsheet = self.workbook.add_sheet(sheetname, cell_overwrite_ok=True)
first_col =Manualsheet.col(0)
sec_col = Manualsheet.col(1)
thr_col = Manualsheet.col(2)
for_col = Manualsheet.col(3)
first_col.width = 220 * 25
sec_col.width = 240 * 25
thr_col.width = 60 * 25
for_col.width = 220 * 25
Manualsheet.write(0, 0, 'MACAddress', self.style)
Manualsheet.write(0, 1, 'Port', self.style)
Manualsheet.write(0, 2, 'IP', self.style)
Manualsheet.write(0, 3, 'SwitchName', self.style)
return Manualsheet
def Autosheet_insert(self,Autosheet,HostName, MACAddress, IPAddress, Port, SwitchName, AddressState, LeaseExpiryTime, index):#自动获取表格插入数据
Autosheet.write(index, 0, HostName, self.style1)
Autosheet.write(index, 1, MACAddress, self.style1)
Autosheet.write(index, 2, IPAddress, self.style1)
Autosheet.write(index, 3, Port, self.style1)
Autosheet.write(index, 4, SwitchName, self.style1)
Autosheet.write(index, 5, AddressState, self.style1)
Autosheet.write(index, 6, LeaseExpiryTime, self.style1)
def Manualsheet_insert(self,Manualsheet,MACAddress,Port,IP,SwitchName,index):#手动绑定表格插入数据
Manualsheet.write(index, 0, MACAddress, self.style1)
Manualsheet.write(index, 1, Port, self.style1)
Manualsheet.write(index, 2, IP, self.style1)
Manualsheet.write(index, 3, SwitchName, self.style1)
def SaveExcel(self):
self.workbook.save('/root/QCMC/EXCEL/'+today+'/'+'%sQCMCSitchMAC.xls'%today)
MACdata = database('X.X.X.X', 'X', 'X', 'X')
MACdata.sql_con()
line=MACdata.SQL_DHCP()
if 'END' in line[0]:
Excel = Excel_Table()
Autolist=MACdata.MACSQL_Auto()
index=index1=index2=index3=index4=index5=index6=index7=index8=index9=index10=index11=index12=index13=1
F11FSwitch = Excel.sheetstyle('Auto', 'F11FSwitch')
F12FSwitch = Excel.sheetstyle('Auto', 'F12FSwitch')
F13FSwitch = Excel.sheetstyle('Auto', 'F13FSwitch')
F31FSwitch = Excel.sheetstyle('Auto', 'F31FSwitch')
F32FSwitch = Excel.sheetstyle('Auto', 'F32FSwitch')
F33FSwitch = Excel.sheetstyle('Auto', 'F33FSwitch')
F34FSwitch = Excel.sheetstyle('Auto', 'F34FSwitch')
F51FSwitch = Excel.sheetstyle('Auto', 'F51FSwitch')
F52FSwitch = Excel.sheetstyle('Auto', 'F52FSwitch')
F53FSwitch = Excel.sheetstyle('Auto', 'F53FSwitch')
F54FSwitch = Excel.sheetstyle('Auto', 'F54FSwitch')
for Autoline in Autolist:
if 'F11F' in Autoline:
Excel.Autosheet_insert(F11FSwitch,Autoline[1], Autoline[2], Autoline[3],Autoline[4], Autoline[5], Autoline[7], Autoline[8], index)
index += 1
if 'F12F' in Autoline:
Excel.Autosheet_insert(F12FSwitch,Autoline[1], Autoline[2], Autoline[3],Autoline[4], Autoline[5], Autoline[7], Autoline[8], index1)
index1 += 1
if 'F13F' in Autoline:
Excel.Autosheet_insert(F13FSwitch,Autoline[1], Autoline[2], Autoline[3],Autoline[4], Autoline[5], Autoline[7], Autoline[8], index2)
index2 += 1
if 'F31F' in Autoline:
Excel.Autosheet_insert(F31FSwitch,Autoline[1], Autoline[2], Autoline[3],Autoline[4], Autoline[5], Autoline[7], Autoline[8], index3)
index3 += 1
if 'F32F' in Autoline:
Excel.Autosheet_insert(F32FSwitch,Autoline[1], Autoline[2], Autoline[3],Autoline[4], Autoline[5], Autoline[7], Autoline[8], index4)
index4 += 1
if 'F33F' in Autoline:
Excel.Autosheet_insert(F33FSwitch,Autoline[1], Autoline[2], Autoline[3],Autoline[4], Autoline[5], Autoline[7], Autoline[8], index5)
index5 += 1
if 'F34F' in Autoline:
Excel.Autosheet_insert(F34FSwitch,Autoline[1], Autoline[2], Autoline[3],Autoline[4], Autoline[5], Autoline[7], Autoline[8], index6)
index6 += 1
if 'F51F' in Autoline:
Excel.Autosheet_insert(F51FSwitch,Autoline[1], Autoline[2], Autoline[3],Autoline[4], Autoline[5], Autoline[7], Autoline[8], index7)
index7 += 1
if 'F52F' in Autoline:
Excel.Autosheet_insert(F52FSwitch,Autoline[1], Autoline[2], Autoline[3],Autoline[4], Autoline[5], Autoline[7], Autoline[8], index8)
index8 += 1
if 'F53F' in Autoline:
Excel.Autosheet_insert(F53FSwitch,Autoline[1], Autoline[2], Autoline[3],Autoline[4], Autoline[5], Autoline[7], Autoline[8], index9)
index9 += 1
if 'F54F' in Autoline:
Excel.Autosheet_insert(F54FSwitch,Autoline[1], Autoline[2], Autoline[3],Autoline[4], Autoline[5], Autoline[7], Autoline[8], index10)
index10 += 1
Manuallist = MACdata.MACSQL_Manual()
F1Manual = Excel.sheetstyle('Manual', 'F1Manual')
F3Manual = Excel.sheetstyle('Manual', 'F3Manual')
F5Manual = Excel.sheetstyle('Manual', 'F5Manual')
for Manualine in Manuallist:
if 'F1' in Manualine:
Excel.Manualsheet_insert(F1Manual, Manualine[1], Manualine[2], 'NUll', Manualine[4],index11)
index11 += 1
if 'F3' in Manualine:
Excel.Manualsheet_insert(F3Manual, Manualine[1], Manualine[2], 'NUll', Manualine[4],index12)
index12 += 1
if 'F5' in Manualine:
Excel.Manualsheet_insert(F5Manual, Manualine[1], Manualine[2], 'NUll', Manualine[4],index13)
index13 += 1
Excel.SaveExcel()
print('表格创建完成')
else:
print('DHCP数据不完整')
exit()
本段代码就是把交换机里面的MAC地址信息和DHCP服务器信息结合,做生成Excel表格。表格发送邮件需要,视需求而定。