Python中openpyxl模塊如何玩轉Excel

# Python中openpyxl模塊如何玩轉Excel

## 一、前言：為什么選擇openpyxl？

在數據分析和辦公自動化領域，Excel文件處理是常見需求。Python作為最受歡迎的編程語言之一，提供了多個操作Excel的模塊（如xlrd/xlwt、pandas等），而**openpyxl**因其獨特優勢脫穎而出：

- **全面支持.xlsx格式**：完美兼容Excel 2010+文件格式
- **讀寫兼備**：既能讀取現有文件，也能創建新工作簿
- **豐富的API**：支持單元格格式、公式、圖表等高級操作
- **純Python實現**：無需安裝Excel軟件
- **活躍社區**：持續維護更新（最新版本3.1.2）

本文將帶你從安裝配置到實戰應用，全面掌握這個強大的工具。

---

## 二、環境配置與基礎操作

### 1. 安裝openpyxl
```bash
pip install openpyxl
# 如需處理圖片安裝額外依賴
pip install pillow

2. 創建第一個工作簿

from openpyxl import Workbook

# 創建空白工作簿
wb = Workbook()
# 獲取活動工作表
ws = wb.active
# 寫入數據
ws['A1'] = "Hello"
ws['B1'] = "World"
# 保存文件
wb.save("first_excel.xlsx")

3. 核心對象模型

• Workbook：整個Excel文件對象
• Worksheet：單個工作表
• Cell：單元格對象
• Column/Row：列和行對象

三、數據讀寫進階技巧

1. 多種數據寫入方式

# 單單元格寫入
ws['A2'] = 42
ws.cell(row=3, column=1, value=3.14)

# 批量寫入（效率更高）
data = [
    ["ID", "Name", "Score"],
    [1, "Alice", 95],
    [2, "Bob", 88]
]
for row in data:
    ws.append(row)

# 使用公式
ws['D2'] = "=SUM(B2:C2)"

2. 數據讀取全攻略

# 按坐標讀取
print(ws['A1'].value)

# 遍歷所有數據
for row in ws.iter_rows(values_only=True):
    print(row)

# 按范圍讀取
for row in ws['A1:C3']:
    for cell in row:
        print(cell.value)

# 獲取最大行列數
print(f"最大行：{ws.max_row}, 最大列：{ws.max_column}")

3. 特殊數據類型處理

# 日期處理
from datetime import datetime
ws['A4'] = datetime.now()

# 布爾值
ws['B4'] = True

# 保存后讀取時會自動轉換類型

四、樣式定制與格式化

1. 字體與顏色設置

from openpyxl.styles import Font, Color

bold_font = Font(name='微軟雅黑', size=14, bold=True, color='FF0000')
ws['A1'].font = bold_font

2. 單元格邊框與填充

from openpyxl.styles import Border, Side, PatternFill

thin_border = Border(left=Side(style='thin'), 
                    right=Side(style='thin'),
                    top=Side(style='thin'),
                    bottom=Side(style='thin'))

yellow_fill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')

ws['B2'].border = thin_border
ws['B2'].fill = yellow_fill

3. 對齊與數字格式

from openpyxl.styles import Alignment, numbers

# 居中對齊
center_alignment = Alignment(horizontal='center', vertical='center')
ws['C3'].alignment = center_alignment

# 貨幣格式
ws['D2'].number_format = numbers.FORMAT_CURRENCY_USD_SIMPLE

五、高級功能探索

1. 工作表操作

# 創建/刪除工作表
wb.create_sheet("月度報表", 0)  # 插入到首位
del wb["Sheet"]  # 刪除默認表

# 工作表復制
source = wb["月度報表"]
target = wb.copy_worksheet(source)
target.title = "年度匯總"

2. 公式與數據驗證

# 添加數據驗證
from openpyxl.worksheet.datavalidation import DataValidation

dv = DataValidation(type="list", formula1='"男,女"')
dv.add('B1:B10')
ws.add_data_validation(dv)

# 使用數組公式
ws['E1'] = '=SUM(IF(A1:A10>5,1,0))'

3. 圖表制作

from openpyxl.chart import BarChart, Reference

chart = BarChart()
data = Reference(ws, min_col=2, min_row=1, max_col=3, max_row=4)
categories = Reference(ws, min_col=1, min_row=2, max_row=4)
chart.add_data(data, titles_from_data=True)
chart.set_categories(categories)
ws.add_chart(chart, "E5")

六、性能優化技巧

1. 只讀模式（處理大文件）

from openpyxl import load_workbook

wb = load_workbook('large_file.xlsx', read_only=True)
# 處理完成后需手動關閉
wb.close()

2. 只寫模式

wb = Workbook(write_only=True)
ws = wb.create_sheet()
# 只能使用append()添加整行數據
for row in range(10000):
    ws.append([row, row*2])

3. 內存優化建議

• 避免頻繁保存操作
• 批量處理數據而非單個單元格
• 及時關閉不再使用的工作簿

七、實戰案例：銷售報表自動化

1. 場景需求

• 從數據庫導出銷售數據
• 生成帶格式的月報表
• 自動添加圖表和匯總公式

2. 完整實現代碼

import sqlite3
from openpyxl import Workbook
from openpyxl.chart import PieChart, Reference
from openpyxl.styles import Font, Alignment

def generate_sales_report():
    # 連接數據庫
    conn = sqlite3.connect('sales.db')
    cursor = conn.cursor()
    
    # 獲取數據
    cursor.execute("SELECT product, amount FROM sales WHERE strftime('%Y-%m', date) = '2023-05'")
    data = cursor.fetchall()
    
    # 創建工作簿
    wb = Workbook()
    ws = wb.active
    ws.title = "五月銷售"
    
    # 寫入標題
    ws['A1'] = "五月產品銷售報表"
    ws['A1'].font = Font(size=16, bold=True)
    ws.merge_cells('A1:B1')
    
    # 寫入數據
    ws.append(["產品名稱", "銷售額(元)"])
    for row in data:
        ws.append(row)
    
    # 添加匯總公式
    last_row = len(data) + 2
    ws[f'A{last_row}'] = "總計"
    ws[f'B{last_row}'] = f"=SUM(B2:B{last_row-1})"
    
    # 創建餅圖
    chart = PieChart()
    labels = Reference(ws, min_col=1, min_row=2, max_row=last_row-1)
    values = Reference(ws, min_col=2, min_row=2, max_row=last_row-1)
    chart.add_data(values, titles_from_data=False)
    chart.set_categories(labels)
    chart.title = "五月銷售占比"
    ws.add_chart(chart, "D2")
    
    # 保存文件
    wb.save("五月銷售報表.xlsx")
    conn.close()

generate_sales_report()

八、常見問題解答

Q1: 如何處理加密的Excel文件？

A: openpyxl不支持直接讀取加密文件，建議先用其他工具解密或使用msoffcrypto-tool庫解密后再處理。

Q2: 為什么修改后的文件變大了？

A: openpyxl會保留修改歷史，可通過optimized_write=True或手動清理冗余數據減少體積。

Q3: 如何設置自動列寬？

from openpyxl.utils import get_column_letter

for col in ws.columns:
    max_length = 0
    column = col[0].column_letter
    for cell in col:
        try:
            if len(str(cell.value)) > max_length:
                max_length = len(str(cell.value))
        except:
            pass
    adjusted_width = (max_length + 2) * 1.2
    ws.column_dimensions[column].width = adjusted_width

九、總結與擴展學習

通過本文，你已經掌握了： - openpyxl的核心API使用方法 - Excel樣式定制技巧 - 大數據量處理的優化方案 - 完整的自動化報表生成流程

進一步學習建議： 1. 官方文檔：https://openpyxl.readthedocs.io 2. 結合Pandas進行復雜數據分析 3. 使用win32com實現openpyxl不支持的Excel功能 4. 探索自動化報告郵件發送（搭配smtplib）

“自動化不是替代人類，而是讓我們有更多時間解決更有價值的問題。” —— 佚名

現在就開始用openpyxl解放你的雙手，讓重復的Excel操作成為歷史吧！ “`

注：本文實際約3800字（含代碼），可根據需要調整代碼示例的復雜度或增加更多實戰案例來精確控制字數。