一、Excel處理在數據分析中的重要性

在現代數據分析工作中，Excel文件幾乎無處不在。作為最廣泛使用的電子表格工具，Excel在企業數據存儲、報表生成和初步數據分析中扮演著重要角色。根據最新調查，超過80%的企業在日常運營中使用Excel作為主要的數據管理工具之一。因此，掌握Python處理Excel文件的能力對于數據分析師、財務人員和科研工作者來說至關重要。

Python提供了多種處理Excel文件的庫，其中最常用的是openpyxl和pandas。openpyxl專注于直接操作Excel文件（特別是.xlsx格式），提供了單元格級別的精細控制；而pandas則是一個強大的數據分析庫，可以方便地將Excel數據讀入DataFrame進行復雜的數據處理和分析。

本文將深入探討這兩個庫的使用方法，從基礎操作到高級技巧，幫助讀者全面掌握Python處理Excel文件的能力。我們將通過大量實際示例展示如何結合使用這兩個庫來完成各種復雜的Excel處理任務。

二、openpyxl基礎與核心功能

2.1 openpyxl簡介與安裝

openpyxl是一個專門用于讀寫Excel 2010 xlsx/xlsm/xltx/xltm文件的Python庫。它不依賴于Excel軟件本身，可以直接操作Excel文件，非常適合自動化處理Excel報表。

安裝openpyxl非常簡單，使用pip命令即可：

pip install openpyxl

如果需要處理圖表，還需要安裝額外的依賴：

pip install openpyxl[charts]

2.2 工作簿與工作表的基本操作

創建新工作簿

from openpyxl import Workbook# 創建一個新工作簿
wb = Workbook()# 獲取活動的工作表
ws = wb.active# 設置工作表標題
ws.title = "第一個工作表"# 創建新的工作表
ws1 = wb.create_sheet("第二個工作表")  # 默認插入到最后
ws2 = wb.create_sheet("第三個工作表", 0)  # 插入到第一個位置# 保存工作簿
wb.save("新工作簿.xlsx")

打開已有工作簿

from openpyxl import load_workbook# 打開一個已存在的工作簿
wb = load_workbook('example.xlsx')# 獲取所有工作表名稱
print(wb.sheetnames)# 通過名稱獲取工作表
ws = wb['Sheet1']# 檢查工作表是否存在
if 'Sheet1' in wb.sheetnames:print("Sheet1存在")

工作表操作

# 復制工作表
source = wb['Sheet1']
target = wb.copy_worksheet(source)
target.title = "Sheet1的副本"# 刪除工作表
del wb['Sheet1的副本']# 遍歷所有工作表
for sheet in wb:print(sheet.title)

2.3 單元格操作詳解

基本單元格操作

# 獲取單元格
cell = ws['A1']# 寫入值
ws['A1'] = "Hello"
ws['B1'] = "World"# 使用單元格坐標
ws.cell(row=1, column=3, value="!")# 讀取值
print(ws['A1'].value)  # 輸出: Hello# 單元格坐標
print(cell.row, cell.column)  # 輸出: 1, 1
print(cell.coordinate)  # 輸出: A1

批量操作單元格

# 批量寫入數據
for row in range(1, 6):for col in range(1, 5):ws.cell(row=row, column=col, value=f"R{row}C{col}")# 批量讀取數據
for row in ws.iter_rows(min_row=1, max_row=3, min_col=1, max_col=3):for cell in row:print(cell.value, end="\t")print()# 使用values_only參數只獲取值
for row in ws.iter_rows(values_only=True):print(row)

特殊單元格操作

# 合并單元格
ws.merge_cells('A1:D1')
ws['A1'] = "合并的標題"# 取消合并
ws.unmerge_cells('A1:D1')# 設置行高和列寬
ws.row_dimensions[1].height = 30
ws.column_dimensions['A'].width = 20# 隱藏行列
ws.column_dimensions['B'].hidden = True
ws.row_dimensions[2].hidden = True

2.4 樣式與格式設置

字體樣式

from openpyxl.styles import Font, Color# 設置字體
bold_font = Font(name='Arial', size=12, bold=True, italic=False, color='FF0000')
ws['A1'].font = bold_font# 修改現有單元格字體
for row in ws.iter_rows(min_row=2, max_row=5):for cell in row:cell.font = Font(size=10, color=Color(rgb='0000FF'))

對齊方式

from openpyxl.styles import Alignment# 設置對齊方式
center_aligned = Alignment(horizontal='center', vertical='center', wrap_text=True)
ws['A1'].alignment = center_aligned# 應用到范圍
for row in ws.iter_rows(min_row=1, max_row=10):for cell in row:cell.alignment = Alignment(horizontal='center')

邊框設置

from openpyxl.styles import Border, Side# 定義邊框樣式
thin_border = Border(left=Side(style='thin'), right=Side(style='thin'),top=Side(style='thin'),bottom=Side(style='thin'))# 應用邊框
for row in ws.iter_rows(min_row=1, max_row=5):for cell in row:cell.border = thin_border

填充顏色

from openpyxl.styles import PatternFill, GradientFill# 純色填充
yellow_fill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')
ws['A1'].fill = yellow_fill# 漸變填充
gradient_fill = GradientFill(stop=('FFFFFF', '0000FF'))
ws['B1'].fill = gradient_fill

數字格式

from openpyxl.styles import numbers# 設置數字格式
ws['C1'].value = 3.1415926
ws['C1'].number_format = numbers.FORMAT_NUMBER_00  # 顯示兩位小數# 自定義格式
ws['D1'].value = 0.85
ws['D1'].number_format = '0.00%'  # 顯示為百分比

2.5 公式與計算

# 寫入公式
ws['E1'] = '=SUM(A1:D1)'
ws['E2'] = '=AVERAGE(A2:D2)'
ws['E3'] = '=IF(A3>B3, "A大", "B大")'# 讀取公式
print(ws['E1'].value)  # 輸出: =SUM(A1:D1)# 計算數據
ws['F1'] = '結果'
ws['F2'] = '=E2*100'

2.6 圖表與圖像操作

創建圖表

from openpyxl.chart import BarChart, Reference# 準備數據
for i in range(1, 6):ws[f'A{i}'] = iws[f'B{i}'] = i*i# 創建柱狀圖
chart = BarChart()
data = Reference(ws, min_col=2, min_row=1, max_row=5)
categories = Reference(ws, min_col=1, min_row=2, max_row=5)
chart.add_data(data, titles_from_data=True)
chart.set_categories(categories)# 添加圖表到工作表
ws.add_chart(chart, "D1")

插入圖像

from openpyxl.drawing.image import Image# 插入圖像
img = Image('logo.png')
ws.add_image(img, 'A10')# 調整圖像大小
img.width = 100
img.height = 100

2.7 高級功能

數據驗證

from openpyxl.worksheet.datavalidation import DataValidation# 創建數據驗證
dv = DataValidation(type="list", formula1='"男,女"', allow_blank=True)
dv.add('A1:A10')  # 應用到A1:A10范圍
ws.add_data_validation(dv)# 數字范圍驗證
dv_num = DataValidation(type="whole", operator="between", formula1=1, formula2=100)
dv_num.error = "輸入必須在1到100之間"
dv_num.add('B1:B10')
ws.add_data_validation(dv_num)

條件格式

from openpyxl.formatting.rule import ColorScaleRule, FormulaRule# 色階條件格式
color_scale_rule = ColorScaleRule(start_type='min', start_color='FF0000',mid_type='percentile', mid_value=50, mid_color='FFFF00',end_type='max', end_color='00FF00')
ws.conditional_formatting.add('C1:C10', color_scale_rule)# 公式條件格式
formula_rule = FormulaRule(formula=['ISBLANK(C1)'], stopIfTrue=True, font=Font(color='FF0000'))
ws.conditional_formatting.add('C1:C10', formula_rule)

保護工作表

# 保護工作表
ws.protection.sheet = True
ws.protection.password = 'password'
ws.protection.enable()# 解鎖特定單元格
for row in ws.iter_rows(min_row=1, max_row=5):for cell in row:cell.protection = Protection(locked=False)

凍結窗格

# 凍結第一行和第一列
ws.freeze_panes = 'B2'# 解凍
ws.freeze_panes = None

三、pandas基礎與核心功能

3.1 pandas簡介與安裝

pandas是一個強大的開源數據分析和操作庫，提供了高性能、易用的數據結構和數據分析工具。它特別適合處理表格數據（如Excel文件）和時間序列數據。

安裝pandas及其依賴：

pip install pandas openpyxl xlrd

注意：xlrd庫用于讀取舊版Excel文件(.xls)，而openpyxl則用于處理.xlsx文件。

3.2 DataFrame基礎

DataFrame是pandas的核心數據結構，可以看作是一個二維表格，類似于Excel工作表。

創建DataFrame

import pandas as pd# 從字典創建
data = {'姓名': ['張三', '李四', '王五'],'年齡': [25, 30, 35],'城市': ['北京', '上海', '廣州']
}
df = pd.DataFrame(data)# 從列表創建
data = [{'姓名': '張三', '年齡': 25, '城市': '北京'},{'姓名': '李四', '年齡': 30, '城市': '上海'},{'姓名': '王五', '年齡': 35, '城市': '廣州'}
]
df = pd.DataFrame(data)# 顯示DataFrame
print(df)

基本屬性

# 查看前幾行
print(df.head(2))# 查看后幾行
print(df.tail(1))# 查看形狀
print(df.shape)  # 輸出: (3, 3)# 查看列名
print(df.columns)  # 輸出: Index(['姓名', '年齡', '城市'], dtype='object')# 查看索引
print(df.index)  # 輸出: RangeIndex(start=0, stop=3, step=1)# 查看數據類型
print(df.dtypes)

數據選擇

# 選擇列
print(df['姓名'])  # 選擇單列
print(df[['姓名', '年齡']])  # 選擇多列# 選擇行
print(df.iloc[0])  # 通過位置選擇
print(df.loc[0])  # 通過索引選擇
print(df[1:3])  # 切片選擇# 條件選擇
print(df[df['年齡'] > 28])
print(df[(df['年齡'] > 25) & (df['城市'] == '上海')])

數據修改

# 添加列
df['性別'] = ['男', '女', '男']# 修改值
df.loc[0, '年齡'] = 26
df['年齡'] = df['年齡'] + 1# 刪除列
df = df.drop('性別', axis=1)# 刪除行
df = df.drop(0, axis=0)

3.3 數據導入與導出

讀取Excel文件

# 讀取整個Excel文件
df = pd.read_excel('data.xlsx', sheet_name='Sheet1')# 讀取指定范圍
df = pd.read_excel('data.xlsx', sheet_name='Sheet1', usecols='A:C', nrows=10)# 讀取多個工作表
with pd.ExcelFile('data.xlsx') as xls:df1 = pd.read_excel(xls, 'Sheet1')df2 = pd.read_excel(xls, 'Sheet2')# 處理缺失值
df = pd.read_excel('data.xlsx', na_values=['NA', 'N/A', '缺失'])

寫入Excel文件

# 寫入單個DataFrame
df.to_excel('output.xlsx', sheet_name='數據', index=False)# 寫入多個DataFrame
with pd.ExcelWriter('output.xlsx') as writer:df1.to_excel(writer, sheet_name='Sheet1')df2.to_excel(writer, sheet_name='Sheet2')# 追加模式寫入
with pd.ExcelWriter('output.xlsx', mode='a') as writer:df3.to_excel(writer, sheet_name='Sheet3')# 設置格式
df.to_excel('output.xlsx', sheet_name='數據',index=False,float_format="%.2f",  # 浮點數格式freeze_panes=(1, 0))  # 凍結首行

其他格式支持

# CSV文件
df.to_csv('data.csv', index=False)
df = pd.read_csv('data.csv')# JSON
df.to_json('data.json', orient='records')
df = pd.read_json('data.json')# SQL數據庫
from sqlalchemy import create_engine
engine = create_engine('sqlite:///data.db')
df.to_sql('table_name', engine, if_exists='replace')
df = pd.read_sql('SELECT * FROM table_name', engine)

3.4 數據清洗與預處理

處理缺失值

# 檢測缺失值
print(df.isnull().sum())# 刪除缺失值
df_cleaned = df.dropna()  # 刪除任何包含缺失值的行
df_cleaned = df.dropna(subset=['年齡'])  # 只刪除年齡列有缺失的行# 填充缺失值
df_filled = df.fillna(0)  # 用0填充
df_filled = df.fillna(df.mean())  # 用均值填充數值列
df_filled = df.fillna(method='ffill')  # 用前一個值填充

處理重復值

# 檢測重復行
print(df.duplicated().sum())# 刪除重復行
df_unique = df.drop_duplicates()# 基于某些列刪除重復
df_unique = df.drop_duplicates(subset=['姓名', '城市'])

數據類型轉換

# 查看數據類型
print(df.dtypes)# 轉換數據類型
df['年齡'] = df['年齡'].astype('float64')
df['日期'] = pd.to_datetime(df['日期'])# 分類數據
df['城市'] = df['城市'].astype('category')

字符串操作

# 字符串方法
df['姓名'] = df['姓名'].str.upper()  # 轉為大寫
df['城市'] = df['城市'].str.replace('京', '都')  # 替換# 提取信息
df['姓氏'] = df['姓名'].str[0]  # 提取第一個字符
df['名字'] = df['姓名'].str[1:]  # 提取第二個字符之后# 拆分列
df[['姓', '名']] = df['姓名'].str.split(expand=True)

日期處理

# 解析日期
df['日期'] = pd.to_datetime(df['日期'], format='%Y-%m-%d')# 提取日期部分
df['年'] = df['日期'].dt.year
df['月'] = df['日期'].dt.month
df['日'] = df['日期'].dt.day
df['星期'] = df['日期'].dt.day_name()# 日期運算
df['年齡天數'] = (pd.to_datetime('today') - df['出生日期']).dt.days
df['年齡'] = df['年齡天數'] // 365

3.5 數據轉換與計算

應用函數

# 應用簡單函數
df['年齡加10'] = df['年齡'].apply(lambda x: x + 10)# 應用復雜函數
def age_group(age):if age < 20: return '少年'elif age < 40: return '青年'else: return '中年'df['年齡段'] = df['年齡'].apply(age_group)# 向量化操作
df['BMI'] = df['體重'] / (df['身高']/100)**2

分組聚合

# 基本分組
grouped = df.groupby('城市')# 聚合函數
print(grouped['年齡'].mean())  # 每個城市的平均年齡
print(grouped.agg({'年齡': ['mean', 'min', 'max'], '收入': 'sum'}))# 多級分組
grouped = df.groupby(['城市', '性別'])
print(grouped['年齡'].mean())

數據透視表

# 簡單透視表
pivot = pd.pivot_table(df, values='年齡', index='城市', aggfunc='mean')# 復雜透視表
pivot = pd.pivot_table(df, values=['年齡', '收入'],index=['城市', '性別'],columns=['教育程度'],aggfunc={'年齡': 'mean', '收入': ['sum', 'count']},fill_value=0,margins=True)

合并與連接

# 合并兩個DataFrame
df1 = pd.DataFrame({'key': ['A', 'B', 'C'], 'value': [1, 2, 3]})
df2 = pd.DataFrame({'key': ['A', 'B', 'D'], 'value': [4, 5, 6]})# 內連接
pd.merge(df1, df2, on='key', how='inner')# 左連接
pd.merge(df1, df2, on='key', how='left')# 外連接
pd.merge(df1, df2, on='key', how='outer')# 縱向合并
pd.concat([df1, df2], axis=0)

3.6 高級數據分析功能

時間序列分析

# 創建時間序列
date_rng = pd.date_range(start='1/1/2020', end='1/10/2020', freq='D')
df = pd.DataFrame(date_rng, columns=['date'])
df['data'] = np.random.randint(0,100,size=(len(date_rng)))# 設置為索引
df = df.set_index('date')# 重采樣
df.resample('W').mean()  # 按周平均
df.resample('M').sum()  # 按月求和# 滾動窗口
df.rolling(window=3).mean()  # 3天移動平均

統計函數

# 描述性統計
print(df.describe())# 相關性
print(df.corr())# 協方差
print(df.cov())# 唯一值計數
print(df['城市'].value_counts())# 交叉表
pd.crosstab(df['城市'], df['性別'])

可視化

import matplotlib.pyplot as plt# 線圖
df.plot.line()# 柱狀圖
df.plot.bar()# 直方圖
df['年齡'].plot.hist(bins=20)# 箱線圖
df.plot.box()# 散點圖
df.plot.scatter(x='年齡', y='收入')plt.show()

四、openpyxl與pandas的協同使用

4.1 結合使用的優勢與場景

openpyxl和pandas各有優勢，在實際工作中結合使用可以發揮更大威力：

1. 數據流轉：pandas適合數據處理和分析，openpyxl適合精細控制Excel格式，兩者結合可以實現"pandas處理數據→openpyxl調整格式"的完整流程。

2. 復雜報表：對于需要復雜格式的報表，可以先用pandas生成數據，再用openpyxl添加圖表、條件格式等高級功能。

3. 模板填充：使用Excel文件作為模板，pandas填充數據，openpyxl保持原有格式和公式。

4. 大數據處理：當Excel文件很大時，可以先用pandas高效讀取和處理數據，再用openpyxl進行必要的格式調整。

4.2 pandas DataFrame與openpyxl的轉換

DataFrame寫入Excel并保持格式

from openpyxl import load_workbook
from openpyxl.utils.dataframe import dataframe_to_rows# 創建DataFrame
df = pd.DataFrame({'產品': ['A', 'B', 'C', 'D'],'銷量': [120, 150, 90, 200],'單價': [25.5, 32.0, 18.0, 40.5]
})# 加載現有工作簿或創建新工作簿
try:wb = load_workbook('report.xlsx')
except FileNotFoundError:wb = Workbook()# 選擇或創建工作表
if '銷售報告' in wb.sheetnames:ws = wb['銷售報告']
else:ws = wb.create_sheet('銷售報告')# 清空現有內容
ws.delete_rows(1, ws.max_row)# 寫入DataFrame數據
for r in dataframe_to_rows(df, index=False, header=True):ws.append(r)# 添加公式
ws['E1'] = '銷售額'
for row in range(2, ws.max_row + 1):ws[f'E{row}'] = f'=B{row}*C{row}'# 設置格式
header_font = Font(bold=True, color='FFFFFF')
header_fill = PatternFill(start_color='4F81BD', end_color='4F81BD', fill_type='solid')for cell in ws[1]:cell.font = header_fontcell.fill = header_fill# 保存工作簿
wb.save('report.xlsx')

從openpyxl讀取數據到DataFrame

from openpyxl import load_workbook# 加載工作簿
wb = load_workbook('data.xlsx')
ws = wb.active# 將工作表數據轉換為列表
data = []
for row in ws.iter_rows(values_only=True):data.append(row)# 轉換為DataFrame
df = pd.DataFrame(data[1:], columns=data[0])# 處理數據
df['日期'] = pd.to_datetime(df['日期'])
df['銷售額'] = df['數量'] * df['單價']print(df.head())

4.3 復雜報表生成案例

import pandas as pd
from openpyxl import Workbook
from openpyxl.styles import Font, Alignment, Border, Side, PatternFill
from openpyxl.chart import BarChart, Reference
from openpyxl.drawing.image import Image
from datetime import datetime# 1. 準備數據
sales_data = {'月份': ['1月', '2月', '3月', '4月', '5月', '6月'],'產品A': [1200, 1500, 1800, 2100, 2400, 2700],'產品B': [800, 950, 1100, 1250, 1400, 1550],'產品C': [500, 600, 700, 800, 900, 1000]
}
df = pd.DataFrame(sales_data)# 2. 創建Excel工作簿
wb = Workbook()
ws = wb.active
ws.title = "銷售報告"# 3. 寫入標題
ws['A1'] = "2023年上半年銷售報告"
ws['A1'].font = Font(size=16, bold=True, name='微軟雅黑')
ws.merge_cells('A1:D1')# 4. 寫入數據
# 寫入列標題
columns = list(df.columns)
for col_num, column_title in enumerate(columns, 1):cell = ws.cell(row=3, column=col_num, value=column_title)cell.font = Font(bold=True)cell.alignment = Alignment(horizontal='center')# 寫入數據
for row_num, row_data in enumerate(df.values, 4):for col_num, cell_value in enumerate(row_data, 1):ws.cell(row=row_num, column=col_num, value=cell_value)# 5. 添加匯總行
last_row = ws.max_row + 1
ws.cell(row=last_row, column=1, value="總計").font = Font(bold=True)
for col_num in range(2, 5):col_letter = chr(64 + col_num)ws.cell(row=last_row, column=col_num, value=f"=SUM({col_letter}4:{col_letter}{last_row-1})")# 6. 設置格式
# 設置邊框
thin_border = Border(left=Side(style='thin'), right=Side(style='thin'),top=Side(style='thin'),bottom=Side(style='thin'))for row in ws.iter_rows(min_row=3, max_row=last_row, min_col=1, max_col=4):for cell in row:cell.border = thin_borderif cell.row > 3 and cell.column > 1:  # 數據單元格cell.number_format = '#,##0'# 設置對齊
for row in ws.iter_rows(min_row=3, max_row=last_row, min_col=1, max_col=4):for cell in row:cell.alignment = Alignment(horizontal='center')# 7. 創建圖表
chart = BarChart()
chart.type = "col"
chart.style = 10
chart.title = "產品銷售趨勢"
chart.y_axis.title = "銷售額"
chart.x_axis.title = "月份"data = Reference(ws, min_col=2, max_col=4, min_row=3, max_row=last_row-1)
categories = Reference(ws, min_col=1, min_row=4, max_row=last_row-1)
chart.add_data(data, titles_from_data=True)
chart.set_categories(categories)ws.add_chart(chart, "F3")# 8. 添加頁腳
footer_row = last_row + 2
ws.cell(row=footer_row, column=1, value=f"報告生成時間: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}")# 9. 調整列寬
ws.column_dimensions['A'].width = 12
ws.column_dimensions['B'].width = 12
ws.column_dimensions['C'].width = 12
ws.column_dimensions['D'].width = 12# 10. 保存文件
filename = f"銷售報告_{datetime.now().strftime('%Y%m%d')}.xlsx"
wb.save(filename)
print(f"報表已生成: {filename}")

4.4 模板填充技術

from openpyxl import load_workbook
from openpyxl.utils.dataframe import dataframe_to_rows
import pandas as pd# 1. 加載模板文件
template_path = 'report_template.xlsx'
wb = load_workbook(template_path)
ws = wb['Data']# 2. 準備數據
data = {'Region': ['North', 'South', 'East', 'West'],'Q1': [1200, 1500, 1800, 900],'Q2': [1300, 1600, 1900, 950],'Q3': [1400, 1700, 2000, 1000],'Q4': [1500, 1800, 2100, 1050]
}
df = pd.DataFrame(data)# 3. 清空模板中的數據區域 (保留格式)
for row in ws.iter_rows(min_row=3, max_row=100, min_col=1, max_col=6):for cell in row:cell.value = None# 4. 寫入新數據
for r_idx, row in enumerate(dataframe_to_rows(df, index=False, header=False), 3):for c_idx, value in enumerate(row, 1):ws.cell(row=r_idx, column=c_idx, value=value)# 5. 更新公式 (假設模板中已有公式)
last_data_row = 3 + len(df) - 1
for row in range(3, last_data_row + 1):ws[f'F{row}'] = f'=SUM(B{row}:E{row})'# 6. 更新匯總公式
ws['B20'] = f'=SUM(B3:B{last_data_row})'
ws['C20'] = f'=SUM(C3:C{last_data_row})'
ws['D20'] = f'=SUM(D3:D{last_data_row})'
ws['E20'] = f'=SUM(E3:E{last_data_row})'
ws['F20'] = f'=SUM(F3:F{last_data_row})'# 7. 更新報告日期
ws['A1'] = f"銷售報告 - {pd.Timestamp.today().strftime('%Y-%m-%d')}"# 8. 保存新文件
output_path = 'quarterly_report.xlsx'
wb.save(output_path)
print(f"報告已生成: {output_path}")

4.5 性能優化技巧

當處理大型Excel文件時，性能可能成為問題。以下是一些優化技巧：

1. 只讀模式：如果只需要讀取數據而不修改文件，使用只讀模式可以顯著提高速度。

wb = load_workbook('large_file.xlsx', read_only=True)

2. 只寫模式：如果只需要寫入大量數據而不讀取現有內容，使用只寫模式。

wb = Workbook(write_only=True)
ws = wb.create_sheet()
for row in data:ws.append(row)

3. 批量操作：盡量減少單個單元格操作，使用批量寫入方法。

4. 禁用計算：在寫入大量公式時，臨時禁用自動計算。

wb = load_workbook('file.xlsx', data_only=False)
wb.calculation = False
# ... 寫入公式 ...
wb.calculation = True
wb.save('file.xlsx')

5. 使用pandas處理大數據：對于數據操作，優先使用pandas，它比openpyxl的單元格操作高效得多。

6. 內存優化：處理完數據后及時刪除不需要的變量，特別是大型DataFrame和工作簿對象。

del large_df
del wb

五、實戰應用案例

5.1 財務報表自動化

import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import Font, Alignment, numbers
from datetime import datetimedef generate_financial_report(input_path, output_path):# 1. 使用pandas讀取和處理數據df = pd.read_excel(input_path, sheet_name='Transactions')# 數據清洗df = df.dropna(subset=['Amount'])df['Date'] = pd.to_datetime(df['Date'])df['Month'] = df['Date'].dt.to_period('M')# 分類匯總income = df[df['Type'] == 'Income'].groupby('Month')['Amount'].sum()expense = df[df['Type'] == 'Expense'].groupby('Month')['Amount'].sum()profit = income - expense# 2. 準備報表數據report_data = pd.DataFrame({'Month': income.index.astype(str),'Income': income.values,'Expense': expense.values,'Profit': profit.values})# 3. 加載模板文件wb = load_workbook('financial_report_template.xlsx')ws = wb['Report']# 4. 清空舊數據 (保留格式)for row in ws.iter_rows(min_row=5, max_row=100, min_col=1, max_col=4):for cell in row:cell.value = None# 5. 寫入新數據for i, row in report_data.iterrows():ws.cell(row=5+i, column=1, value=row['Month'])ws.cell(row=5+i, column=2, value=row['Income'])ws.cell(row=5+i, column=3, value=row['Expense'])ws.cell(row=5+i, column=4, value=row['Profit'])# 6. 設置數字格式for row in ws.iter_rows(min_row=5, max_row=5+len(report_data), min_col=2, max_col=4):for cell in row:cell.number_format = numbers.FORMAT_CURRENCY_USD_SIMPLE# 7. 更新匯總信息last_row = 5 + len(report_data) - 1ws['B1'] = f"財務報告 - {datetime.now().strftime('%Y-%m-%d')}"ws['B2'] = f"數據期間: {report_data['Month'].iloc[0]} 至 {report_data['Month'].iloc[-1]}"ws[f'B{last_row+2}'] = "總計:"ws[f'C{last_row+2}'] = f"=SUM(C5:C{last_row})"ws[f'D{last_row+2}'] = f"=SUM(D5:D{last_row})"ws[f'E{last_row+2}'] = f"=SUM(E5:E{last_row})"# 8. 設置匯總行格式for cell in ws[f'B{last_row+2}':f'E{last_row+2}'][0]:cell.font = Font(bold=True)if cell.column in [3,4,5]:cell.number_format = numbers.FORMAT_CURRENCY_USD_SIMPLE# 9. 保存報告wb.save(output_path)print(f"財務報表已生成: {output_path}")# 使用示例
generate_financial_report('transactions.xlsx', 'financial_report_Q1.xlsx')