openpyxl基礎用法
openpyxl 是一個用于處理 Excel 文件（.xlsx/.xlsm）的 Python 庫，支持讀取、修改和創建 Excel 文檔。以下是其常見用法的詳細介紹：
一、基礎操作：打開與保存文件
from openpyxl import load_workbook
from openpyxl import Workbook# 1. 加載現有Excel文件
wb = load_workbook('example.xlsx')# 2. 創建新Excel文件
wb = Workbook()# 3. 保存文件
wb.save('new_example.xlsx')# 4. 獲取所有工作表名稱
sheet_names = wb.sheetnames
print(sheet_names) # 輸出：['Sheet1', 'Sheet2', ...]
二、工作表操作
# 1. 獲取默認工作表（通常是第一個）
sheet = wb.active# 2. 通過名稱獲取工作表
sheet = wb['Sheet1']# 3. 創建新工作表
new_sheet = wb.create_sheet('NewSheet') # 默認添加到末尾
new_sheet = wb.create_sheet('NewSheet', 0) # 指定位置為第一個# 4. 刪除工作表
wb.remove(wb['Sheet2'])# 5. 復制工作表
source_sheet = wb['Sheet1']
target_sheet = wb.copy_worksheet(source_sheet)
target_sheet.title = 'Sheet1_copy'# 6. 移動工作表位置
wb.move_sheet(wb['NewSheet'], 1) # 移動到第二個位置
三、單元格操作
# 1. 通過行列索引獲取單元格（從1開始計數）
cell = sheet.cell(row=1, column=1) # A1單元格
cell = sheet['A1'] # 等價寫法# 2. 寫入數據
sheet['A1'] = '姓名'
sheet.cell(row=2, column=1, value='張三')# 3. 讀取數據
value = sheet['A1'].value
print(value) # 輸出：姓名# 4. 批量操作行和列
# 獲取第一行
first_row = sheet[1]
for cell in first_row:print(cell.value)# 獲取第一列
first_col = sheet['A']
for cell in first_col:print(cell.value)# 獲取指定范圍的單元格（A1到C3）
for row in sheet['A1':'C3']:for cell in row:print(cell.value, end=' ')print()
四、格式設置
from openpyxl.styles import Font, Border, Side, Alignment, PatternFill# 1. 字體設置
font = Font(name='微軟雅黑', size=12, bold=True, italic=False, color='FF0000') # 紅色
sheet['A1'].font = font# 2. 邊框設置
border = Border(left=Side(style='thin'), right=Side(style='thin'),top=Side(style='thin'),bottom=Side(style='thin'))
sheet['A1'].border = border# 3. 對齊方式
alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)
sheet['A1'].alignment = alignment# 4. 填充顏色
fill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid') # 黃色
sheet['A1'].fill = fill# 5. 批量設置樣式
for row in sheet.iter_rows(min_row=1, max_row=1, min_col=1, max_col=3):for cell in row:cell.font = fontcell.border = bordercell.alignment = alignment
五、公式與函數
# 1. 寫入公式
sheet['B2'] = '=SUM(A2:A10)' # 求和公式
sheet['C2'] = '=AVERAGE(B2:B10)' # 平均值公式# 2. 日期函數（需要先設置單元格格式）
from openpyxl.utils import get_column_letter
sheet[f'{get_column_letter(3)}{2}'] = '=TODAY()'
sheet[f'{get_column_letter(3)}{2}'].number_format = 'yyyy-mm-dd'# 3. 計算工作表中的公式（需要手動觸發計算）
# openpyxl不自動計算公式，可通過第三方庫如pycel處理
六、數據處理與分析
# 1. 讀取數據到列表
data = []
for row in sheet.iter_rows(min_row=2, values_only=True): # 跳過表頭data.append(row)# 2. 寫入列表數據到工作表
for i, row_data in enumerate(data, start=2): # 從第二行開始寫入for j, value in enumerate(row_data, start=1):sheet.cell(row=i, column=j, value=value)# 3. 排序數據（按第二列升序）
sorted_data = sorted(data, key=lambda x: x[1])# 4. 篩選數據
filtered_data = [row for row in data if row[2] > 80] # 假設第三列是分數
七、合并與拆分單元格
# 1. 合并單元格（合并A1到C1）
sheet.merge_cells('A1:C1')
sheet['A1'] = '標題'
sheet['A1'].alignment = Alignment(horizontal='center') # 居中顯示# 2. 拆分單元格
sheet.unmerge_cells('A1:C1')# 3. 檢查單元格是否為合并單元格
from openpyxl.cell import MergedCell
if isinstance(sheet['A1'], MergedCell):print('A1是合并單元格')
八、圖表創建
from openpyxl.chart import BarChart, Reference, LineChart, PieChart# 1. 創建柱狀圖
chart = BarChart()
chart.title = '銷售數據柱狀圖'
chart.x_axis.title = '月份'
chart.y_axis.title = '銷售額'# 引用數據范圍
data = Reference(sheet, min_col=2, min_row=1, max_col=3, max_row=13)
categories = Reference(sheet, min_col=1, min_row=2, max_row=13)chart.add_data(data, titles_from_data=True)
chart.set_categories(categories)# 添加圖表到工作表
sheet.add_chart(chart, 'E2') # 放置在E2單元格位置# 2. 創建折線圖、餅圖等，邏輯類似
九、高級功能：樣式模板與自定義
# 1. 創建樣式模板
def get_title_style():return Font(name='黑體', size=16, bold=True, color='000000')def get_header_style():return Font(bold=True, color='FFFFFF'), PatternFill(start_color='4285F4', fill_type='solid')# 2. 使用模板
sheet['A1'].font = get_title_style()
header_font, header_fill = get_header_style()
for cell in sheet[2]: # 假設第二行為表頭cell.font = header_fontcell.fill = header_fill# 3. 自定義樣式類
from openpyxl.styles import NamedStyle# 創建自定義樣式
my_style = NamedStyle(name='my_style')
my_style.font = Font(name='Arial', size=10, italic=True)
my_style.alignment = Alignment(horizontal='right')
my_style.border = Border(top=Side(style='thin'))# 應用樣式
sheet['A1'].style = my_style
十、性能優化：處理大文件
# 1. 讀取模式（僅讀取，不加載全部數據）
from openpyxl import load_workbook
wb = load_workbook('large_file.xlsx', read_only=True)
sheet = wb.active# 逐行讀取，適合大文件
for row in sheet.iter_rows(values_only=True):print(row)# 2. 寫入模式（快速寫入，不保存全部對象）
from openpyxl import Workbook
wb = Workbook(write_only=True)
sheet = wb.create_sheet()# 批量寫入數據
for i in range(1000):sheet.append([f'data_{i}', i*2, i*3])wb.save('large_output.xlsx')
常用工具函數
from openpyxl.utils import get_column_letter, column_index_from_string# 1. 列索引與字母轉換
col_letter = get_column_letter(3) # 輸出: 'C'
col_index = column_index_from_string('D') # 輸出: 4# 2. 數據框轉Excel
import pandas as pd
df = pd.DataFrame({'姓名': ['張三', '李四'], '年齡': [25, 30]})# 方法1：使用pandas.to_excel
df.to_excel('dataframe.xlsx', index=False)# 方法2：使用openpyxl手動寫入
wb = Workbook()
sheet = wb.active
for r in dataframe_to_rows(df, index=False, header=True):sheet.append(r)
wb.save('dataframe2.xlsx')
注意事項
- 版本兼容性：openpyxl 不支持舊版 .xls 文件，需用?
xlrd/xlwt?處理。 - 公式計算：openpyxl 不自動計算公式，需手動處理或使用其他庫（如?
pycel）。 - 合并單元格：讀取合并單元格時，只有左上角單元格有值，其他為?
MergedCell。 - 性能考慮：處理大文件時使用?
read_only?或?write_only?模式，避免內存溢出。
通過以上用法，可滿足大多數 Excel 文檔的處理需求，從簡單的數據讀寫到復雜的格式設置和圖表生成。
openpyxl進階用法?
openpyxl 的進階用法，涵蓋高級樣式設置、圖表操作、數據透視表、性能優化等方面：
一、高級樣式設置
1. 自定義命名樣式
from openpyxl.styles import NamedStyle, Font, Border, Side, Alignment, PatternFill# 創建自定義樣式
header_style = NamedStyle(name='header')
header_style.font = Font(bold=True, color='FFFFFF')
header_style.fill = PatternFill(start_color='4285F4', fill_type='solid')
header_style.border = Border(bottom=Side(style='thin'))
header_style.alignment = Alignment(horizontal='center', vertical='center')# 應用樣式
sheet['A1'].style = header_style# 批量應用到表頭行
for cell in sheet[1]:cell.style = header_style
2. 條件格式
from openpyxl.formatting.rule import CellIsRule# 為A1:A10設置條件格式：值大于100時顯示為紅色
sheet.conditional_formatting.add('A1:A10',CellIsRule(operator='greaterThan', formula=['100'], font=Font(color='FF0000'))
)# 數據條樣式
from openpyxl.formatting.rule import DataBar, FormatObjectdata_bar = DataBar(cfvo=[FormatObject(type='min'), FormatObject(type='max')],color='638EC6', showValue=True, minLength=None, maxLength=None)
sheet.conditional_formatting.add('B1:B10', data_bar)
二、復雜圖表操作
1. 創建組合圖表
from openpyxl.chart import BarChart, LineChart, Reference# 準備數據
sheet.append(['月份', '銷售額', '增長率'])
for i in range(1, 13):sheet.append([f'{i}月', i*1000, i*5])# 創建柱狀圖（銷售額）
bar_chart = BarChart()
bar_chart.title = '銷售數據'
bar_chart.x_axis.title = '月份'
bar_chart.y_axis.title = '銷售額'# 創建折線圖（增長率）
line_chart = LineChart()
line_chart.y_axis.axId = 200 # 第二個Y軸
line_chart.add_data(Reference(sheet, min_col=3, min_row=1, max_row=13), titles_from_data=True)# 組合圖表
bar_chart += line_chart
sheet.add_chart(bar_chart, 'E2')
2. 圖表樣式自定義
# 設置圖表樣式
chart.style = 13 # 內置樣式編號# 設置線條顏色和寬度
line_chart.series[0].graphicalProperties.line.width = 25000 # 0.25mm# 添加數據標簽
for series in bar_chart.series:series.dLbls = Trueseries.dLbls.showVal = True
三、數據透視表
from openpyxl import Workbook
from openpyxl.utils import get_column_letter
from openpyxl.worksheet.pivot import PivotTable, PivotField# 準備數據
data = [['部門', '姓名', '銷售額', '季度'],['銷售部', '張三', 15000, 'Q1'],['銷售部', '李四', 18000, 'Q1'],['市場部', '王五', 12000, 'Q1'],['銷售部', '張三', 20000, 'Q2'],['市場部', '王五', 15000, 'Q2'],
]# 寫入數據
sheet = wb.active
for row in data:sheet.append(row)# 創建數據透視表
pivot = PivotTable(ref=f'A1:{get_column_letter(sheet.max_column)}{sheet.max_row}',pos='G2',name='銷售數據透視表'
)# 設置行、列和值字段
pivot.add_row('部門')
pivot.add_col('季度')
pivot.add_data('銷售額', '總和')# 添加到工作表
sheet.add_pivot(pivot)
四、Excel 函數與公式進階
1. R1C1 引用樣式
# 使用R1C1樣式設置公式（行和列均為數字索引）
sheet['A1'] = 10
sheet['B1'] = 20
sheet['C1'] = '=R[0]C[-2]+R[0]C[-1]' # 等價于=A1+B1
2. 動態數組公式（Excel 365+）
# FILTER函數示例
sheet['A1'] = '=FILTER(A1:C10, C1:C10>1000)' # 篩選C列大于1000的行
五、性能優化技巧
1. 處理大型文件（read_only 模式）
from openpyxl import load_workbook# 只讀模式（內存占用低，無法修改）
wb = load_workbook('large_file.xlsx', read_only=True)
sheet = wb.active# 逐行讀取（避免一次性加載全量數據）
for row in sheet.rows:for cell in row:print(cell.value, end='\t')print()
2. 快速寫入模式（write_only 模式）
from openpyxl import Workbook# 只寫模式（適合生成大數據量文件）
wb = Workbook(write_only=True)
sheet = wb.create_sheet()# 批量寫入數據
for i in range(100000):sheet.append([f'data_{i}', i*2, i*3])wb.save('large_output.xlsx')
3. 禁用不必要的功能
# 禁用自動計算公式和樣式解析
wb = load_workbook('example.xlsx', read_only=True, data_only=True, keep_vba=False)
六、合并與拆分工作簿
1. 合并多個 Excel 文件
import os
from openpyxl import Workbook, load_workbook# 創建新工作簿
merged_wb = Workbook()
merged_sheet = merged_wb.active# 遍歷目錄下所有Excel文件
for file in os.listdir('excel_files'):if file.endswith('.xlsx'):wb = load_workbook(os.path.join('excel_files', file))sheet = wb.active# 復制數據到新工作簿for row in sheet.rows:merged_sheet.append([cell.value for cell in row])merged_wb.save('merged.xlsx')
2. 拆分工作表
# 按列值拆分為多個工作表
from collections import defaultdict# 假設A列是部門，按部門拆分
department_data = defaultdict(list)for row in sheet.rows:department = row[0].valuedepartment_data[department].append([cell.value for cell in row])# 創建新工作表
for department, rows in department_data.items():new_sheet = wb.create_sheet(title=department)for row in rows:new_sheet.append(row)
七、保護與加密
1. 保護工作表
# 保護工作表（僅允許查看）
sheet.protection.sheet = True
sheet.protection.password = '123456'# 允許特定操作
sheet.protection.selectLockedCells = False
sheet.protection.selectUnlockedCells = True
2. 加密工作簿
# 設置工作簿密碼（需使用密碼打開）
wb.security.workbookPassword = '123456'
wb.security.lockStructure = True # 鎖定結構，禁止添加/刪除工作表
八、自定義 Excel 模板
# 創建帶樣式和公式的模板
def create_template():wb = Workbook()sheet = wb.active# 設置表頭樣式header_style = NamedStyle(name='header')header_style.font = Font(bold=True, color='FFFFFF')header_style.fill = PatternFill(start_color='4285F4', fill_type='solid')# 添加表頭sheet.append(['日期', '收入', '支出', '余額'])for cell in sheet[1]:cell.style = header_style# 設置余額公式（D3=D2+B3-C3，下拉自動計算）sheet['D2'] = 0 # 初始余額sheet['D3'] = '=D2+B3-C3'# 設置格式for col in ['B', 'C', 'D']:sheet[f'{col}1'].alignment = Alignment(horizontal='center')sheet.column_dimensions[col].width = 15# 保存模板wb.save('financial_template.xlsx')
九、與 Pandas 結合使用
import pandas as pd
from openpyxl import load_workbook
from openpyxl.utils.dataframe import dataframe_to_rows# 從DataFrame寫入Excel
df = pd.DataFrame({'姓名': ['張三', '李四'], '年齡': [25, 30]})wb = Workbook()
sheet = wb.active# 將DataFrame寫入工作表
for r in dataframe_to_rows(df, index=False, header=True):sheet.append(r)# 添加統計行
sheet.append(['平均年齡', f'=AVERAGE(B2:B{sheet.max_row})'])wb.save('pandas_example.xlsx')# 從Excel讀取到DataFrame
wb = load_workbook('pandas_example.xlsx')
sheet = wb.activedata = []
for row in sheet.rows:data.append([cell.value for cell in row])df = pd.DataFrame(data[1:], columns=data[0])
十、處理 Excel 內置函數
# 檢查公式是否有效
from openpyxl.utils import FORMULAEif 'VLOOKUP' in FORMULAE:sheet['A1'] = '=VLOOKUP(B1,$D$1:$E$10,2,FALSE)'# 獲取函數參數信息
print(FORMULAE['SUM']) # 輸出: {'min_params': 1, 'max_params': 255, 'category': '數學與三角函數', ...}
通過這些進階技巧，你可以更高效地處理復雜的 Excel 任務，從自動化報表生成到數據分析，甚至構建 Excel 應用程序。
官方文檔：openpyxl - A Python library to read/write Excel 2010 xlsx/xlsm files — openpyxl 3.1.3 documentation，這是 openpyxl 庫的官方文檔，內容權威、全面，涵蓋了從基礎操作到高級應用的所有功能，并且提供了豐富的示例代碼，有助于開發者快速上手
