Python使用XlsxWriter庫(kù)操作Excel詳解

更新時(shí)間：2023年11月23日 15:50:03 作者：微軟技術(shù)分享

XlsxWriter 是一個(gè)功能強(qiáng)大的 Python 模塊,專門用于生成 Microsoft Excel 2007及以上版本的電子表格文件,本文主要為大家介紹了如何使用XlsxWriter庫(kù)進(jìn)行Excel基本操作,需要的可以參考下

在數(shù)據(jù)處理和報(bào)告生成的領(lǐng)域中，Excel 文件一直是廣泛使用的標(biāo)準(zhǔn)格式。為了讓 Python 開(kāi)發(fā)者能夠輕松創(chuàng)建和修改 Excel 文件，XlsxWriter 庫(kù)應(yīng)運(yùn)而生。XlsxWriter 是一個(gè)功能強(qiáng)大的 Python 模塊，專門用于生成 Microsoft Excel 2007及以上版本（.xlsx 格式）的電子表格文件。本文將對(duì)XlsxWriter進(jìn)行概述，探討其主要特點(diǎn)、用法和一些實(shí)際應(yīng)用，并實(shí)現(xiàn)繪制各類圖例（條形圖，柱狀圖，餅狀圖）等。

主要特點(diǎn)

.xlsx 格式支持： XlsxWriter 專注于創(chuàng)建 Microsoft Excel 2007 及以上版本的文件，這是一種基于 XML 的格式，允許存儲(chǔ)大量數(shù)據(jù)、樣式和圖表。
格式和樣式： XlsxWriter 允許開(kāi)發(fā)者以編程方式設(shè)置單元格的格式和樣式，包括字體、顏色、對(duì)齊方式等。這使得生成的 Excel 文件能夠呈現(xiàn)出精美的外觀。
圖表和圖形： XlsxWriter 支持創(chuàng)建各種類型的圖表，如折線圖、柱狀圖、餅圖等，使用戶能夠直觀地呈現(xiàn)數(shù)據(jù)。同時(shí)，它還支持插入圖片、形狀和注釋等圖形元素。
公式和函數(shù)： XlsxWriter 允許在單元格中使用 Excel 公式和函數(shù)，這對(duì)于進(jìn)行復(fù)雜的計(jì)算和數(shù)據(jù)分析非常有用。
大數(shù)據(jù)量處理： XlsxWriter 被設(shè)計(jì)為高性能的庫(kù)，能夠處理大規(guī)模的數(shù)據(jù)集，同時(shí)保持生成的 Excel 文件的高質(zhì)量。
圖表和條件格式： 除了基本的單元格樣式，XlsxWriter 支持添加條件格式，以及在工作表中插入圖表，提供更直觀的數(shù)據(jù)可視化。

安裝模塊

要開(kāi)始使用 XlsxWriter，首先需要安裝該庫(kù)。可以通過(guò)以下命令使用 pip 安裝：

pip install XlsxWriter

XlsxWriter 提供了一個(gè)強(qiáng)大而靈活的工具，使得使用 Python 生成 Excel 文件變得簡(jiǎn)單而高效。無(wú)論是用于數(shù)據(jù)分析、報(bào)告生成還是其他領(lǐng)域，XlsxWriter 都為開(kāi)發(fā)者提供了一種簡(jiǎn)單而可靠的方法，使他們能夠充分利用 Excel 的強(qiáng)大功能。在掌握了基本用法后，開(kāi)發(fā)者可以深入研究 XlsxWriter 的高級(jí)特性，以滿足更復(fù)雜的需求。

單行輸出函數(shù)

函數(shù)WriteSingleArticle()調(diào)用時(shí)傳入文檔名稱，以及傳入表頭和數(shù)據(jù)，寫出簡(jiǎn)單的單行記錄。

import xlsxwriter

# 寫出數(shù)據(jù)
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("寫出狀態(tài): {}".format(ref))

輸出效果如下所示；

多行表格輸出函數(shù)

函數(shù)CreateTable(address,data,section)實(shí)現(xiàn)了輸出一個(gè)列表格式的Table，只需傳入列表序列即可。

先找到表格生成坐標(biāo)與大小之間的比值關(guān)系，這是第一步，如下是簡(jiǎn)單的實(shí)現(xiàn)固定位置生成表格。

import xlsxwriter

# 設(shè)置表格sheet名稱
workbook = xlsxwriter.Workbook('lyshark.xlsx')
worksheet = workbook.add_worksheet("系統(tǒng)磁盤統(tǒng)計(jì)")

# 設(shè)置頭部標(biāo)題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)

# 設(shè)置表格頭部提示,并將前兩個(gè)表頭合并為1個(gè)
header = ["IP地址","IP地址","路徑","總?cè)萘?,"剩余容量","利用率"]
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)      # 合并表頭(合并第一個(gè)元素)

# 寫出路徑列表
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)

# 寫出總?cè)萘?
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()

輸出效果如下所示；

繼續(xù)封裝如上代碼，將其封裝為CreateTable(address,data,section)函數(shù)，用戶傳入表頭地址，數(shù)據(jù)集，以及從第幾行開(kāi)始寫數(shù)據(jù)，則自動(dòng)生成表單。

import xlsxwriter

workbook = xlsxwriter.Workbook('lyshark.xlsx')
worksheet = workbook.add_worksheet("統(tǒng)計(jì)表")

# 創(chuàng)建表結(jié)構(gòu)
def CreateTable(address,data,section):
    # --------------------------------------------------------------------
    # 計(jì)算表頭列表長(zhǎng)度
    header_count = len(data[1])
    print("不帶表頭的列表長(zhǎng)度: {}".format(header_count))

    merge_format1 = workbook.add_format({'bold': True, 'border': 1, 'align': 'center', 'valign': 'vcenter', 'fg_color': '#AEEEEE'})
    # 根據(jù)表格列長(zhǎng)度 計(jì)算出表格大小
    header_range = f"A{section}:B{section+header_count}"
    print("表頭總長(zhǎng)度 header_range = {}".format(header_range))
    # 寫出表頭到文件
    worksheet.write_row(header_range, data[0], merge_format1)

    # --------------------------------------------------------------------
    # 計(jì)算合并表頭偏移,并合并
    header_merge_range = f"A{section}:B{section}"
    print("合并表頭偏移 header_merge_range = {}".format(header_merge_range))

    # 合并表頭(合并第一個(gè)元素)
    header_table = data[0][0]
    worksheet.merge_range(header_merge_range, header_table, merge_format1)
    # worksheet.merge_range(header_merge_range, "IP地址", merge_format1)

    # --------------------------------------------------------------------
    # 計(jì)算出表頭 所占總單元格大小
    remove_header_count = len(data) - 1
    print("除去表頭的列表長(zhǎng)度: {}".format(remove_header_count))
    # 此處自己調(diào)整列長(zhǎng)度
    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)

    # --------------------------------------------------------------------
    # 循環(huán)填充數(shù)據(jù)
    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']

    # 循環(huán)填充數(shù)據(jù) 最大字段長(zhǎng)度為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)

    """
        #  不使用循環(huán)逐條填充
        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)
    """
    # 返回計(jì)算后的表格的下兩個(gè)單元的實(shí)際偏移位置
    return section + remove_header_count + 3

# 測(cè)試恒矩陣
def Test():
    val = \
    [
        ["測(cè)試地址", "測(cè)試地址","磁盤路徑", "總?cè)萘?, "剩余容量"],
        ["/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("返回下一個(gè)表格索引: {}".format(ref))

    ref = CreateTable("192.168.1.1",val,ref)
    print("返回下一個(gè)表格索引: {}".format(ref))

    workbook.close()

# 測(cè)試豎矩陣
def Test2():

    header = ["測(cè)試地址", "測(cè)試地址","磁盤路徑", "總?cè)萘?, "剩余容量"]

    val = \
    [
        ["/etc/system/", "1024 GB", "256 GB"],
        ["/etc/passwd/", "104 GB", "345GB"],
        ["/etc/username/", "12 GB", "56 GB"],
        ["/etc/lyshark/", "12 GB", "56 GB"]
    ]

    # 橫向矩陣轉(zhuǎn)豎向矩陣
    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("返回下一個(gè)表格索引: {}".format(ref))

    workbook.close()

if __name__ == "__main__":
    Test2()

輸出效果如下所示；

柱狀圖輸出函數(shù)

簡(jiǎn)單實(shí)現(xiàn)CreateChart(headings,data)柱狀圖生成函數(shù)，通過(guò)傳入頭部標(biāo)題和數(shù)據(jù)集列表即可完成表單生成。

import xlsxwriter

workbook = xlsxwriter.Workbook('lyshark.xlsx')
worksheet = workbook.add_worksheet("統(tǒng)計(jì)表")

def CreateChart(headings,data):
    # 定義表格樣式
    head_style = workbook.add_format({"bold": True, "align": "center", "font": 13})

    # 逐條寫入數(shù)據(jù)
    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": "=統(tǒng)計(jì)表!$B$1",              # 圖例項(xiàng)
        "categories": "=統(tǒng)計(jì)表!$A$2:$A$10",  # X軸Item名稱
        "values": "=統(tǒng)計(jì)表!$B$2:$B$10"       # X軸Item值
    })
    chart.add_series({
        "name": "=統(tǒng)計(jì)表!$C$1",
        "categories": "=統(tǒng)計(jì)表!$A$2:$A$10",
        "values": "=統(tǒng)計(jì)表!$C$2:$C$10"
    })
    chart.add_series({
        "name": "=統(tǒng)計(jì)表!$D$1",
        "categories": "=統(tǒng)計(jì)表!$A$2:$A$10",
        "values": "=統(tǒng)計(jì)表!$D$2:$D$10"
    })

    # 添加柱狀圖標(biāo)題
    chart.set_title({"name": "性能統(tǒng)計(jì)柱狀圖"})
    chart.set_style(12)

    # 在G2處繪制
    worksheet.insert_chart("G2", chart)
    workbook.close()

if __name__ == "__main__":
    headings = ["主機(jī)地址", "CPU利用率", "內(nèi)存利用率", "交換分區(qū)"]
    data = [["192.168.1.100", 88, 36, 66], ["192.168.1.200", 98, 89, 66], ["192.168.1.220", 88, 100, 32]]

    # 循環(huán)添加模擬數(shù)據(jù)
    for i in range(1, 100):
        s = ["192.168.1.{}".format(i), i, i, i]
        data.append(s)

    CreateChart(headings,data)

輸出效果如下所示；

條形圖輸出函數(shù)

封裝CreateChart(headings,data)函數(shù)實(shí)現(xiàn)輸出條形圖，并將前十的數(shù)據(jù)繪成圖展示在右側(cè)。

import xlsxwriter

workbook = xlsxwriter.Workbook('lyshark.xlsx')
worksheet = workbook.add_worksheet("統(tǒng)計(jì)表")

def CreateChart(headings,data):
    # 定義表格樣式
    head_style = workbook.add_format({"bold": True, "align": "center", "fg_color": "#D7E4BC"})
    worksheet.set_column("A1:D1", 15)

    # 逐條寫入數(shù)據(jù)
    worksheet.write_row("A1", headings, head_style)
    for i in range(0, len(data)):
        worksheet.write_row("A{}".format(i + 2), data[i])

    # 添加條形圖，顯示前十個(gè)元素
    chart = workbook.add_chart({"type": "line"})
    chart.add_series({
        "name": "=統(tǒng)計(jì)表!$B$1",              # 圖例項(xiàng)
        "categories": "=統(tǒng)計(jì)表!$A$2:$A$10",  # X軸Item名稱
        "values": "=統(tǒng)計(jì)表!$B$2:$B$10"       # X軸Item值
    })
    chart.add_series({
        "name": "=統(tǒng)計(jì)表!$C$1",
        "categories": "=統(tǒng)計(jì)表!$A$2:$A$10",
        "values": "=統(tǒng)計(jì)表!$C$2:$C$10"
    })
    chart.add_series({
        "name": "=統(tǒng)計(jì)表!$D$1",
        "categories": "=統(tǒng)計(jì)表!$A$2:$A$10",
        "values": "=統(tǒng)計(jì)表!$D$2:$D$10"
    })

    # 添加柱狀圖標(biāo)題
    chart.set_title({"name": "負(fù)載統(tǒng)計(jì)條形圖"})
    # 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 = ["獲取時(shí)間", "1分鐘負(fù)載", "5分鐘負(fù)載", "15分鐘負(fù)載"]
    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)

輸出效果如下所示；

餅狀圖輸出函數(shù)

函數(shù)CreateChart(headings,data)用于生成餅狀圖，實(shí)現(xiàn)對(duì)主機(jī)以及主機(jī)數(shù)量的圖形化展示。

import xlsxwriter

workbook = xlsxwriter.Workbook('lyshark.xlsx')
worksheet = workbook.add_worksheet("統(tǒng)計(jì)表")

def CreateChart(headings,data):
    # 定義表格樣式
    head_style = workbook.add_format({"bold": True, "align": "center", "fg_color": "#D7E4BC"})
    worksheet.set_column("A1:D1", 15)

    # 逐條寫入數(shù)據(jù)
    worksheet.write_row("A1", headings, head_style)
    for i in range(0, len(data)):
        worksheet.write_row("A{}".format(i + 2), data[i])

    # 添加條形圖，顯示前十個(gè)元素
    chart = workbook.add_chart({"type": "pie"})
    chart.add_series({
        "name": "=統(tǒng)計(jì)表!$B$1",             # 圖例項(xiàng)
        "categories": "=統(tǒng)計(jì)表!$A$2:$A$4",  # X軸 Item名稱
        "values": "=統(tǒng)計(jì)表!$B$2:$B$4"       # X軸Item值
    })

    # 添加餅狀圖
    chart.set_title({"name": "系統(tǒng)版本分布"})
    chart.set_size({'width': 600, 'height': 300})
    chart.set_legend({'position': 'right'})

    # 在D2處繪制
    worksheet.insert_chart("D2", chart)
    workbook.close()

if __name__ == "__main__":
    headings = ["系統(tǒng)版本", "數(shù)量"]
    data = [["Suse", 30], ["Centos", 25], ["AIX", 15]]
    CreateChart(headings,data)

輸出效果如下所示；

實(shí)現(xiàn)繪圖類

通過(guò)調(diào)用xlsxwriter第三方庫(kù),實(shí)現(xiàn)繪制各類通用圖形,并保存為XLS文檔格式.

import xlsxwriter

class DrawChart():
    def __init__(self,workbook):
        self.workbook = xlsxwriter.Workbook(workbook)

    # 排序函數(shù),以第三列為條件排列
    def cpu_takeSecond(self,elem):
        return int(elem[3])
    def mem_taskSecond(self,elem):
        return int(elem[1])

    # 封裝統(tǒng)計(jì)主機(jī)磁盤使用情況
    def CreateDiskTable(self,worksheet,address,data,section):
        # 添加統(tǒng)計(jì)名稱 例如: 磁盤統(tǒng)計(jì)
        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'})

        # 根據(jù)磁盤路徑計(jì)算出表格大小
        header_range = "A{}:B{}".format(section,section+header_count)
        worksheet.write_row(header_range, data[0], merge_format1)  # 顯示表頭

        # 計(jì)算合并表頭偏移
        header_merge_range = "A{}:B{}".format(section,section)
        worksheet.merge_range(header_merge_range, "巡檢IP地址", merge_format1)  # 合并表頭(合并第一個(gè)元素)

        # 計(jì)算出地址所占總單元格大小
        address_merge_range = "A{}:B{}".format(section+1,section+header_count)
        worksheet.merge_range(address_merge_range, address , merge_format)  #需要計(jì)算出來(lái)，根據(jù)傳入分區(qū)數(shù)量

        # 通過(guò)計(jì)算得到磁盤路徑所對(duì)應(yīng)到表中的位置
        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)

        # 計(jì)算出總?cè)萘繉?duì)應(yīng)到表中的位置
        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)

        # 計(jì)算出剩余容量對(duì)應(yīng)到表中的位置
        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)

        # 計(jì)算出利用率對(duì)應(yīng)到表中的位置
        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)

        # 返回計(jì)算后的表格的下兩個(gè)單元的實(shí)際偏移位置
        return section + header_count + 3

    # 創(chuàng)建CPU利用率百分比,并統(tǒng)計(jì)前十
    def CreateCpuUsedTable(self,worksheet,header,data):
        worksheet = self.workbook.add_worksheet(worksheet)

        # 設(shè)置頭部顏色,并寫入頭部數(shù)據(jù)
        head_style = self.workbook.add_format({"bold": True, "align": "center", "fg_color": "#D7E4BC"})
        worksheet.write_row("A1", header, head_style)

        # 設(shè)置頭部列寬
        worksheet.set_column("A1:D1", 15)

        # 排序,統(tǒng)計(jì)第三列數(shù)據(jù)，將最大的放在最前面，以此向下
        data.sort(key=self.cpu_takeSecond, reverse=True)

        # 將數(shù)據(jù)批量添加到表格中
        for x in range(0,len(data)):
            worksheet.write_row("A{}".format(x + 2), data[x])

        # --------------------------------------------------------------
        # 添加柱狀圖(開(kāi)始繪圖)
        chart = self.workbook.add_chart({"type": "column"})
        chart.add_series({
            "name": "=CPU利用率!$B$1",              # 圖例項(xiàng)(也就是CPU內(nèi)核態(tài))
            "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"
        })

        # 添加柱狀圖標(biāo)題
        chart.set_title({"name": "CPU 性能統(tǒng)計(jì)柱狀圖"})
        # 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)

    # 內(nèi)存利用率統(tǒng)計(jì)
    def CreateMemoryTable(self, worksheet, header, data):
        worksheet = self.workbook.add_worksheet(worksheet)

        # 設(shè)置頭部顏色,并寫入頭部數(shù)據(jù)
        head_style = self.workbook.add_format({"bold": True, "align": "center", "fg_color": "#D7E4BC"})
        worksheet.write_row("A1", header, head_style)

        # 設(shè)置頭部列寬
        worksheet.set_column("A1:D1", 15)

        # 排序,統(tǒng)計(jì)第三列數(shù)據(jù)，將最大的放在最前面，以此向下
        data.sort(key=self.mem_taskSecond, reverse=True)

        # 將數(shù)據(jù)批量添加到表格中
        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": "=內(nèi)存利用率!$B$1",
            "categories": "=內(nèi)存利用率!$A$2:$A$10",
            "values": "=內(nèi)存利用率!$B$2:$B$10"
        })
        chart.add_series({
            "name": "=內(nèi)存利用率!$C$1",
            "categories": "=內(nèi)存利用率!$A$2:$A$10",
            "values": "=內(nèi)存利用率!$C$2:$C$10"
        })
        # 添加柱狀圖標(biāo)題
        chart.set_title({"name": "內(nèi)存利用率統(tǒng)計(jì)圖"})
        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)

    # --------------------------------------------------------------
    # 統(tǒng)計(jì)CPU Load 負(fù)載情況 注意: 只能指定單獨(dú)的主機(jī)
    def CreateCpuLoadAvgTable(self, address,worksheet, header, data):
        worksheet = self.workbook.add_worksheet(worksheet)

        # 設(shè)置頭部顏色,并寫入頭部數(shù)據(jù)
        head_style = self.workbook.add_format({"bold": True, "align": "center", "fg_color": "#D7E4BC"})
        worksheet.write_row("A1", header, head_style)

        # 設(shè)置頭部列寬
        worksheet.set_column("A1:D1", 15)

        # 將數(shù)據(jù)批量添加到表格中
        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)

        # 逐條寫入數(shù)據(jù)
        worksheet.write_row("A1", header, head_style)
        for i in range(0, len(data)):
            worksheet.write_row("A{}".format(i + 2), data[i])

        # 添加條形圖，顯示前十個(gè)元素
        chart = self.workbook.add_chart({"type": "line"})
        chart.add_series({
            "name": "=CPU負(fù)載數(shù)據(jù)統(tǒng)計(jì)!$B$1",  # 圖例項(xiàng)
            "categories": "=CPU負(fù)載數(shù)據(jù)統(tǒng)計(jì)!$A$2:$A$10",  # X軸 Item名稱
            "values": "=CPU負(fù)載數(shù)據(jù)統(tǒng)計(jì)!$B$2:$B$10"  # X軸Item值
        })
        chart.add_series({
            "name": "=CPU負(fù)載數(shù)據(jù)統(tǒng)計(jì)!$C$1",  # 第一個(gè)線條（圖例）
            "categories": "=CPU負(fù)載數(shù)據(jù)統(tǒng)計(jì)!$A$2:$A$10",
            "values": "=CPU負(fù)載數(shù)據(jù)統(tǒng)計(jì)!$C$2:$C$10"
        })
        chart.add_series({
            "name": "=CPU負(fù)載數(shù)據(jù)統(tǒng)計(jì)!$D$1",  # 第二個(gè)線條（圖例）
            "categories": "=CPU負(fù)載數(shù)據(jù)統(tǒng)計(jì)!$A$2:$A$10",
            "values": "=CPU負(fù)載數(shù)據(jù)統(tǒng)計(jì)!$D$2:$D$10"
        })

        # 添加柱狀圖標(biāo)題
        chart.set_title({"name": "統(tǒng)計(jì)地址: {}".format(address)})
        chart.set_size({'width': 900, 'height': 500})
        chart.set_legend({'position': 'top'})

        # 在F2處繪制
        worksheet.insert_chart("F2", chart)

    # 關(guān)閉并保存繪制結(jié)果
    def Save(self):
        self.workbook.close()

if __name__ == "__main__":
    work = DrawChart("lyshark.xlsx")
    # ------------------------------------------------------------------
    # 統(tǒng)計(jì)系統(tǒng)磁盤容量
    disk_val = [
        ["IP地址", "IP地址", "磁盤路徑", "總?cè)萘?, "剩余容量", "利用率"],
        ["/etc/system/", "/proc/", "/sys", "/abc/lyshark"],
        ["1024GG", "2048GB", "111GB", "1111GB"],
        ["1024GG", "2048GB", "111GB", "22GB"],
        ["10%", "50%", "20%", "33%"]
    ]
    ref = work.CreateDiskTable("磁盤分區(qū)統(tǒng)計(jì)","127.0.0.1",disk_val,3)
    print("下個(gè)表格開(kāi)頭位置: {}".format(ref))
    print("[+] 磁盤數(shù)據(jù)統(tǒng)計(jì)完成")

    # -------------------------------------------------------------------
    # 統(tǒng)計(jì)系統(tǒng)CPU負(fù)載情況
    header = ["主機(jī)地址", "CPU內(nèi)核態(tài)", "CPU用戶態(tài)", "總利用率"]
    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利用率統(tǒng)計(jì)已完成")

    # -------------------------------------------------------------------
    # 統(tǒng)計(jì)系統(tǒng)內(nèi)存利用率數(shù)據(jù)
    header = ["主機(jī)地址", "通用內(nèi)存利用率", "交換內(nèi)存利用率"]
    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("內(nèi)存利用率",header,mem_val)
    print("[+] 內(nèi)存利用率統(tǒng)計(jì)已完成")

    # -------------------------------------------------------------------
    # 獲取CPU LoadAvg負(fù)載情況
    header = ["拉取日期","1分鐘負(fù)載","5分鐘負(fù)載","15分鐘負(fù)載"]
    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負(fù)載數(shù)據(jù)統(tǒng)計(jì)",header,cpu_avg_val)
    print("[+] CPU負(fù)載統(tǒng)計(jì)完成")
    work.Save()

輸出效果如下所示；