xlsx-manipulation

XLSX 操作技能

概述

此技能可讓您使用 openpyxl 程式庫，以程式方式建立、編輯和操作 Microsoft Excel (.xlsx) 試算表。無需手動編輯，即可建立包含公式、格式、圖表和資料驗證的專業試算表。

使用方式

1. 描述您想建立或修改的試算表
2. 提供資料、公式或格式需求
3. 我將產生 openpyxl 程式碼並執行

範例提示：

• "建立一個包含每月追蹤的預算試算表"
• "新增條件式格式，將高於門檻的值標示出來"
• "從這份資料產生類似樞紐分析表的摘要"
• "建立包含圖表和 KPI 的儀表板"

領域知識

openpyxl 基礎

from openpyxl import Workbook, load_workbook
from openpyxl.styles import Font, Fill, Border, Alignment
from openpyxl.chart import BarChart, Reference

# 建立新活頁簿
wb = Workbook()
ws = wb.active

# 或開啟現有檔案
wb = load_workbook('existing.xlsx')
ws = wb.active

活頁簿結構

Workbook
├── worksheets (工作表/標籤)
│   ├── cells (資料儲存)
│   ├── rows/columns (格式設定)
│   ├── merged_cells
│   └── charts
├── defined_names (命名範圍)
└── styles (格式範本)

操作儲存格

基本儲存格操作

# 依儲存格參照
ws['A1'] = 'Header'
ws['B1'] = 42

# 依列、欄
ws.cell(row=1, column=3, value='Data')

# 多個儲存格
ws['A1:C1'] = [['Col1', 'Col2', 'Col3']]

# 附加列
ws.append(['Row', 'Data', 'Here'])

讀取儲存格

# 單一儲存格
value = ws['A1'].value

# 儲存格範圍
for row in ws['A1:C3']:
    for cell in row:
        print(cell.value)

# 迭代列
for row in ws.iter_rows(min_row=1, max_row=10, min_col=1, max_col=3):
    for cell in row:
        print(cell.value)

公式

# 基本公式
ws['D1'] = '=SUM(A1:C1)'
ws['D2'] = '=AVERAGE(A2:C2)'
ws['E1'] = '=IF(D1>100,"High","Low")'

# 命名範圍
from openpyxl.workbook.defined_name import DefinedName
ref = "Sheet!$A$1:$C$10"
defn = DefinedName("SalesData", attr_text=ref)
wb.defined_names.add(defn)

# 使用命名範圍
ws['F1'] = '=SUM(SalesData)'

格式設定

儲存格樣式

from openpyxl.styles import Font, Fill, PatternFill, Border, Side, Alignment

# 字型
ws['A1'].font = Font(
    name='Arial',
    size=14,
    bold=True,
    italic=False,
    color='FF0000'  # 紅色
)

# 填滿 (背景)
ws['A1'].fill = PatternFill(
    start_color='FFFF00',  # 黃色
    end_color='FFFF00',
    fill_type='solid'
)

# 框線
thin_border = Border(
    left=Side(style='thin'),
    right=Side(style='thin'),
    top=Side(style='thin'),
    bottom=Side(style='thin')
)
ws['A1'].border = thin_border

# 對齊
ws['A1'].alignment = Alignment(
    horizontal='center',
    vertical='center',
    wrap_text=True
)

數字格式

# 貨幣
ws['B2'].number_format = '$#,##0.00'

# 百分比
ws['C2'].number_format = '0.00%'

# 日期
ws['D2'].number_format = 'YYYY-MM-DD'

# 自訂
ws['E2'].number_format = '#,##0.00 "units"'

條件式格式

from openpyxl.formatting.rule import ColorScaleRule, CellIsRule, FormulaRule
from openpyxl.styles import PatternFill

# 色階 (熱力圖)
color_scale = ColorScaleRule(
    start_type='min', start_color='FF0000',
    end_type='max', end_color='00FF00'
)
ws.conditional_formatting.add('A1:A10', color_scale)

# 儲存格值規則
red_fill = PatternFill(start_color='FFCCCC', end_color='FFCCCC', fill_type='solid')
rule = CellIsRule(operator='greaterThan', formula=['100'], fill=red_fill)
ws.conditional_formatting.add('B1:B10', rule)

圖表

from openpyxl.chart import BarChart, LineChart, PieChart, Reference

# 準備資料
data = Reference(ws, min_col=2, min_row=1, max_col=3, max_row=5)
categories = Reference(ws, min_col=1, min_row=2, max_row=5)

# 長條圖
chart = BarChart()
chart.type = "col"  # 或 "bar" 為水平長條圖
chart.title = "Sales by Region"
chart.add_data(data, titles_from_data=True)
chart.set_categories(categories)
chart.shape = 4
ws.add_chart(chart, "E1")

# 折線圖
line = LineChart()
line.title = "Trend Analysis"
line.add_data(data, titles_from_data=True)
line.set_categories(categories)
ws.add_chart(line, "E15")

# 圓餅圖
pie = PieChart()
pie.add_data(data, titles_from_data=True)
pie.set_categories(categories)
ws.add_chart(pie, "M1")

資料驗證

from openpyxl.worksheet.datavalidation import DataValidation

# 下拉式清單
dv = DataValidation(
    type="list",
    formula1='"Option1,Option2,Option3"',
    allow_blank=True
)
dv.error = "請從清單中選取"
dv.errorTitle = "無效輸入"
ws.add_data_validation(dv)
dv.add('A1:A100')

# 數字範圍
dv_num = DataValidation(
    type="whole",
    operator="between",
    formula1="1",
    formula2="100"
)
ws.add_data_validation(dv_num)
dv_num.add('B1:B100')

工作表操作

# 建立新工作表
ws2 = wb.create_sheet("Data")
ws3 = wb.create_sheet("Summary", 0)  # 在位置 0

# 重新命名
ws.title = "Main Report"

# 刪除
del wb["Sheet2"]

# 複製
source = wb["Template"]
target = wb.copy_worksheet(source)

列/欄操作

# 設定欄寬
ws.column_dimensions['A'].width = 20

# 設定列高
ws.row_dimensions[1].height = 30

# 隱藏欄
ws.column_dimensions['C'].hidden = True

# 凍結窗格
ws.freeze_panes = 'B2'  # 凍結第 1 列與 A 欄

# 自動篩選
ws.auto_filter.ref = "A1:D100"

最佳實務

1. 使用範本：對於複雜格式，從 .xlsx 範本開始
2. 批次操作：盡量減少逐格操作以提升速度
3. 命名範圍：使用定義名稱讓公式更清晰
4. 資料驗證：加入驗證以防止輸入錯誤
5. 增量儲存：對於大型檔案，定期儲存

常見模式

資料匯入

def import_csv_to_xlsx(csv_path, xlsx_path):
    import csv
    wb = Workbook()
    ws = wb.active
    
    with open(csv_path) as f:
        reader = csv.reader(f)
        for row in reader:
            ws.append(row)
    
    wb.save(xlsx_path)

報表範本

def create_monthly_report(data, output_path):
    wb = Workbook()
    ws = wb.active
    ws.title = "Monthly Report"
    
    # 標題列
    headers = ['Date', 'Revenue', 'Expenses', 'Profit']
    ws.append(headers)
    
    # 設定標題樣式
    for col in range(1, 5):
        cell = ws.cell(1, col)
        cell.font = Font(bold=True)
        cell.fill = PatternFill('solid', fgColor='4472C4')
        cell.font = Font(bold=True, color='FFFFFF')
    
    # 資料
    for row in data:
        ws.append(row)
    
    # 加入總計
    last_row = len(data) + 1
    ws.cell(last_row + 1, 1, 'TOTAL')
    ws.cell(last_row + 1, 2, f'=SUM(B2:B{last_row})')
    ws.cell(last_row + 1, 3, f'=SUM(C2:C{last_row})')
    ws.cell(last_row + 1, 4, f'=SUM(D2:D{last_row})')
    
    wb.save(output_path)

範例

範例 1：預算追蹤器

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter

wb = Workbook()
ws = wb.active
ws.title = "Budget 2024"

# 標題列
months = ['Category', 'Jan', 'Feb', 'Mar', 'Q1 Total']
ws.append(months)

# 類別與資料
budget_data = [
    ['Salary', 5000, 5000, 5000],
    ['Rent', -1500, -1500, -1500],
    ['Utilities', -200, -180, -220],
    ['Food', -400, -450, -380],
    ['Transport', -150, -160, -140],
    ['Entertainment', -200, -250, -200],
]

for row in budget_data:
    ws.append(row + [f'=SUM(B{ws.max_row + 1}:D{ws.max_row + 1})'])

# 總計列
ws.append(['TOTAL', 
    f'=SUM(B2:B{ws.max_row})',
    f'=SUM(C2:C{ws.max_row})',
    f'=SUM(D2:D{ws.max_row})',
    f'=SUM(E2:E{ws.max_row})'
])

# 格式設定
header_fill = PatternFill('solid', fgColor='366092')
header_font = Font(bold=True, color='FFFFFF')

for cell in ws[1]:
    cell.fill = header_fill
    cell.font = header_font
    cell.alignment = Alignment(horizontal='center')

# 貨幣格式
for row in ws.iter_rows(min_row=2, min_col=2, max_col=5):
    for cell in row:
        cell.number_format = '$#,##0.00'

# 欄寬
ws.column_dimensions['A'].width = 15
for col in range(2, 6):
    ws.column_dimensions[get_column_letter(col)].width = 12

wb.save('budget_2024.xlsx')

範例 2：銷售儀表板

from openpyxl import Workbook
from openpyxl.chart import BarChart, PieChart, Reference
from openpyxl.styles import Font, PatternFill

wb = Workbook()
ws = wb.active
ws.title = "Sales Dashboard"

# 資料
ws.append(['Region', 'Q1', 'Q2', 'Q3', 'Q4'])
data = [
    ['North', 150000, 165000, 180000, 195000],
    ['South', 120000, 125000, 140000, 155000],
    ['East', 180000, 190000, 210000, 225000],
    ['West', 95000, 110000, 125000, 140000],
]
for row in data:
    ws.append(row)

# 長條圖
data_ref = Reference(ws, min_col=2, min_row=1, max_col=5, max_row=5)
cats_ref = Reference(ws, min_col=1, min_row=2, max_row=5)

bar = BarChart()
bar.type = "col"
bar.title = "Quarterly Sales by Region"
bar.add_data(data_ref, titles_from_data=True)
bar.set_categories(cats_ref)
bar.height = 10
bar.width = 15
ws.add_chart(bar, "A8")

# 圓餅圖 - Q4 細項
pie_data = Reference(ws, min_col=5, min_row=1, max_row=5)
pie = PieChart()
pie.title = "Q4 Market Share"
pie.add_data(pie_data, titles_from_data=True)
pie.set_categories(cats_ref)
ws.add_chart(pie, "J8")

wb.save('sales_dashboard.xlsx')

限制

• 無法執行 VBA 巨集
• 複雜的樞紐分析表未完全支援
• 走勢圖支援有限
• 不支援外部資料連線
• 部分進階圖表類型無法使用

安裝

pip install openpyxl

資源

• openpyxl 文件
• GitHub 儲存庫
• 使用樣式