Python巡检关于Excel表格操作

import psutil
import time
import xlwt
import platform
from subprocess import Popen, PIPE


def getoutput(command):
    print("开始执行命令：%s" %command)
    comm = Popen(command, stdout=PIPE, stdin=PIPE, stderr=PIPE, shell=True)
    out, err = comm.communicate()
    if err:
        print("执行命令失败：%s"% command)
    else:
        return out


def get_os_info():
    sys = platform.system()
    ip = getoutput("ifconfig eth0 |awk -F '[ :]+' 'NR==2{print $4}'")
    ip = str(ip, encoding='utf-8').strip()
    return sys, ip


def get_time():
    now_time = time.strftime('%Y-%m-%d %H:%M:%S')
    return now_time


def get_CPU():
    cpu = "CPU:%s" % int(psutil.cpu_percent(interval=0)) + "%"
    return cpu


def get_memory():
    data = psutil.virtual_memory() #获取内存完整信息
    memory = "Memory:%s" % (int((data.percent))) + "%"
    return memory


def get_dick():
    dick_1 = []
    dick1 = getoutput("df -h |grep '%'| awk -F ' ' 'NR>2{print $5, $6}'")
    #print(type(dick1))
    str1 = str(dick1, encoding='utf-8')
    dick1 = str1.strip().split('\n')
    #['Use% Mounted', '/ ', '0% /dev/shm', '9% /boot', '100% /mnt', '']
    return dick1


def get_process():
    com = getoutput('ps -ef |grep ssh |wc -l' )
    return com

def dict():
    info = []
    info_content = []
    # get_time = get_time()
    get_info = get_os_info()
    # interval=1
    os_sys = list(get_info)[0]
    os_ip = list(get_info)[1]
    info.append(os_sys)
    info_content.append(os_ip)

    CUP = get_CPU()
    # print(CUP.split(':')[0])
    info.append(CUP.split(':')[0])
    info_content.append(CUP.split(':')[1])

    MEM = get_memory()
    info.append(MEM.split(':')[0])
    info_content.append(MEM.split(':')[1])
    # print(info_content)
    # print(info)

    Dick = get_dick()
    dick = int(len(Dick))
    for i in range(dick):
        info.append(Dick[i].split(' ')[1])
        info_content.append(Dick[i].split(' ')[0])

    dict = {}
    for i in range(len(info)):
        dict[info[i]] = info_content[i]
    return  dict, info, info_content
# 百分数转为int
def compare_int(string):
    if "%" in string:
        newint = int(string.strip("%")) / 100
        return newint
    else:
        print("你输入的不是百分比！")

if __name__ == "__main__":

    dict_1 = dict()  #所有信息
    time = get_time()
    workbook = xlwt.Workbook(encoding='utf-8')
    worksheet = workbook.add_sheet('每日巡检', cell_overwrite_ok=True)
    # 设置字体
    #style = xlwt.XFStyle()  # 初始化样式
    font = xlwt.Font()  # 为样式创建字体
    font.name = '宋体'
    font.bold = True  # 黑体
    # 对其方式
    al = xlwt.Alignment()
    al.horz = 0x02  # 设置水平居中
    al.vert = 0x01  # 设置垂直居中
    # 添加边框
    borders = xlwt.Borders()  # Create Borders
    borders.left = xlwt.Borders.THIN
    borders.right = xlwt.Borders.THIN
    borders.top = xlwt.Borders.THIN
    borders.bottom = xlwt.Borders.THIN
    #背景色红色
    pattern = xlwt.Pattern()
    pattern.pattern = xlwt.Pattern.SOLID_PATTERN
    pattern.pattern_fore_colour = 10 #红色
    #背景色2
    pattern2 = xlwt.Pattern()
    pattern2.pattern = xlwt.Pattern.SOLID_PATTERN
    pattern2.pattern_fore_colour = 23  # 浅色
    # 初始化样式
    style1 = xlwt.XFStyle() #字体
    style1.font = font
    style1.alignment = al

    style2 = xlwt.XFStyle()
    style2.font = font
    style2.alignment = al
    style2.borders = borders
    style2.pattern = pattern2

    style3 = xlwt.XFStyle()
    style3.font = font
    style3.alignment = al
    style3.borders = borders

    #红色
    style4 = xlwt.XFStyle()
    style4.alignment = al
    style4.borders = borders
    style4.pattern = pattern

    result = ['序号', "ip地址", '操作系统版本', 'CPU使用率(<80%)', '内存使用率(<80*)', '/boot使用率(<80%)', '/mnt使用率(<80%)','ssh进程(存活)']
    worksheet.write(0, 0, '%s' %time, style1) # 带样式的写入
    result_1 = len(result)
    #print(result)
    for i in range(result_1):
        #print(result[i])
        # 设置首列的宽度
        fir_col = worksheet.col(i)
        fir_col.width = 300 * 20

        # 设置行高
        tall_style = xlwt.easyxf('font:height 320;')  # 36pt,类型小初的字号
        first_row = worksheet.row(i)
        first_row.set_style(tall_style)
        worksheet.write(1, i, result[i], style2)
        if i == 0:
            worksheet.write(2, i, 1, style3)
        else:
            pass
    res = get_os_info()
    #print(list(res)[0])
    worksheet.write(2, 1, list(res)[1], style3)
    worksheet.write(2, 2, list(res)[0], style3)
    res_1 = list(dict_1)[0]
    cpu_int = res_1.get('CPU')
    cpu_int2 = '80%'
    if compare_int(cpu_int) >= compare_int(cpu_int2):
        worksheet.write(2, 3, res_1.get('CPU'), style4)
    else:
        worksheet.write(2, 3, res_1.get('CPU'), style3)
    mem_int = res_1.get('Memory')
    mem_int2 = '80%'
    if compare_int(mem_int) >= compare_int(mem_int2):
        worksheet.write(2, 4, res_1.get('Memory'), style4)
    else:
        worksheet.write(2, 4, res_1.get('Memory'), style3)
    boot = res_1.get('/boot')
    boot_int2 = '80%'
    if compare_int(boot) >= compare_int(boot_int2):
        worksheet.write(2, 5, res_1.get('/boot'), style4)
    else:
        worksheet.write(2, 5, res_1.get('/boot'), style3)
    mnt = res_1.get('/mnt')
    mnt_int2 = '80%'
    if compare_int(mnt) >= compare_int(mnt_int2):
        worksheet.write(2, 6, res_1.get('/mnt'), style4)
    else:
        worksheet.write(2, 6, res_1.get('/mnt'), style3)

    process = int(get_process())
    if process > 2:
        worksheet.write(2, 7, '存活', style3)
    else:
        worksheet.write(2, 7, '无进程', style4)
    workbook.save('formatting.xls') # 保存文件