非法客户端准入控制（二）

最新推荐文章于 2019-04-30 16:50:55 发布

谢冰棍

最新推荐文章于 2019-04-30 16:50:55 发布

阅读量422

点赞数 2

分类专栏：运维 Python 文章标签： Python xlwt 运维

本文链接：https://blog.csdn.net/weixin_42551826/article/details/84621613

版权

运维同时被 2 个专栏收录

8 篇文章 5 订阅

订阅专栏

Python

3 篇文章 0 订阅

订阅专栏

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表格。表格发送邮件需要，视需求而定。

谢冰棍

关注

2
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
非法客户端准入控制（二）

import datetimeimport timeimport pymssqlimport xlwtfrom xlwt import *import ostoday=datetime.date.today().strftime('%Y%m%d')pathMAC = "/root/QCMC/EXCEL/"+todayif os.access(pathMAC,os.F_OK):...
复制链接

扫一扫