改进

1、以前的代码比较适合交换机少的情况，一个交换机写一个代码，现在有70多台交换机，把交换机的信息导入sql数据库，然后用一段代码写完。（数据库真的很好用，导入修改都很方便，以后新增交换机，修改数据库就可以了）
2、生成的excel文档，行数太多，根据楼层，分层多个页，每页就不会有很多行。

代码

抓取交换机信息代码，改进。

import pexpect
import sys
import datetime
import os
import pymssql
today=datetime.date.today().strftime('%Y%m%d')
path = "/root/F5/"+today
os.mkdir(path,777)
passwd='xxx'
def Switch(S,I,N):#循环找到数据库每行里面对应的数据，交换机的名字、IP等。
    try:#遇到错误信息，直接掠过，防止因一个交换机信息有误，导致程序不能继续进行。
        name1="---- More ----"
        child=pexpect.spawn('telnet %s'%I)#循环telnetIP地址
        fout=open('/root/F5/'+today+'/'+'%s-%s.txt'%(S,I),'wb+')
        child.logfile = fout
        child.expect('Username:')
        child.sendline("admin")
        child.expect('(?i)ssword:')
        child.sendline("%s"%passwd)
        child.expect('<%s>'%N)
        child.sendline("dis mac-address")
        for i in range(10):
          index = child.expect([name1,'<%s>'%N])
          if ( index == 0 ):
            child.send(" ")
          else:
            child.sendline("quit")  
            break#不能用sys.exit()
    except:
         pass

host = 'x.x.x.x'
user = 'sa'
pwd = 'xxxxxx'
db = 'MAC'
conn = pymssql.connect(host=host,user=user,password=pwd,database=db,timeout=1,login_timeout=1,charset="utf8")
cur = conn.cursor()
sqls ="select * from [dbo].[F5Switch]"
cur.execute(sqls)
listall = cur.fetchall()
for line in listall:
    Switch(line[0],line[1],line[3])#循环输出sql数据库里面每一行的内容
conn.commit()
conn.close()

代码

抓取两个表中MAC地址一样的信息，并串接成一个表，并做成excel。改进：增加第一行颜色，更改行宽，分成多个表格。

import pymssql
import xlwt
import datetime
from xlwt import *

workbook = xlwt.Workbook()

style = XFStyle()
pattern = Pattern()
pattern.pattern = Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = Style.colour_map['red'] #设置单元格背景色为红色
style.pattern = pattern

today=datetime.date.today().strftime('%Y%m%d')
F51FSwitch = workbook.add_sheet('F51FSwitch',cell_overwrite_ok=True)#创建F51FSwtich
first_col=F51FSwitch.col(0)#定义第一列，xlwt中是行和列都是从0开始计算的
sec_col=F51FSwitch.col(1)
thr_col=F51FSwitch.col(2)
for_col=F51FSwitch.col(3)
fir_col=F51FSwitch.col(4)
first_col.width=460*25#设定列宽，460为衡量单位，25表示25个字符宽度（我也没搞清楚这数字是啥意思，我自己大概试的）
sec_col.width=256*25
thr_col.width=200*25
for_col.width=256*25
fir_col.width=256*25

F51FSwitch.write(0,0,'HotName',style=style)#第一行第一列的单元格添加颜色
F51FSwitch.write(0,1,'MACAddress',style=style)#第一行第二列的单元格添加颜色
F51FSwitch.write(0,2,'IPAddress',style=style)
F51FSwitch.write(0,3,'Port',style=style)
F51FSwitch.write(0,4,'SwitchName',style=style)

F52FSwitch = workbook.add_sheet('F52FSwitch',cell_overwrite_ok=True)
first_col=F52FSwitch.col(0)
sec_col=F52FSwitch.col(1)
thr_col=F52FSwitch.col(2)
for_col=F52FSwitch.col(3)
fir_col=F52FSwitch.col(4)
first_col.width=460*25
sec_col.width=256*25
thr_col.width=200*25
for_col.width=256*25
fir_col.width=256*25
F52FSwitch.write(0,0,'HotName',style=style)
F52FSwitch.write(0,1,'MACAddress',style=style)
F52FSwitch.write(0,2,'IPAddress',style=style)
F52FSwitch.write(0,3,'Port',style=style)
F52FSwitch.write(0,4,'SwitchName',style=style)

F53FSwitch = workbook.add_sheet('F53FSwitch',cell_overwrite_ok=True)
first_col=F53FSwitch.col(0)
sec_col=F53FSwitch.col(1)
thr_col=F53FSwitch.col(2)
for_col=F53FSwitch.col(3)
fir_col=F53FSwitch.col(4)
first_col.width=460*25
sec_col.width=256*25
thr_col.width=200*25
for_col.width=256*25
fir_col.width=256*25
F53FSwitch.write(0,0,'HotName',style=style)
F53FSwitch.write(0,1,'MACAddress',style=style)
F53FSwitch.write(0,2,'IPAddress',style=style)
F53FSwitch.write(0,3,'Port',style=style)
F53FSwitch.write(0,4,'SwitchName',style=style)

F54FSwitch = workbook.add_sheet('F54FSwitch',cell_overwrite_ok=True)
first_col=F54FSwitch.col(0)
sec_col=F54FSwitch.col(1)
thr_col=F54FSwitch.col(2)
for_col=F54FSwitch.col(3)
fir_col=F54FSwitch.col(4)
first_col.width=460*25
sec_col.width=256*25
thr_col.width=200*25
for_col.width=256*25
fir_col.width=256*25
F54FSwitch.write(0,0,'HotName',style=style)
F54FSwitch.write(0,1,'MACAddress',style=style)
F54FSwitch.write(0,2,'IPAddress',style=style)
F54FSwitch.write(0,3,'Port',style=style)
F54FSwitch.write(0,4,'SwitchName',style=style)


def exceladd(HotName,MACAddress,IPAddress,Port,SwitchName,index):#给F51FSwitch表格插入数据
    F51FSwitch.write(index,0,HotName)
    F51FSwitch.write(index,1,MACAddress)
    F51FSwitch.write(index,2,IPAddress)
    F51FSwitch.write(index,3,Port)
    F51FSwitch.write(index,4,SwitchName)

def exceladd1(HotName,MACAddress,IPAddress,Port,SwitchName,index):
    F52FSwitch.write(index1,0,HotName)
    F52FSwitch.write(index1,1,MACAddress)
    F52FSwitch.write(index1,2,IPAddress)
    F52FSwitch.write(index1,3,Port)
    F52FSwitch.write(index1,4,SwitchName)

def exceladd2(HotName,MACAddress,IPAddress,Port,SwitchName,index):
    F53FSwitch.write(index2,0,HotName)
    F53FSwitch.write(index2,1,MACAddress)
    F53FSwitch.write(index2,2,IPAddress)
    F53FSwitch.write(index2,3,Port)
    F53FSwitch.write(index2,4,SwitchName)

def exceladd3(HotName,MACAddress,IPAddress,Port,SwitchName,index):
    F54FSwitch.write(index3,0,HotName)
    F54FSwitch.write(index3,1,MACAddress)
    F54FSwitch.write(index3,2,IPAddress)
    F54FSwitch.write(index3,3,Port)
    F54FSwitch.write(index3,4,SwitchName)


host = 'x.x.x.x'
user = 'sa'
pwd = 'xxx'
db = 'MAC'
conn = pymssql.connect(host=host,user=user,password=pwd,database=db,timeout=1,login_timeout=1,charset="utf8")
cur = conn.cursor()
sqls ="select Hostname,mac,ip,port,Switchname,name from [dbo].[MACF5] join [dbo].[DHCP] on MAC = MACADD where Port<>'GigabitEthernet1/0/24' order by Switchname,Port"
cur.execute(sqls)

listall = cur.fetchall()
index = 1
index1 = 1
index2 = 1
index3 = 1
for line in listall:
    if 'F51F'in line: 
        exceladd(line[0],line[1],line[2],line[3],line[4],index)
        index += 1
for line1 in listall:
    if 'F52F'in line1:
        exceladd1(line1[0],line1[1],line1[2],line1[3],line1[4],index1)
        index1 += 1
for line2 in listall:
    if 'F53F'in line2: 
        exceladd2(line2[0],line2[1],line2[2],line2[3],line2[4],index2)
        index2 += 1
for line3 in listall:
    if 'F54F'in line3:
        exceladd3(line3[0],line3[1],line3[2],line3[3],line3[4],index3)
        index3 += 1
conn.commit()
conn.close()

print ('创建excel文件完成！')
workbook.save('/root/F5/%sF5MAC.xls'%today)

数据库部分截图

1、F5Switch表

2、MACF5表

3、DHCP表

成果

分页的excel文档