Python利用xlsxwriter讀寫Excel檔案(持續補充)

前言

有時我們要針對一些資料做統計報告，在檔案過多亦或資料量大，excel操作重復操作又過多等情況下，我們可以利用Python進行資料分析處理，

例子

讀取文本資料

假設有文本資料如下：

201801136 61.0 68.0 60.0
201801137 94.0 64.0 75.0
201801138 88.0 77.0 82.0
201801139 87.0 84.0 72.0
201801140 93.0 69.0 74.0

我們利用python讀取并將除了第一列學號外的資料，整理到numpy矩陣中，代碼如下：

def read_data(file):
    with open(file, "r") as f:
        id, subject_1, subject_2, subject_3 =[], [], [], []
        for line in f.readlines():
            line = line.strip('\n')  # 去掉串列中每一個元素的換行符
            line = line.split(' ')
            id.append((line[0]))
            subject_1.append(float(line[1]))
            subject_2.append(float(line[2]))
            subject_3.append(float(line[3]))
        row, col = len(subject_1), 3
        table = np.zeros((row, col))
        table[:, 0], table[:, 1], table[:, 2] = subject_1, subject_2, subject_3
        # head = ['subject_1', 'subject_2', 'subject_3']
        # data = pd.DataFrame(table)
        # data.columns = head
        return table

資料寫入Excel

1. 設定單元格格式

    style = workbook.add_format({
        "fg_color": "yellow",  # 單元格的背景顏色
        "bold": 1,             # 字體加粗
        "align": "center",     # 對齊方式
        "valign": "vcenter",   # 字體對齊方式
        "font_color": "red"    # 字體顏色
    })
    # 在Excel的第r行第c列以style格式寫入data[r][c]資料
    worksheet.write(r, c, data[r][c], style)
    worksheet.write_row('B1', head, style_more)  # 寫入表頭
    # 設定單元格寬度
    width = 20
    worksheet.set_column(0, col, width)

2. 呼叫Excel自身的公式

    worksheet.write(row, col+1, '=SUM(B6:D6)')

3. 設定資料格式為百分比

    # 設定資料格式為百分比
    style_pre = workbook.add_format({'num_format': '0.000%'})

4. 插入Excel圖表

    chart = workbook.add_chart({"type": "column"})
    # column 柱狀圖
    # area面積圖
    # bar 條形圖
    # line折現圖
    # radar雷達圖
    # 5為圖表添加資料
    chart.add_series(
        {"name": "成績",  # 標題
         "categories": "=Score!$b$1:$d$1",  # 統計項名稱 作業簿名稱+資料
         "values": "=Score!$b$2:$d$2",  # 統計值 作業簿名稱+資料
         "line": {"color": "black", "bold": True}  # 柱子邊顏色
         }
    )
    worksheet.insert_chart("A11", chart)

5. 簡化Demo

def write_excel(file_name):
    workbook = xlsxwriter.Workbook(file_name)
    # 在檔案中創建一個名為TEST的sheet,不加名字默認為sheet1
    worksheet = workbook.add_worksheet(u'Score')  
    worksheet.write(0, 0, '數學')
    worksheet.write(1, 1, 521.1314)
    workbook.close()

6. 完整代碼

import os
import numpy as np
import pandas as pd
import xlrd
import xlwt
import xlsxwriter
import matplotlib.pyplot as plt


def read_data(file):
    with open(file, "r") as f:
        id, subject_1, subject_2, subject_3 =[], [], [], []
        for line in f.readlines():
            line = line.strip('\n')  # 去掉串列中每一個元素的換行符
            line = line.split(' ')
            id.append((line[0]))
            subject_1.append(float(line[1]))
            subject_2.append(float(line[2]))
            subject_3.append(float(line[3]))
        row, col = len(subject_1), 3
        table = np.zeros((row, col))
        table[:, 0], table[:, 1], table[:, 2] = subject_1, subject_2, subject_3
        # head = ['subject_1', 'subject_2', 'subject_3']
        # data = pd.DataFrame(table)
        # data.columns = head
        return table


def write_excel_demo(file_name):
    workbook = xlsxwriter.Workbook(file_name)
    # 在檔案中創建一個名為TEST的sheet,不加名字默認為sheet1
    worksheet = workbook.add_worksheet(u'Test')
    worksheet.write(0, 0, '數學')
    workbook.close()

def write_excel(file_name, data):
    workbook = xlsxwriter.Workbook(file_name)
    # 1）設定單元格格式
    style = workbook.add_format({
        "align": "center",    # 對齊方式
        "valign": "vcenter",  # 字體對齊方式
    })
    style_more = workbook.add_format({
        "fg_color": "yellow",  # 單元格的背景顏色
        "bold": 1,             # 字體加粗
        "align": "center",     # 對齊方式
        "valign": "vcenter",   # 字體對齊方式
        "font_color": "red"    # 字體顏色
    })

    # 2）設定資料格式為百分比
    style_pre = workbook.add_format({'num_format': '0.000%'})

    worksheet = workbook.add_worksheet(u'Score')  # 在檔案中創建一個名為Score的sheet,不加名字默認為sheet1
    row, col = len(data), len(data[0, :])
    width = 20
    # 3) 設定單元格寬度
    worksheet.set_column(0, col, width)
    head = ['subject_1', 'subject_2', 'subject_3']
    '''
    for i in range(len(head)):
        worksheet.write(0, i+1, head[i], style_more) 
    '''
    worksheet.write_row('B1', head, style_more)  # 寫入表頭
    for i in range(row):
        for j in range(col):
            worksheet.write(i+1, j+1, data[i][j], style)
    # 4) 呼叫Excel自身的公式
    worksheet.write(row, col+1, '=SUM(B6:D6)')

    # 5) 插入Excel圖表
    chart = workbook.add_chart({"type": "column"})
    # column 柱狀圖
    # area面積圖
    # bar 條形圖
    # line折現圖
    # radar雷達圖
    # 5為圖表添加資料
    chart.add_series(
        {"name": "成績",  # 標題
         "categories": "=Score!$b$1:$d$1",  # 統計項名稱 作業簿名稱+資料
         "values": "=Score!$b$2:$d$2",  # 統計值 作業簿名稱+資料
         "line": {"color": "black", "bold": True}  # 柱子邊顏色
         }
    )
    worksheet.insert_chart("A11", chart)
    workbook.close()


if __name__ == '__main__':
    file_name = "test.xlsx"
    write_excel_demo(file_name)
    data = read_data("data/score.txt")
    file_name = 'result.xlsx'
    write_excel(file_name, data)

7. Excel檔案內容

總結

本文分節詳細，一是為了后續查尋方便，二是后續在學習作業中也會持續總結，畢竟熟能生巧，萬一一段時間不用，不熟練了也可以回來查找，以上代碼和資料都可以去我的GitHub下載，