萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 網路程式設計 >> 程式語言綜合

Python openpyxl 操作 Excel 完整指南:建立、樣式、公式與效能優化

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

大資料優化:Write-Only 模式

預設模式將整個工作表載入記憶體,大資料會 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

最佳實踐

  • 大量資料**務必使用 Write-Only 模式**。
  • 樣式物件**重用**,不要在迴圈中重複建立。
  • 日期時間寫入用 Python `datetime` 物件,openpyxl 自動轉換數字格式。
  • 儲存前呼叫 `wb.save(path)`,大檔案可能需時,避免頻繁存檔。
  • 生產環境考慮加上 `try/finally` 確保關閉。
copyright © 萬盛學電腦網 all rights reserved