openpyxl 是 Python 操作 Office Open XML 格式 (.xlsx) 的標準庫,支援讀寫、樣式、公式、圖表、資料驗證等完整功能。本文系統介紹核心 API、樣式系統、大資料優化模式與實戰技巧。
pip install openpyxl
核心物件層級:
Workbook (整個 .xlsx 檔案)
└─ Worksheet (工作表)
└─ Cell (儲存格)
├─ value (值)
├─ style (樣式: Font, Fill, Border, Alignment, NumberFormat, Protection)
└─ comment / hyperlink
from openpyxl import Workbook
from openpyxl.utils import get_column_letter
wb = Workbook()
ws = wb.active
ws.title = "銷售報表"
# 寫入儲存格
ws['A1'] = '商品'
ws['B1'] = '銷量'
ws.cell(row=2, column=1, value='筆電')
ws.cell(row=2, column=2, value=150)
# 批次寫入 (列)
ws.append(['滑鼠', 300])
ws.append(['鍵盤', 200])
# 依欄位字母寫入
col = get_column_letter(3) # 'C'
ws[f'{col}1'] = '單價'
ws[f'{col}2'] = 25000
樣式物件**不可變**,需建立新物件指派:
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side, numbers
# 字型
header_font = Font(name='微軟正黑體', bold=True, size=12, color='FFFFFF')
# 填滿
header_fill = PatternFill(start_color='4472C4', end_color='4472C4', fill_type='solid')
# 對齊
center_align = Alignment(horizontal='center', vertical='center', wrap_text=True)
# 邊框
thin_border = Border(
left=Side(style='thin'),
right=Side(style='thin'),
top=Side(style='thin'),
bottom=Side(style='thin')
)
# 數字格式
currency_fmt = '#,##0'
pct_fmt = '0.00%'
# 套用
for cell in ws[1]: # 第一列
cell.font = header_font
cell.fill = header_fill
cell.alignment = center_align
cell.border = thin_border
ws['B2'].number_format = currency_fmt
ws['C2'].number_format = currency_fmt
ws.column_dimensions['A'].width = 20
ws.column_dimensions['B'].width = 12
ws.row_dimensions[1].height = 25
ws.freeze_panes = 'A2' # 凍結首列
ws.auto_filter.ref = ws.dimensions # 自動篩選
ws['D1'] = '總額'
ws['D2'] = '=B2*C2' # 公式字串
ws['D3'] = '=B3*C3'
ws['D4'] = '=SUM(D2:D3)'
# openpyxl 不計算公式值,Excel 開啟時計算
# 需預先計算值可用: from openpyxl.utils import FORMULAE; 或第三方庫 (pycel)
ws.merge_cells('A1:B1') # 或 ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=2)
ws['A1'] = '合併標題'
# 僅左上角儲存格保留值,其他變為 MergedCell
預設模式將整個工作表載入記憶體,大資料會 OOM。Write-Only 模式串流寫入,記憶體 O(1):
from openpyxl import Workbook
wb = Workbook(write_only=True)
ws = wb.create_sheet('BigData')
# 僅支援 append,不支援隨機存取、樣式、公式、合併
for i in range(100000):
ws.append([i, f'item_{i}', i * 1.5])
wb.save('big_data.xlsx')
限制:無法讀取、修改、樣式、公式、合併儲存格。純寫入大量資料首選。
from openpyxl import load_workbook
wb = load_workbook('existing.xlsx')
ws = wb['Sheet1']
print(ws['A1'].value)
# 僅讀模式 (記憶體優化)
wb = load_workbook('large.xlsx', read_only=True)
for row in wb.active.iter_rows(values_only=True):
print(row)
wb.close()
| 操作 | 代碼 |
|---|---|
| 插入列 | ws.insert_rows(2) |
| 刪除列 | ws.delete_rows(2) |
| 插入欄 | ws.insert_cols(2) |
| 刪除欄 | ws.delete_cols(2) |
| 移動範圍 | ws.move_range("A1:B10", rows=2, cols=1) |
| 複製工作表 | wb.copy_worksheet(ws) |
| 資料驗證 | from openpyxl.worksheet.datavalidation import DataValidation |
| 條件格式 | from openpyxl.formatting.rule import CellIsRule |
| 圖表 | from openpyxl.chart import BarChart, Reference |