本篇文章同步發布於 Python 使用 openpyxl 將 Excel 格式化為表格【Python 處理 Excel #23】

前言

我喜歡將 Excel 的內容格式化表格，因為這樣在 Excel 公式中引用欄位時，可以使用有意義欄位名稱 (例如 [@訂單號碼])，而非一般的儲存格參照 (例如 A1、B2)。而且在表格中新增數據時，Excel 會自動擴展表格範圍並填充公式，再加上格式化後的表格能輕易篩選，所以如果需要在 Excel 中處理資料，我通常會先將 Excel 內容格式化為表格。這篇文章分享如何使用 Python 的 openpyxl 將 Excel 格式化為表格。

文章案例說明

文章中使用 example.xlsx 作為說明用的案例資料。example.xlsx 的內容如下：

如何將 Excel 內容格式化為表格？

下方的程式碼可以傳入一個 Excel 工作表，然後將那個工作表格式化為表格，預設的樣式是藍色表頭的淺色 9 (TableStyleLight9)：

def format_as_excel_table(ws, table_name="Table1", style_name="TableStyleLight9"):
    ref = f"A1:{get_column_letter(ws.max_column)}{ws.max_row}"
    tab = Table(displayName=table_name, ref=ref)
    style = TableStyleInfo(
        name=style_name,
        showFirstColumn=False,
        showLastColumn=False,
        showRowStripes=False,
        showColumnStripes=False
    )
    tab.tableStyleInfo = style
    ws.add_table(tab)

解釋

以下逐段拆解 format_as_excel_table 函數的程式碼。

定義函數

def format_as_excel_table(ws, table_name="Table1", style_name="TableStyleLight9"):

• ws：要格式化的工作表 (Worksheet) 物件。
• table_name：格式化後的表格的名稱，預設為 Table1。這個名稱會在 Excel 顯示。
• style_name：表格樣式的名稱，預設為 TableStyleLight9。這個參數讓使用者能傳入不同的樣式。

定義表格範圍

ref = f"A1:{get_column_letter(ws.max_column)}{ws.max_row}"

• ref：定義表格範圍。使用 get_column_letter(ws.max_column) 取得當前工作表中最後一個欄位位置並用字母表示，並結合 ws.max_row 取得當前工作表中的最大的列數。最終產生從 A1 到最後一個儲存格的範圍，例如表格有 3 個欄位和 10 列，則產生的範圍是 A1:C10。

建立表格物件

tab = Table(displayName=table_name, ref=ref)

• tab：建立一個新的表格物件，並指定表格的名稱和範圍。displayName 參數用來設置表格在 Excel 中顯示的名稱，而 ref 則是之前定義的表格範圍。

定義表格樣式

style = TableStyleInfo(
    name=style_name,
    showFirstColumn=False,
    showLastColumn=False,
    showRowStripes=False,
    showColumnStripes=False
)

• style：建立一個新的樣式物件，使用 TableStyleInfo 類別定義表格的樣式。
• name=style_name：使用傳入的樣式名稱設置樣式，目前傳入 TableStyleLight9。
• showFirstColumn=False：不顯示第一個欄位的特殊樣式。
• showLastColumn=False：不顯示最後一個欄位的特殊樣式。
• showRowStripes=False：不顯示列條紋，即列跟列之間會不會交替不同顏色。
• showColumnStripes=False：不顯示欄位的條紋。

將樣式套用到表格

tab.tableStyleInfo = style

將定義的樣式套用到先前建立的表格物件上，使其具有指定的外觀和格式。

將表格增加到工作表

ws.add_table(tab)

最後將建立並表格化後的表格增加到工作表中，這會讓 Excel 在該工作表中顯示剛剛建立的格式化表格。

測試用的程式碼案例

如果想要嘗試使用 openpyxl 將 Excel 內容格式化為表格，可以使用以下程式碼測試：

import pandas as pd
import openpyxl
from openpyxl.utils import get_column_letter
from openpyxl.worksheet.table import Table, TableStyleInfo

def create_workbook_and_sheet(filename, sheet_name):
    wb = openpyxl.Workbook()
    ws = wb.active
    ws.title = sheet_name
    return wb, ws

def add_data_to_sheet(ws, data):
    for row in data:
        ws.append(row)

def create_styled_excel_from_input(input_filename, output_filename):
    # 使用 pandas 讀取 Excel 檔案
    df = pd.read_excel(input_filename)

    # 建立新的活頁簿和工作表
    wb, ws = create_workbook_and_sheet(output_filename, "Orders")

    # 將 DataFrame 的標題增加到工作表中
    add_data_to_sheet(ws, [df.columns.tolist()])  # 增加標題列

    # 將 DataFrame 的數據增加到工作表中
    add_data_to_sheet(ws, df.values.tolist())

    # 格式化為表格
    format_as_excel_table(ws)

    # 儲存 Excel 檔案
    wb.save(output_filename)

# 測試案例
create_styled_excel_from_input("example.xlsx", "styled_example.xlsx")

總結

• 結合 get_column_letter(ws.max_column) 和 ws.max_row 能確認欲格式化的表格範圍。
• 透過 openpyxl 的 Table 類別能建立的表格物件，並指定表格名稱和範圍。
• 使用 openpyxl 的 TableStyleInfo 物件能定義表格樣式，包括是否顯示條紋和指定特殊樣式。

本篇文章同步發布於 Python 使用 openpyxl 將 Excel 格式化為表格【Python 處理 Excel #23】