官网地址：https://openpyxl.readthedocs.io/en/stable/charts/line.html#id1
openpyxl+pandas
# coding=utf-8
import pandas as pd
import time
from openpyxl import Workbook
from openpyxl.chart import (
LineChart,
Reference,
)
from openpyxl.chart.axis import DateAxis
def cpu_info(csv_path="./datas-permon/CPU_20200111005156.csv"):
df = pd.read_csv(csv_path) #dtype={'timeStamp':str}
df['timeStamp'] = df['timeStamp'].apply(lambda x:time.strftime('%Y%m%d%H%M%S'
,time.localtime(int(str(x)[:10]))))
user=df.loc[df.label=="192.168.110.151 CPU user"][["label","timeStamp","elapsed"]]
iowait=df.loc[df.label=="192.168.110.151 CPU iowait"][["label","timeStamp","elapsed"]]
idle=df.loc[df.label=="192.168.110.151 CPU idle"][["label","timeStamp","elapsed"]]
it=df["timeStamp"].drop_duplicates().values.tolist()
t=[ str(i) for i in it]
u=user["elapsed"].values.tolist()
io=iowait["elapsed"].values.tolist()
idl=idle["elapsed"].values.tolist()
rows=list(zip(t,u,io,idl))
rows.insert(0,["timeStamp","user","iowait","idle"])
return rows
def mem_info(csv_path="./datas-permon/Meminfo_20200111005156.csv"):
df=pd.read_csv(csv_path)
df['timeStamp'] = df['timeStamp'].apply(lambda x:time.strftime('%Y%m%d%H%M%S'
,time.localtime(int(str(x)[:10]))))
total=df.loc[df.label=="192.168.110.151 Memory total"][["label","timeStamp","elapsed"]]
used=df.loc[df.label=="192.168.110.151 Memory used"][["label","timeStamp","elapsed"]]
free=df.loc[df.label=="192.168.110.151 Memory free"][["label","timeStamp","elapsed"]]
tol=total["elapsed"]/1024/1024/1024
us=used["elapsed"]/1024/1024/1024
fr=free["elapsed"]/1024/1024/1024
it = df["timeStamp"].drop_duplicates().values.tolist()
t = [str(i) for i in it]
to=tol.values.tolist()
f=fr.values.tolist()
u=us.values.tolist()
rows=list(zip(t,to,u,f))
rows.insert(0,["timestamp","total","used","free"])
return rows
def network_info(csv_path="./datas-permon/NetIO_20200111005156.csv"):
df=pd.read_csv(csv_path)
df['timeStamp'] = df['timeStamp'].apply(lambda x:time.strftime('%Y%m%d%H%M%S'
,time.localtime(int(str(x)[:10]))))
send=df.loc[df.label=="192.168.110.151 Network I/O bytessent"][["label","timeStamp","elapsed"]]
recv=df.loc[df.label=="192.168.110.151 Network I/O bytesrecv"][["label","timeStamp","elapsed"]]
elapsed_send=send["elapsed"].values.tolist()
elapsed_recv=recv["elapsed"].values.tolist()
it = df["timeStamp"].drop_duplicates().values.tolist()
t = [str(i) for i in it]
rows=list(zip(t,elapsed_send,elapsed_recv))
rows.insert(0,["timeStamp","sentBytes","recvBytes"])
return rows
def performance_util(configurations):
wb = Workbook()
for configuration in configurations:
rows = configuration["rows"]
sheet = configuration["sheet_name"]
label_max=configuration["label"]
ws = wb.create_sheet(sheet, index=configuration["index"])
for row in rows:
ws.append(row)
data = Reference(ws, min_col=2, min_row=1, max_col=label_max, max_row=len(rows)-1) # max_row=7
# Chart with date axis
c2 = LineChart()
c2.title = "Date Axis"
c2.style = 7
c2.y_axis.title = "Size"
c2.y_axis.crossAx = 500
c2.x_axis = DateAxis(crossAx=100)
c2.x_axis.number_format = '%Y%m%d%H%M%S'
# c2.x_axis.majorTimeUnit = "days"
c2.x_axis.title = "Date"
c2.height = 16
c2.width = 28
c2.add_data(data, titles_from_data=True)
dates = Reference(ws, min_col=1, min_row=2, max_row=len(rows)-1) # max_row=7
c2.set_categories(dates)
ws.add_chart(c2, "F1")
wb.save("line_permon.xlsx")
if __name__ == '__main__':
cpu_dict={"rows":cpu_info(),"sheet_name":"CPU","index":0,"label":4}
mem_dict={"rows":mem_info(),"sheet_name":"Mem","index":1,"label":4}
network_dict = {"rows": network_info(), "sheet_name": "NetWork", "index": 2,"label":3}
performance_util([cpu_dict,mem_dict,network_dict])
# network_info()
效果图：
mem:
network:
