cpu、fs、io表格分析转化并生成图表

光大表格转换

CPU表格转化

from openpyxl import *
import openpyxl
from openpyxl.chart import(
    Reference,
    AreaChart,
)

wb = openpyxl.load_workbook('cpu.xlsx')
ws = wb.active
range = ws.iter_rows(min_row=2)
dict1 = {}
for row in range:
    each_row = [cell.value for cell in row]
#    print(each_row)
    if each_row[1] in dict1.keys():
        dict1[each_row[1]] += [each_row]
    else:
        dict1[each_row[1]] = [each_row]
# print(dict1)
# print(dict1['10.191.4.31'][1])
# print(dict1['10.191.4.31'][2])
# print(dict1['10.191.4.32'][1])
for key, value in sorted(dict1.items()):
    nws = wb.create_sheet(key)
    nws.append(['time', 'hostname', 'max_cpu_precent', 'agv_cpu_precent'])
    for data in value:
        nws.append(data)

for key, value in sorted(dict1.items()):
    ws = wb.get_sheet_by_name(key)

    chart = AreaChart()
    chart.title = "CPU"
    chart.style = 13
    chart.x_axis.title = 'Time'
    chart.y_axis.title = 'Cpu precent'

    cats = Reference(ws, min_col=1, min_row=2, max_row=31)
    data = Reference(ws, min_col=3, min_row=1, max_col=4, max_row=31)
    chart.add_data(data, titles_from_data=True)
    chart.set_categories(cats)

    ws.add_chart(chart, "G3")
    # print(ws)
wb.remove(wb['导出工作表'])
wb.remove(wb['SQL'])
wb.save('transition_cpu.xlsx')
print('complete transition')

memory表格转化

from openpyxl import *
import openpyxl
from openpyxl.chart import(
    Reference,
    AreaChart,
)

wb = openpyxl.load_workbook('mem.xlsx')
ws = wb.active
range = ws.iter_rows(min_row=2)
dict1 = {}
for row in range:
    each_row = [cell.value for cell in row]
#    print(each_row)
    if each_row[1] in dict1.keys():
        dict1[each_row[1]] += [each_row]
    else:
        dict1[each_row[1]] = [each_row]
# print(dict1)
# print(dict1['10.191.4.31'][1])
# print(dict1['10.191.4.31'][2])
# print(dict1['10.191.4.32'][1])
for key, value in sorted(dict1.items()):
    nws = wb.create_sheet(key)
    nws.append(['Time', 'Host_Name', '已用内存量(百分比)'])
    for data in value:
        nws.append(data)

for key, value in sorted(dict1.items()):
    ws = wb.get_sheet_by_name(key)

    chart = AreaChart()
    chart.title = "Memory"
    chart.style = 13
    chart.x_axis.title = 'Time'
    chart.y_axis.title = 'Mem precent'

    cats = Reference(ws, min_col=1, min_row=2, max_row=ws.max_row)
    data = Reference(ws, min_col=3, min_row=1, max_col=3, max_row=ws.max_row)
    chart.add_data(data, titles_from_data=True)
    chart.set_categories(cats)

    ws.add_chart(chart, "G3")
    # print(ws)
wb.remove(wb['导出工作表'])
wb.remove(wb['SQL'])
wb.save('transition_memory.xlsx')
print('complete transition')

文件系统使用率表格转化

import openpyxl
from openpyxl.chart import(
    Reference,
    LineChart
)

wb = openpyxl.load_workbook('disk.xlsx')
ws = wb.active
range = ws.iter_rows(min_row=2)
dict1 = {}
for row in range:
    each_row = [cell.value for cell in row]
#    print(each_row)
    if each_row[1] in dict1.keys():
        dict1[each_row[1]] += [each_row]
    else:
        dict1[each_row[1]] = [each_row]
# print(dict1)
# print(dict1['10.191.4.31'][1])
# print(dict1['10.191.4.31'][2])
# print(dict1['10.191.4.32'][1])
for key, value in sorted(dict1.items()):
    nws = wb.create_sheet(key)
    nws.append(['Time', 'Host_Name', 'LAT_Mount_Point', 'AVG_Space_Available_Percent'])
    # print(key, value)
    for data in value:
        nws.append(data)
# 一维表转化成二维
#wb.save('transition_disk.xlsx')
for key, value in sorted(dict1.items()):
    ws = wb[key]
    nws = wb.create_sheet(key+'-')
    rngs = list(ws.values)[1:]
    mm = list({m.value: '' for m in ws['a'][1:]})
    name = list({m.value: '' for m in ws['c'][1:]})
    nws.append(['Time \ Mount_Point'] + name)
    for n in mm:
        l = [(n, m) for m in name]
        # print(l)
        # print([t for t in l])
        # nws.append([n]+[list(filter(lambda r: t[1]==r[2],rngs))[0][3] for t in l])
        nws.append([n] + [list(filter(lambda r: t[1] == r[2] and t[0] == r[0], rngs))[0][3] for t in l])
        #         # nws.append([n]+[list(filter(lambda r: t[0]==r[2],rngs))[0][3] for t in l])
        # try:
        #     # nws.append([n] + [list(filter(lambda r: t[0] == r[0] and t[1] == r[1], rngs))[0][3] for t in l])
        #     print([n] + [list(filter(lambda r: t[0] == r[0] and t[1] == r[1], rngs))[0][3] for t in l])
        # except:
        #     pass
for key, value in sorted(dict1.items()):
    wb.remove(wb[key])
wb.remove(wb['导出工作表'])
wb.remove(wb['SQL'])
wb.save('transition_disk.xlsx')



####生产图表
for key, value in sorted(dict1.items()):
    ws = wb[key+'-']
    # ws=wb.get_sheet_by_name(key)
    # 创建图表
    c1 = LineChart()
    c1.title = "FS USE PRECENT"  # 标题
    c1.style = 13  # 样式
    c1.y_axis.title = 'FS Precent'  # Y轴
    c1.x_axis.title = 'TIME'  # X轴

    # 选择数据范围
    data = Reference(ws, min_col=2, min_row=1, max_col=21, max_row=31)
    c1.add_data(data, titles_from_data=True)

    # 线条样式
    s0 = c1.series[0]
    s0.marker.symbol = "triangle"  # triangle为三角形标记， 可选circle、dash、diamond、dot、picture、plus、square、star、triangle、x、auto
    s0.marker.graphicalProperties.solidFill = "FF0000"  # 填充颜色
    s0.marker.graphicalProperties.line.solidFill = "0000FF"  # 边框颜色
    # s0.graphicalProperties.line.noFill = True  # 改为True则隐藏线条，但显示标记形状

    s1 = c1.series[1]
    s1.graphicalProperties.line.solidFill = "00AAAA"
    s1.graphicalProperties.line.dashStyle = "sysDot"  # 线条点状样式
    s1.graphicalProperties.line.width = 80000  # 线条大小，最大20116800EMUs

    s2 = c1.series[2]  # 采用默认设置
    s2.smooth = True  # 线条平滑

    ws.add_chart(c1, "G2")  # 图表位置
    # chart = AreaChart()
    # chart.title = "Area Chart"
    # chart.style = 13
    # chart.x_axis.title = 'Test'
    # chart.y_axis.title = 'Percentage'
    #
    # cats = Reference(ws, min_col=1, min_row=1, max_row=30)
    # data = Reference(ws, min_col=2, min_row=1, max_col=11, max_row=30)
    # chart.add_data(data, titles_from_data=True)
    # chart.set_categories(cats)
    #
    # ws.add_chart(chart, "A10")

#
wb.save('transition_disk.xlsx')
# print('complete transition')

磁盘IO使用率表格转化

import openpyxl
from openpyxl.chart import(
    Reference,
    LineChart
)

wb = openpyxl.load_workbook('diskio.xlsx')
ws = wb.active
range = ws.iter_rows(min_row=2)
dict1 = {}
for row in range:
    each_row = [cell.value for cell in row]
    if each_row[1] in dict1.keys():
        dict1[each_row[1]] += [each_row]
    else:
        dict1[each_row[1]] = [each_row]

for key, value in sorted(dict1.items()):
    nws = wb.create_sheet(key)
    nws.append(['Time', 'Host_Name', 'LAT_Mount_Point', 'MIN_Busy_Percent', 'MAX_Transferred_Bytes', 'MAX_Busy_Percent', 'SUM_Busy_Percent', 'AVG_Busy_Percent'])
    for data in value:
        nws.append(data)
# 一维表转化成二维
#wb.save('transition_disk.xlsx')
for key, value in sorted(dict1.items()):
    ws = wb[key]
    nws = wb.create_sheet(key+'-')
    rngs = list(ws.values)[1:]
    mm = list({m.value: '' for m in ws['a'][1:]})
    name = list({m.value: '' for m in ws['c'][1:]})
    nws.append(['Time \ Mount_Point'] + name)
    for n in mm:
        l = [(n, m) for m in name]

        # nws.append([n]+[list(filter(lambda r: t[1]==r[2],rngs))[0][7] for t in l])
        nws.append([n] + [list(filter(lambda r: t[1] == r[2] and t[0] == r[0], rngs))[0][7] for t in l])

for key, value in sorted(dict1.items()):
    wb.remove(wb[key])
wb.remove(wb['导出工作表'])
wb.remove(wb['SQL'])
#wb.save('transition_diskio.xlsx')



####生产图表
for key, value in sorted(dict1.items()):
    # wb = openpyxl.load_workbook('transition_diskio.xlsx')
    ws = wb[key+'-']
    # 创建图表
    c1 = LineChart()
    c1.title = "DISO IO"  # 标题
    c1.style = 13  # 样式
    c1.y_axis.title = 'IO'  # Y轴

    # 选择数据范围
    # dict2 = {
    #     'key':
    # }
    # print(dict1)
    data = Reference(ws, min_col=2, min_row=1, max_col=11, max_row=30)
    c1.add_data(data, titles_from_data=True)

    # 线条样式
    s0 = c1.series[0]
    s0.marker.symbol = "triangle"  # triangle为三角形标记， 可选circle、dash、diamond、dot、picture、plus、square、star、triangle、x、auto
    s0.marker.graphicalProperties.solidFill = "FF0000"  # 填充颜色
    s0.marker.graphicalProperties.line.solidFill = "0000FF"  # 边框颜色
    # s0.graphicalProperties.line.noFill = True  # 改为True则隐藏线条，但显示标记形状

    s1 = c1.series[1]
    s1.graphicalProperties.line.solidFill = "00AAAA"
    s1.graphicalProperties.line.dashStyle = "sysDot"  # 线条点状样式
    s1.graphicalProperties.line.width = 80000  # 线条大小，最大20116800EMUs

    s2 = c1.series[2]  # 采用默认设置
    s2.smooth = True  # 线条平滑

    ws.add_chart(c1, "G2")  # 图表位置


wb.save('transition_diskio.xlsx')