在數據處理和報告生成的領域中,Excel 文件一直是廣泛使用的標準格式。為了讓 Python 開發者能夠輕鬆創建和修改 Excel 文件,XlsxWriter 庫應運而生。XlsxWriter 是一個功能強大的 Python 模塊,專門用於生成 Microsoft Excel 2007及以上版本(.... ...
在數據處理和報告生成的領域中,Excel 文件一直是廣泛使用的標準格式。為了讓 Python 開發者能夠輕鬆創建和修改 Excel 文件,XlsxWriter 庫應運而生。XlsxWriter 是一個功能強大的 Python 模塊,專門用於生成 Microsoft Excel 2007及以上版本(.xlsx 格式)的電子錶格文件。本文將對XlsxWriter進行概述,探討其主要特點、用法和一些實際應用,並實現繪製各類圖例(條形圖,柱狀圖,餅狀圖)等。
主要特點
- .xlsx 格式支持: XlsxWriter 專註於創建 Microsoft Excel 2007 及以上版本的文件,這是一種基於 XML 的格式,允許存儲大量數據、樣式和圖表。
- 格式和樣式: XlsxWriter 允許開發者以編程方式設置單元格的格式和樣式,包括字體、顏色、對齊方式等。這使得生成的 Excel 文件能夠呈現出精美的外觀。
- 圖表和圖形: XlsxWriter 支持創建各種類型的圖表,如折線圖、柱狀圖、餅圖等,使用戶能夠直觀地呈現數據。同時,它還支持插入圖片、形狀和註釋等圖形元素。
- 公式和函數: XlsxWriter 允許在單元格中使用 Excel 公式和函數,這對於進行複雜的計算和數據分析非常有用。
- 大數據量處理: XlsxWriter 被設計為高性能的庫,能夠處理大規模的數據集,同時保持生成的 Excel 文件的高質量。
- 圖表和條件格式: 除了基本的單元格樣式,XlsxWriter 支持添加條件格式,以及在工作表中插入圖表,提供更直觀的數據可視化。
安裝模塊
要開始使用 XlsxWriter,首先需要安裝該庫。可以通過以下命令使用 pip 安裝:
pip install XlsxWriter
XlsxWriter 提供了一個強大而靈活的工具,使得使用 Python 生成 Excel 文件變得簡單而高效。無論是用於數據分析、報告生成還是其他領域,XlsxWriter 都為開發者提供了一種簡單而可靠的方法,使他們能夠充分利用 Excel 的強大功能。在掌握了基本用法後,開發者可以深入研究 XlsxWriter 的高級特性,以滿足更複雜的需求。
單行輸出函數
函數WriteSingleArticle()
調用時傳入文檔名稱,以及傳入表頭和數據,寫出簡單的單行記錄。
import xlsxwriter
# 寫出數據
def WriteSingleArticle(xls_name,header,data):
workbook = xlsxwriter.Workbook(xls_name)
worksheet = workbook.add_worksheet()
# 定義表格樣式
head_style = workbook.add_format({"bold": True, "align": "center", "border": 1, "fg_color": "#D7E4BC"})
worksheet.set_column("A1:D1", 15)
# 寫出表頭
worksheet.write_row("A1", header, head_style)
for index in range(0, len(data)):
worksheet.write_row("A{}".format(index + 2), data[index])
workbook.close()
return True
if __name__ == "__main__":
headings = ["用戶名", "密碼", "地址"]
data = [["admin","123456","192.168.1.1"],["admin","123456","192.168.1.1"]]
ref = WriteSingleArticle("lyshark.xlsx",headings,data)
print("寫出狀態: {}".format(ref))
輸出效果如下所示;
多行表格輸出函數
函數CreateTable(address,data,section)
實現了輸出一個列表格式的Table,只需傳入列表序列即可。
先找到表格生成坐標與大小之間的比值關係,這是第一步,如下是簡單的實現固定位置生成表格。
import xlsxwriter
# 設置表格sheet名稱
workbook = xlsxwriter.Workbook('lyshark.xlsx')
worksheet = workbook.add_worksheet("系統磁碟統計")
# 設置頭部標題IP地址列
merge_format = workbook.add_format({'bold': True,'border': 1,'align': 'center','valign': 'vcenter','fg_color': '#EEAEEE'})
worksheet.merge_range('A9:B12', '192.168.1.1', merge_format)
# 設置表格頭部提示,並將前兩個表頭合併為1個
header = ["IP地址","IP地址","路徑","總容量","剩餘容量","利用率"]
merge_format1 = workbook.add_format({'bold': True,'border': 1,'align': 'center','valign': 'vcenter','fg_color': '#AEEEEE'})
worksheet.write_row("A8:B12",header,merge_format1) # 顯示表頭
worksheet.merge_range('A8:B8',"IP地址",merge_format1) # 合併表頭(合併第一個元素)
# 寫出路徑列表
data1 = ["/etc/system/","/proc/","/sys","/var/lyshark"]
merge_format2 = workbook.add_format({'bold': True,'border': 1,'valign': 'vcenter','fg_color': '#D7E4BC','align': 'center'})
worksheet.write_column("C9",data1,merge_format2)
worksheet.set_column("C9:C9",30)
# 寫出總容量
data2 = ["1024 GB","2048 GB","111 GB","1111 GB"]
merge_format3 = workbook.add_format({'bold': True,'border': 1,'valign': 'vcenter','fg_color': '#D7E4BC','align': 'center'})
worksheet.write_column("D9",data2,merge_format3)
worksheet.set_column("D9:D9",20)
# 寫出剩餘容量
data3 = ["1024 GB","2048 GB","111 GB","22 GB"]
merge_format4 = workbook.add_format({'bold': True,'border': 1,'valign': 'vcenter','fg_color': '#D7E4BC','align': 'center'})
worksheet.write_column("E9",data3,merge_format4)
worksheet.set_column("E9:E9",20)
# 寫出利用率
data4= ["10%","50%","20%","33%"]
merge_format5 = workbook.add_format({'bold': True,'border': 1,'valign': 'vcenter','fg_color': '#D7E4BC','align': 'center'})
worksheet.write_column("F9",data4,merge_format5)
worksheet.set_column("F9:F9",20)
workbook.close()
輸出效果如下所示;
繼續封裝如上代碼,將其封裝為CreateTable(address,data,section)
函數,用戶傳入表頭地址,數據集,以及從第幾行開始寫數據,則自動生成表單。
import xlsxwriter
workbook = xlsxwriter.Workbook('lyshark.xlsx')
worksheet = workbook.add_worksheet("統計表")
# 創建表結構
def CreateTable(address,data,section):
# --------------------------------------------------------------------
# 計算表頭列表長度
header_count = len(data[1])
print("不帶表頭的列表長度: {}".format(header_count))
merge_format1 = workbook.add_format({'bold': True, 'border': 1, 'align': 'center', 'valign': 'vcenter', 'fg_color': '#AEEEEE'})
# 根據表格列長度 計算出表格大小
header_range = f"A{section}:B{section+header_count}"
print("表頭總長度 header_range = {}".format(header_range))
# 寫出表頭到文件
worksheet.write_row(header_range, data[0], merge_format1)
# --------------------------------------------------------------------
# 計算合併表頭偏移,併合並
header_merge_range = f"A{section}:B{section}"
print("合併表頭偏移 header_merge_range = {}".format(header_merge_range))
# 合併表頭(合併第一個元素)
header_table = data[0][0]
worksheet.merge_range(header_merge_range, header_table, merge_format1)
# worksheet.merge_range(header_merge_range, "IP地址", merge_format1)
# --------------------------------------------------------------------
# 計算出表頭 所占總單元格大小
remove_header_count = len(data) - 1
print("除去表頭的列表長度: {}".format(remove_header_count))
# 此處自己調整列長度
address_merge_range = f"A{section+1}:B{section + len(data[0][1])}"
print("所占總單元格大小 address_merge_range = {} => {}".format(len(data[0][1]),address_merge_range))
merge_format = workbook.add_format({'bold': True, 'border': 1, 'align': 'center', 'valign': 'vcenter', 'fg_color': '#EEAEEE'})
# 寫出單元格合併大小
worksheet.merge_range(address_merge_range, address , merge_format)
# --------------------------------------------------------------------
# 迴圈填充數據
merge_format_index = workbook.add_format(
{'bold': True, 'border': 1, 'valign': 'vcenter', 'fg_color': '#D7E4BC', 'align': 'center'})
letter_list = ['C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O',
'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z']
# 迴圈填充數據 最大欄位長度為24
for index in range(0, remove_header_count):
index_range = f"{letter_list[index]}{section+1}"
worksheet.write_column(index_range, data[index+1], merge_format_index)
index_range = f"{letter_list[index]}{section+1}:{letter_list[index]}{section+1}"
worksheet.set_column(index_range, 30)
"""
# 不使用迴圈逐條填充
merge_format2 = workbook.add_format( {'bold': True, 'border': 1, 'valign': 'vcenter', 'fg_color': '#D7E4BC', 'align': 'center'})
index_range = "C{}".format(section+1)
worksheet.write_column(index_range, data[1], merge_format2)
index_range = "C{}:C{}".format(section+1,section+1)
worksheet.set_column(index_range, 30)
index_range = "D{}".format(section+1)
worksheet.write_column(index_range, data[2], merge_format2)
index_range = "D{}:D{}".format(section+1,section+1)
worksheet.set_column(index_range, 30)
index_range = "E{}".format(section+1)
worksheet.write_column(index_range, data[2], merge_format2)
index_range = "E{}:E{}".format(section+1,section+1)
worksheet.set_column(index_range, 30)
"""
# 返回計算後的表格的下兩個單元的實際偏移位置
return section + remove_header_count + 3
# 測試恆矩陣
def Test():
val = \
[
["測試地址", "測試地址","磁碟路徑", "總容量", "剩餘容量"],
["/etc/system/", "/proc/", "/sys", "/user"],
["1024 GB", "2048 GB", "12 GB","98 GB"],
["1345 GB", "1124 GB", "341 GB", "55 GB"]
]
ref = CreateTable("192.168.1.1",val,1)
print("返回下一個表格索引: {}".format(ref))
ref = CreateTable("192.168.1.1",val,ref)
print("返回下一個表格索引: {}".format(ref))
workbook.close()
# 測試豎矩陣
def Test2():
header = ["測試地址", "測試地址","磁碟路徑", "總容量", "剩餘容量"]
val = \
[
["/etc/system/", "1024 GB", "256 GB"],
["/etc/passwd/", "104 GB", "345GB"],
["/etc/username/", "12 GB", "56 GB"],
["/etc/lyshark/", "12 GB", "56 GB"]
]
# 橫向矩陣轉豎向矩陣
ref_xor = list ( map(list,zip(*val)) )
# 追加頭部
ref_xor.insert(0, header)
print(ref_xor)
ref = CreateTable("192.168.1.1",ref_xor,1)
print("返回下一個表格索引: {}".format(ref))
workbook.close()
if __name__ == "__main__":
Test2()
輸出效果如下所示;
柱狀圖輸出函數
簡單實現CreateChart(headings,data)
柱狀圖生成函數,通過傳入頭部標題和數據集列表即可完成表單生成。
import xlsxwriter
workbook = xlsxwriter.Workbook('lyshark.xlsx')
worksheet = workbook.add_worksheet("統計表")
def CreateChart(headings,data):
# 定義表格樣式
head_style = workbook.add_format({"bold": True, "align": "center", "font": 13})
# 逐條寫入數據
worksheet.write_row("A1", headings, head_style)
for i in range(0, len(data)):
worksheet.write_row("A{}".format(i + 2), data[i])
# 添加柱狀圖
chart = workbook.add_chart({"type": "column"})
chart.add_series({
"name": "=統計表!$B$1", # 圖例項
"categories": "=統計表!$A$2:$A$10", # X軸Item名稱
"values": "=統計表!$B$2:$B$10" # X軸Item值
})
chart.add_series({
"name": "=統計表!$C$1",
"categories": "=統計表!$A$2:$A$10",
"values": "=統計表!$C$2:$C$10"
})
chart.add_series({
"name": "=統計表!$D$1",
"categories": "=統計表!$A$2:$A$10",
"values": "=統計表!$D$2:$D$10"
})
# 添加柱狀圖標題
chart.set_title({"name": "性能統計柱狀圖"})
chart.set_style(12)
# 在G2處繪製
worksheet.insert_chart("G2", chart)
workbook.close()
if __name__ == "__main__":
headings = ["主機地址", "CPU利用率", "記憶體利用率", "交換分區"]
data = [["192.168.1.100", 88, 36, 66], ["192.168.1.200", 98, 89, 66], ["192.168.1.220", 88, 100, 32]]
# 迴圈添加模擬數據
for i in range(1, 100):
s = ["192.168.1.{}".format(i), i, i, i]
data.append(s)
CreateChart(headings,data)
輸出效果如下所示;
條形圖輸出函數
封裝CreateChart(headings,data)
函數實現輸出條形圖,並將前十的數據繪成圖展示在右側。
import xlsxwriter
workbook = xlsxwriter.Workbook('lyshark.xlsx')
worksheet = workbook.add_worksheet("統計表")
def CreateChart(headings,data):
# 定義表格樣式
head_style = workbook.add_format({"bold": True, "align": "center", "fg_color": "#D7E4BC"})
worksheet.set_column("A1:D1", 15)
# 逐條寫入數據
worksheet.write_row("A1", headings, head_style)
for i in range(0, len(data)):
worksheet.write_row("A{}".format(i + 2), data[i])
# 添加條形圖,顯示前十個元素
chart = workbook.add_chart({"type": "line"})
chart.add_series({
"name": "=統計表!$B$1", # 圖例項
"categories": "=統計表!$A$2:$A$10", # X軸Item名稱
"values": "=統計表!$B$2:$B$10" # X軸Item值
})
chart.add_series({
"name": "=統計表!$C$1",
"categories": "=統計表!$A$2:$A$10",
"values": "=統計表!$C$2:$C$10"
})
chart.add_series({
"name": "=統計表!$D$1",
"categories": "=統計表!$A$2:$A$10",
"values": "=統計表!$D$2:$D$10"
})
# 添加柱狀圖標題
chart.set_title({"name": "負載統計條形圖"})
# chart.set_style(8)
chart.set_size({'width': 1000, 'height': 500})
chart.set_legend({'position': 'top'})
# 在F2處繪製
worksheet.insert_chart("F2", chart)
workbook.close()
if __name__ == "__main__":
headings = ["獲取時間", "1分鐘負載", "5分鐘負載", "15分鐘負載"]
data = [["12:01", 0.05, 0.7, 0.006], ["12:02", 0.5, 0.08, 0.06], ["12:03", 0.7, 1, 2.1]]
CreateChart(headings,data)
輸出效果如下所示;
餅狀圖輸出函數
函數CreateChart(headings,data)
用於生成餅狀圖,實現對主機以及主機數量的圖形化展示。
import xlsxwriter
workbook = xlsxwriter.Workbook('lyshark.xlsx')
worksheet = workbook.add_worksheet("統計表")
def CreateChart(headings,data):
# 定義表格樣式
head_style = workbook.add_format({"bold": True, "align": "center", "fg_color": "#D7E4BC"})
worksheet.set_column("A1:D1", 15)
# 逐條寫入數據
worksheet.write_row("A1", headings, head_style)
for i in range(0, len(data)):
worksheet.write_row("A{}".format(i + 2), data[i])
# 添加條形圖,顯示前十個元素
chart = workbook.add_chart({"type": "pie"})
chart.add_series({
"name": "=統計表!$B$1", # 圖例項
"categories": "=統計表!$A$2:$A$4", # X軸 Item名稱
"values": "=統計表!$B$2:$B$4" # X軸Item值
})
# 添加餅狀圖
chart.set_title({"name": "系統版本分佈"})
chart.set_size({'width': 600, 'height': 300})
chart.set_legend({'position': 'right'})
# 在D2處繪製
worksheet.insert_chart("D2", chart)
workbook.close()
if __name__ == "__main__":
headings = ["系統版本", "數量"]
data = [["Suse", 30], ["Centos", 25], ["AIX", 15]]
CreateChart(headings,data)
輸出效果如下所示;
實現繪圖類
通過調用xlsxwriter
第三方庫,實現繪製各類通用圖形,並保存為XLS文檔格式.
import xlsxwriter
class DrawChart():
def __init__(self,workbook):
self.workbook = xlsxwriter.Workbook(workbook)
# 排序函數,以第三列為條件排列
def cpu_takeSecond(self,elem):
return int(elem[3])
def mem_taskSecond(self,elem):
return int(elem[1])
# 封裝統計主機磁碟使用情況
def CreateDiskTable(self,worksheet,address,data,section):
# 添加統計名稱 例如: 磁碟統計
worksheet = self.workbook.add_worksheet(worksheet)
merge_format = self.workbook.add_format(
{'bold': True, 'border': 1, 'align': 'center', 'valign': 'vcenter', 'fg_color': '#EEAEEE'})
header_count = len(data[1])
merge_format1 = self.workbook.add_format(
{'bold': True, 'border': 1, 'align': 'center', 'valign': 'vcenter', 'fg_color': '#AEEEEE'})
# 根據磁碟路徑計算出表格大小
header_range = "A{}:B{}".format(section,section+header_count)
worksheet.write_row(header_range, data[0], merge_format1) # 顯示表頭
# 計算合併表頭偏移
header_merge_range = "A{}:B{}".format(section,section)
worksheet.merge_range(header_merge_range, "巡檢IP地址", merge_format1) # 合併表頭(合併第一個元素)
# 計算出地址所占總單元格大小
address_merge_range = "A{}:B{}".format(section+1,section+header_count)
worksheet.merge_range(address_merge_range, address , merge_format) #需要計算出來,根據傳入分區數量
# 通過計算得到磁碟路徑所對應到表中的位置
merge_format2 = self.workbook.add_format(
{'bold': True, 'border': 1, 'valign': 'vcenter', 'fg_color': '#D7E4BC'})
index_range = "C{}".format(section+1)
worksheet.write_column(index_range, data[1], merge_format2)
index_range = "C{}:C{}".format(section+1,section+1)
worksheet.set_column(index_range, 30)
# 計算出總容量對應到表中的位置
merge_format3 = self.workbook.add_format(
{'bold': True, 'border': 1, 'valign': 'vcenter', 'fg_color': '#D7E4BC', 'align': 'center'})
index_range = "D{}".format(section + 1)
worksheet.write_column(index_range, data[2], merge_format3)
index_range = "D{}:D{}".format(section + 1, section + 1)
worksheet.set_column(index_range, 20)
# 計算出剩餘容量對應到表中的位置
merge_format4 = self.workbook.add_format(
{'bold': True, 'border': 1, 'valign': 'vcenter', 'fg_color': '#D7E4BC', 'align': 'center'})
index_range = "E{}".format(section + 1)
worksheet.write_column(index_range, data[3], merge_format4)
index_range = "E{}:E{}".format(section + 1, section + 1)
worksheet.set_column(index_range, 20)
# 計算出利用率對應到表中的位置
merge_format5 = self.workbook.add_format(
{'bold': True, 'border': 1, 'valign': 'vcenter', 'fg_color': '#D7E4BC', 'align': 'center'})
index_range = "F{}".format(section + 1)
worksheet.write_column(index_range, data[4], merge_format5)
index_range = "F{}:F{}".format(section + 1, section + 1)
worksheet.set_column(index_range, 20)
# 返回計算後的表格的下兩個單元的實際偏移位置
return section + header_count + 3
# 創建CPU利用率百分比,並統計前十
def CreateCpuUsedTable(self,worksheet,header,data):
worksheet = self.workbook.add_worksheet(worksheet)
# 設置頭部顏色,並寫入頭部數據
head_style = self.workbook.add_format({"bold": True, "align": "center", "fg_color": "#D7E4BC"})
worksheet.write_row("A1", header, head_style)
# 設置頭部列寬
worksheet.set_column("A1:D1", 15)
# 排序,統計第三列數據,將最大的放在最前面,以此向下
data.sort(key=self.cpu_takeSecond, reverse=True)
# 將數據批量添加到表格中
for x in range(0,len(data)):
worksheet.write_row("A{}".format(x + 2), data[x])
# --------------------------------------------------------------
# 添加柱狀圖(開始繪圖)
chart = self.workbook.add_chart({"type": "column"})
chart.add_series({
"name": "=CPU利用率!$B$1", # 圖例項(也就是CPU內核態)
"categories": "=CPU利用率!$A$2:$A$10", # X軸 Item名稱
"values": "=CPU利用率!$B$2:$B$10" # X軸Item值
})
chart.add_series({
"name": "=CPU利用率!$C$1",
"categories": "=CPU利用率!$A$2:$A$10",
"values": "=CPU利用率!$C$2:$C$10"
})
chart.add_series({
"name": "=CPU利用率!$D$1",
"categories": "=CPU利用率!$A$2:$A$10",
"values": "=CPU利用率!$D$2:$D$10"
})
# 添加柱狀圖標題
chart.set_title({"name": "CPU 性能統計柱狀圖"})
# chart.set_style(8)
chart.set_x_axis({
'major_gridlines': {
'visible': True,
'line': {'width': 1.25, 'dash_type': 'dash'}
},
})
chart.set_size({'width': 900, 'height': 500})
chart.set_legend({'position': 'top'})
chart.set_table({'show_keys': True})
# 在F2處繪製
worksheet.insert_chart("F2", chart)
# 記憶體利用率統計
def CreateMemoryTable(self, worksheet, header, data):
worksheet = self.workbook.add_worksheet(worksheet)
# 設置頭部顏色,並寫入頭部數據
head_style = self.workbook.add_format({"bold": True, "align": "center", "fg_color": "#D7E4BC"})
worksheet.write_row("A1", header, head_style)
# 設置頭部列寬
worksheet.set_column("A1:D1", 15)
# 排序,統計第三列數據,將最大的放在最前面,以此向下
data.sort(key=self.mem_taskSecond, reverse=True)
# 將數據批量添加到表格中
for x in range(0,len(data)):
worksheet.write_row("A{}".format(x + 2), data[x])
# --------------------------------------------------------------
# 添加柱狀圖(橫向圖)
chart = self.workbook.add_chart({"type": "bar"})
chart.add_series({
"name": "=記憶體利用率!$B$1",
"categories": "=記憶體利用率!$A$2:$A$10",
"values": "=記憶體利用率!$B$2:$B$10"
})
chart.add_series({
"name": "=記憶體利用率!$C$1",
"categories": "=記憶體利用率!$A$2:$A$10",
"values": "=記憶體利用率!$C$2:$C$10"
})
# 添加柱狀圖標題
chart.set_title({"name": "記憶體利用率統計圖"})
chart.set_x_axis({
'major_gridlines': {
'visible': True,
'line': {'width': 1.25, 'dash_type': 'dash'}
},
})
chart.set_size({'width': 900, 'height': 400})
chart.set_legend({'position': 'top'})
# 在F2處繪製
worksheet.insert_chart("F2", chart)
# --------------------------------------------------------------
# 統計CPU Load 負載情況 註意: 只能指定單獨的主機
def CreateCpuLoadAvgTable(self, address,worksheet, header, data):
worksheet = self.workbook.add_worksheet(worksheet)
# 設置頭部顏色,並寫入頭部數據
head_style = self.workbook.add_format({"bold": True, "align": "center", "fg_color": "#D7E4BC"})
worksheet.write_row("A1", header, head_style)
# 設置頭部列寬
worksheet.set_column("A1:D1", 15)
# 將數據批量添加到表格中
for x in range(0,len(data)):
worksheet.write_row("A{}".format(x + 2), data[x])
# 定義表格樣式
head_style = self.workbook.add_format({"bold": True, "align": "center", "fg_color": "#D7E4BC"})
worksheet.set_column("A1:D1", 15)
# 逐條寫入數據
worksheet.write_row("A1", header, head_style)
for i in range(0, len(data)):
worksheet.write_row("A{}".format(i + 2), data[i])
# 添加條形圖,顯示前十個元素
chart = self.workbook.add_chart({"type": "line"})
chart.add_series({
"name": "=CPU負載數據統計!$B$1", # 圖例項
"categories": "=CPU負載數據統計!$A$2:$A$10", # X軸 Item名稱
"values": "=CPU負載數據統計!$B$2:$B$10" # X軸Item值
})
chart.add_series({
"name": "=CPU負載數據統計!$C$1", # 第一個線條(圖例)
"categories": "=CPU負載數據統計!$A$2:$A$10",
"values": "=CPU負載數據統計!$C$2:$C$10"
})
chart.add_series({
"name": "=CPU負載數據統計!$D$1", # 第二個線條(圖例)
"categories": "=CPU負載數據統計!$A$2:$A$10",
"values": "=CPU負載數據統計!$D$2:$D$10"
})
# 添加柱狀圖標題
chart.set_title({"name": "統計地址: {}".format(address)})
chart.set_size({'width': 900, 'height': 500})
chart.set_legend({'position': 'top'})
# 在F2處繪製
worksheet.insert_chart("F2", chart)
# 關閉並保存繪製結果
def Save(self):
self.workbook.close()
if __name__ == "__main__":
work = DrawChart("lyshark.xlsx")
# ------------------------------------------------------------------
# 統計系統磁碟容量
disk_val = [
["IP地址", "IP地址", "磁碟路徑", "總容量", "剩餘容量", "利用率"],
["/etc/system/", "/proc/", "/sys", "/abc/lyshark"],
["1024GG", "2048GB", "111GB", "1111GB"],
["1024GG", "2048GB", "111GB", "22GB"],
["10%", "50%", "20%", "33%"]
]
ref = work.CreateDiskTable("磁碟分區統計","127.0.0.1",disk_val,3)
print("下個表格開頭位置: {}".format(ref))
print("[+] 磁碟數據統計完成")
# -------------------------------------------------------------------
# 統計系統CPU負載情況
header = ["主機地址", "CPU內核態", "CPU用戶態", "總利用率"]
cpu_val = [
["192.168.1.100", 88, 36, 100],
["192.168.1.200", 98, 89, 128],
["192.168.1.220", 88, 100, 190]
]
ref = work.CreateCpuUsedTable("CPU利用率",header,cpu_val)
print("[+] CPU利用率統計已完成")
# -------------------------------------------------------------------
# 統計系統記憶體利用率數據
header = ["主機地址", "通用記憶體利用率", "交換記憶體利用率"]
mem_val = [
["192.168.1.100", 25, 35],
["192.168.1.200", 44, 57],
["192.168.1.200", 24, 21],
["192.168.1.200", 78, 89]
]
ref = work.CreateMemoryTable("記憶體利用率",header,mem_val)
print("[+] 記憶體利用率統計已完成")
# -------------------------------------------------------------------
# 獲取CPU LoadAvg負載情況
header = ["拉取日期","1分鐘負載","5分鐘負載","15分鐘負載"]
cpu_avg_val = [
["12:11",0.1,0.2,1.3],
["12:12",1.4,3.3,6.9],
["12:13",2.6,3.2,6.9]
]
ref = work.CreateCpuLoadAvgTable("127.0.0.1","CPU負載數據統計",header,cpu_avg_val)
print("[+] CPU負載統計完成")
work.Save()
輸出效果如下所示;
文章出處:https://www.cnblogs.com/LyShark/p/17851542.html本博客所有文章除特別聲明外,均採用 BY-NC-SA 許可協議。轉載請註明出處!