掌握openpyxl：Excel自动化处理全指南

发布于：2025-06-22 ⋅ 阅读:(19) ⋅ 点赞:(0)

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 = font
        cell.border = border
        cell.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_font
    cell.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')

注意事项

  1. 版本兼容性：openpyxl 不支持旧版 .xls 文件，需用 xlrd/xlwt 处理。
  2. 公式计算：openpyxl 不自动计算公式，需手动处理或使用其他库（如 pycel）。
  3. 合并单元格：读取合并单元格时，只有左上角单元格有值，其他为 MergedCell。
  4. 性能考虑：处理大文件时使用 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, FormatObject

data_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 = True
    series.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].value
    department_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.active

data = []
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 FORMULAE

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

网站公告

今日签到

点亮在社区的每一天
去签到

热门文章

最新发布