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 库的官方文档，内容权威、全面，涵盖了从基础操作到高级应用的所有功能，并且提供了丰富的示例代码，有助于开发者快速上手
