openpyxl入门：Excel读写基础操作

Python 办公自动化专题 · 从零开始掌握Python操作Excel的核心技能

专题：Python 自动化办公系统学习

关键词：Python, 自动化办公, openpyxl, Excel读写, 工作簿, 工作表, 单元格, Python自动化

一、openpyxl概述

openpyxl是Python生态中最流行的Excel文件操作库之一，专门用于读写Microsoft Excel 2010及以上版本的xlsx/xlsm/xltx/xltm文件格式。它提供了完整的API来操作Excel文件的各个层次：从最顶层的工作簿（Workbook），到中间层的工作表（Worksheet），再到底层的单元格（Cell），每个层次都封装了丰富的操作方法。与xlrd/xlwt/xlwings/pandas等其他Excel处理库相比，openpyxl在功能完整性和易用性之间取得了良好的平衡。

openpyxl的核心特性包括：原生xlsx格式支持，无需依赖Excel应用程序即可独立运行；完整的工作簿生命周期管理（创建、打开、保存、关闭）；丰富的工作表操作能力（增删改查、标签颜色、隐藏等）；全面的单元格格式控制（字体、颜色、对齐、边框、数字格式）；内置公式写入和计算支持；图表创建与插入功能；数据验证和条件格式等高级特性。这些特性使得openpyxl成为Python办公自动化中处理Excel文件的首选方案。

与其他Excel库对比

库名称	支持格式	读/写	样式支持	适用场景
openpyxl	xlsx/xlsm	读+写	完整	通用Excel操作
xlrd	xls/xlsx	只读	有限	旧格式读取
xlwt	xls	只写	完整	旧格式写入
xlwings	xlsx/xls	读+写	完整	需要Excel应用
pandas	xlsx/xls/csv	读+写	有限	数据分析

安装方法

pip install openpyxl

推荐使用国内镜像源加速安装：

pip install openpyxl -i https://pypi.tuna.tsinghua.edu.cn/simple

安装完成后可以通过简单的 import 验证是否成功。openpyxl 无需额外安装Excel或任何Office组件即可独立运行，这是它最大的优势之一。对于需要处理图片和图表的场景，建议同时安装 Pillow 库，以获取更完整的图片处理能力。

要点提示：openpyxl 无法处理 xls 格式文件（Excel 97-2003），如需处理 xls 格式，需要组合使用 xlrd 和 xlwt。实际项目中常将 openpyxl 与 pandas 配合使用：pandas 负责数据清洗和分析，openpyxl 负责格式化和报表生成。

二、工作簿操作

工作簿（Workbook）是操作Excel文件的入口对象，所有操作都需要基于一个工作簿实例来进行。openpyxl支持从空白创建工作簿、从已有文件打开工作簿，以及将内存中的工作簿保存到磁盘。理解工作簿的加载模式对写出高效且内存友好的代码至关重要。

创建工作簿与保存

from openpyxl import Workbook

# 创建一个新的工作簿
wb = Workbook()

# 获取默认的工作表
ws = wb.active

# 写入一些数据
ws['A1'] = 'Hello, openpyxl!'

# 保存工作簿到文件
wb.save('example.xlsx')

# 操作完成后关闭工作簿（释放资源）
wb.close()

打开已有工作簿

openpyxl 提供了两种加载模式：data_only 参数控制是从公式缓存中读取值还是读取公式本身，这是使用中最容易混淆的地方之一。

from openpyxl import load_workbook

# 默认模式：读取公式（不计算结果）
wb = load_workbook('example.xlsx')

# data_only=True：读取公式的缓存结果（即上次保存时的计算值）
wb_value = load_workbook('example.xlsx', data_only=True)

# 检查差异
ws = wb.active
ws_value = wb_value.active

# 如果A1单元格包含公式 =SUM(B1:B10)
print(ws['A1'].value)        # 输出：=SUM(B1:B10)
print(ws_value['A1'].value)  # 输出：42（假设计算结果为42）

wb.close()
wb_value.close()

要点提示：data_only=True 获取的是Excel最近一次保存时的计算结果。如果公式引用的数据在保存后发生了变化，data_only 模式不会重新计算。需要最新计算结果时，应在Excel中打开文件并保存，或在 Python 中使用 openpyxl 的公式计算引擎。

只读模式与只写模式

对于大文件处理，openpyxl 提供了优化的读写模式，大幅降低内存占用：

from openpyxl import load_workbook

# 只读模式：逐行读取，内存占用极低，适合超大文件
wb_readonly = load_workbook('large_file.xlsx', read_only=True)
ws = wb_readonly.active
for row in ws.iter_rows():
    for cell in row:
        print(cell.value)
wb_readonly.close()

# 只写模式：逐行写入，无法修改样式，适合大批量数据导出
from openpyxl import Workbook
wb_writeonly = Workbook(write_only=True)
ws = wb_writeonly.create_sheet()
ws.append(['ID', '姓名', '分数'])
for i in range(100000):
    ws.append([i, f'学生{i}', 60 + i % 41])
wb_writeonly.save('large_output.xlsx')
wb_writeonly.close()

三、工作表管理

一个工作簿可以包含多个工作表（Worksheet），openpyxl 提供了丰富的工作表管理API，支持获取工作表列表、创建新的工作表、重命名、设置标签颜色、隐藏工作表等操作。熟练管理工作表是构建复杂Excel报表的基础技能。

工作表基本操作

from openpyxl import Workbook

wb = Workbook()

# 获取当前活动工作表
ws = wb.active
print(f"当前工作表: {ws.title}")

# 创建工作表：可以在指定位置插入
ws1 = wb.create_sheet('销售数据')       # 默认追加到最后
ws2 = wb.create_sheet('库存数据', 0)     # 插入到第一个位置
ws3 = wb.create_sheet('客户信息', 1)     # 插入到索引1的位置

# 获取所有工作表名称
print(wb.sheetnames)  # 输出：['库存数据', '客户信息', '销售数据', 'Sheet']

# 通过名称获取工作表
sheet = wb['销售数据']

# 重命名工作表
sheet.title = '月度销售报表'

# 删除工作表
del wb['库存数据']      # 方式一
wb.remove(wb['客户信息'])  # 方式二

print(wb.sheetnames)  # 输出：['月度销售报表', 'Sheet']

wb.close()

工作表属性设置

from openpyxl import Workbook
from openpyxl.styles import PatternFill

wb = Workbook()
ws = wb.active

# 设置工作表标签颜色
ws.sheet_properties.tabColor = 'FF0000'  # 红色标签

# 隐藏工作表
ws2 = wb.create_sheet('隐藏工作表')
ws2.sheet_properties.tabColor = '808080'
# 方式一：通过 sheet_state
ws2.sheet_state = 'hidden'       # 用户可通过右键取消隐藏
# 方式二：非常隐藏（用户无法通过Excel界面取消隐藏）
# ws2.sheet_state = 'veryHidden'

# 设置工作表缩放
ws.sheet_view.zoomScale = 120    # 120% 缩放

# 设置工作表打印区域
ws.print_area = 'A1:F50'

# 冻结窗格（冻结首行和首列）
ws.freeze_panes = 'B2'           # B2单元格左上部分全部冻结

wb.save('sheet_properties.xlsx')
wb.close()

工作表切换和复制

from openpyxl import Workbook
from openpyxl.utils import quote_sheetname

wb = Workbook()
ws1 = wb.active
ws1.title = '原始数据'
ws1['A1'] = '重要数据'

# 复制工作表
ws2 = wb.copy_worksheet(ws1)
ws2.title = '数据备份'
print(ws2['A1'].value)  # 输出：重要数据

# 移动工作表位置（移动到末尾）
wb.move_sheet(ws2, offset=-1)  # 负值向前移，正值向后移

# 安全地引用带空格的工作表名称
sheet_name = '月度 报表'
ws3 = wb.create_sheet(sheet_name)
ws3['A1'] = 100

# 在公式中引用带特殊字符的表名
from openpyxl.utils import quote_sheetname
# 在公式中使用引用的表名
ws3['B1'] = f'={quote_sheetname(sheet_name)}!A1'

wb.save('sheet_management.xlsx')
wb.close()

四、单元格读写

单元格（Cell）是Excel数据存储的基本单元，openpyxl 提供了灵活多样的单元格读写方式，支持通过行列索引或Excel坐标两种方式引用单元格。对于大批量数据操作，掌握正确的遍历和写入方式可以显著提升程序性能。

单个单元格读写

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

# 方式一：使用Excel坐标（字母+数字）
ws['A1'] = '姓名'
ws['B1'] = '年龄'
ws['C1'] = '成绩'

# 方式二：使用行列索引（行、列）
ws.cell(row=2, column=1, value='张三')
ws.cell(row=2, column=2, value=25)
ws.cell(row=2, column=3, value=92.5)

# 读取单元格
name = ws['A2'].value         # 方式一
age = ws.cell(row=2, column=2).value  # 方式二
print(f'姓名：{name}, 年龄：{age}')

# 获取单元格的其他属性
cell = ws['C2']
print(f'值：{cell.value}')       # 单元格的值
print(f'行：{cell.row}')         # 行号
print(f'列：{cell.column}')      # 列号（数字）
print(f'坐标：{cell.coordinate}') # 坐标字符串

wb.save('cell_basic.xlsx')
wb.close()

行列范围读写

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

# 准备数据
headers = ['商品', '单价', '数量', '总价']
data = [
    ['苹果', 5.5, 100],
    ['香蕉', 3.0, 150],
    ['橘子', 4.2, 120],
    ['葡萄', 8.8, 80],
]

# 写入表头
for col_idx, header in enumerate(headers, 1):
    ws.cell(row=1, column=col_idx, value=header)

# 写入数据行
for row_idx, row_data in enumerate(data, 2):
    for col_idx, value in enumerate(row_data, 1):
        ws.cell(row=row_idx, column=col_idx, value=value)

# 使用切片读取范围数据
cell_range = ws['A1:D5']
for row in cell_range:
    for cell in row:
        print(f'{cell.coordinate} = {cell.value}')

# 读取指定行
row_values = [cell.value for cell in ws[2]]  # 第二行所有单元格

# 读取指定列
col_values = [cell.value for cell in ws['A']]  # A列所有单元格

wb.save('cell_range.xlsx')
wb.close()

批量写入与遍历

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

# 使用 iter_rows 和 iter_cols 遍历
data = [
    ['A1', 'B1', 'C1'],
    ['A2', 'B2', 'C2'],
    ['A3', 'B3', 'C3'],
]

for row_data in data:
    ws.append(row_data)  # 逐行追加数据

# 按行遍历（返回行迭代器）
print("按行遍历：")
for row in ws.iter_rows(min_row=1, max_row=3, min_col=1, max_col=3):
    for cell in row:
        print(f'  {cell.coordinate}={cell.value}', end='')
    print()

# 按列遍历（返回列迭代器）
print("按列遍历：")
for col in ws.iter_cols(min_row=1, max_row=3, min_col=1, max_col=3):
    for cell in col:
        print(f'  {cell.coordinate}={cell.value}', end='')
    print()

# 使用 values_only 直接获取纯值
print("纯值输出：")
for row in ws.iter_rows(min_row=1, max_row=3, values_only=True):
    print(row)

wb.close()

要点提示：当需要大批量写入数据时，推荐使用 ws.append() 方法逐行追加，这比逐个单元格赋值要快得多。在读取大量数据时，使用 iter_rows(values_only=True) 可以直接获取 Python 原生类型的值，避免创建 Cell 对象带来的额外内存开销。

单元格数据类型与空值处理

from openpyxl import Workbook
from datetime import datetime, date

wb = Workbook()
ws = wb.active

# openpyxl 支持多种数据类型
ws['A1'] = '字符串文本'        # str
ws['B1'] = 42                 # int
ws['C1'] = 3.14159            # float
ws['D1'] = True               # bool
ws['E1'] = None               # None（空单元格）
ws['F1'] = datetime.now()     # datetime
ws['G1'] = date.today()       # date
ws['H1'] = '=SUM(B1:C1)'      # 公式（字符串形式）

# 读取并检查数据类型
for cell in ws[1]:
    if cell.value is None:
        print(f'{cell.coordinate}: 空值')
    elif isinstance(cell.value, (datetime, date)):
        print(f'{cell.coordinate}: 日期={cell.value}')
    else:
        print(f'{cell.coordinate}: 类型={type(cell.value).__name__}, 值={cell.value}')

# 空值判断的最佳实践
if ws['E1'].value is None:
    print('这是一个空单元格')

# 使用布尔值判断（不推荐，因为0和False也会被认为是False）
if not ws['E1'].value:
    print('值为空或假值')

wb.save('data_types.xlsx')
wb.close()

五、数据格式化

数据格式化是Excel报表制作中最能体现专业度的环节。openpyxl 通过 openpyxl.styles 模块提供了丰富的数据格式控制能力，支持字体、颜色、对齐方式、边框、填充、数字格式等全方位的单元格样式的设置。合理的格式化能让数据报表更加清晰易读，提升信息传达效率。

字体设置

from openpyxl import Workbook
from openpyxl.styles import Font

wb = Workbook()
ws = wb.active

# 设置标题字体
title_font = Font(
    name='Microsoft YaHei',     # 字体名称
    size=16,                     # 字号
    bold=True,                   # 加粗
    italic=False,                # 斜体
    color='2E7D32'               # 字体颜色（十六进制，无#号）
)
ws['A1'].value = '销售数据报表'
ws['A1'].font = title_font

# 多种字体样式示例
ws['A3'].value = '加粗红色字体'
ws['A3'].font = Font(bold=True, color='FF0000', size=12)

ws['A4'].value = '蓝色斜体带下划线'
ws['A4'].font = Font(italic=True, color='0000FF', underline='single', size=11)

ws['A5'].value = '删除线和双下划线'
ws['A5'].font = Font(strikethrough=True, underline='double', size=10)

# 批量设置表头样式
headers = ['编号', '产品名称', '单价', '库存量']
header_font = Font(name='Arial', bold=True, color='FFFFFF', size=11)

for col, header in enumerate(headers, 1):
    cell = ws.cell(row=7, column=col, value=header)
    cell.font = header_font

wb.save('font_styles.xlsx')
wb.close()

对齐方式、边框和填充

from openpyxl import Workbook
from openpyxl.styles import Alignment, Border, Side, PatternFill

wb = Workbook()
ws = wb.active

# 对齐设置
center_align = Alignment(
    horizontal='center',     # 水平居中
    vertical='center',       # 垂直居中
    wrap_text=True           # 自动换行
)

ws['A1'].value = '居中显示\n（自动换行）'
ws['A1'].alignment = center_align

# 其他对齐方式
ws['A2'].value = '左对齐顶部对齐'
ws['A2'].alignment = Alignment(horizontal='left', vertical='top')

ws['A3'].value = '右对齐底部对齐'
ws['A3'].alignment = Alignment(horizontal='right', vertical='bottom')

# 边框设置
thin_border = Border(
    left=Side(style='thin', color='000000'),
    right=Side(style='thin', color='000000'),
    top=Side(style='thin', color='000000'),
    bottom=Side(style='thin', color='000000')
)

dashed_border = Border(
    left=Side(style='dashed', color='FF0000'),
    right=Side(style='dashed', color='FF0000'),
    top=Side(style='dashed', color='FF0000'),
    bottom=Side(style='dashed', color='FF0000')
)

# 填充设置
green_fill = PatternFill(
    start_color='2E7D32',    # 填充起始色
    end_color='2E7D32',      # 填充结束色（纯色时与起始色相同）
    fill_type='solid'        # 填充类型
)

# 渐变填充
gradient_fill = PatternFill(
    start_color='E8F5E9',
    end_color='FFFFFF',
    fill_type='lightTridiag'
)

# 应用样式
ws['A5'].value = '绿色背景白色字体'
ws['A5'].fill = green_fill
ws['A5'].font = Font(color='FFFFFF', bold=True)
ws['A5'].border = thin_border

ws['A6'].value = '红色虚线边框'
ws['A6'].border = dashed_border

# 批量设置表格样式
data = [['A', 'B', 'C'], [1, 2, 3], [4, 5, 6]]
for row_idx, row_data in enumerate(data, 10):
    for col_idx, value in enumerate(row_data, 1):
        cell = ws.cell(row=row_idx, column=col_idx, value=value)
        cell.alignment = center_align
        cell.border = thin_border

wb.save('alignment_border_fill.xlsx')
wb.close()

数字格式与行列调整

from openpyxl import Workbook
from openpyxl.styles import numbers

wb = Workbook()
ws = wb.active

# 设置列宽
ws.column_dimensions['A'].width = 20
ws.column_dimensions['B'].width = 15
ws.column_dimensions['C'].width = 15
ws.column_dimensions['D'].width = 15

# 设置行高
ws.row_dimensions[1].height = 30
ws.row_dimensions[2].height = 25

# 数字格式示例
ws['A1'] = '格式说明'
ws['B1'] = '原始值'
ws['C1'] = '格式化显示'

# 常规数字
ws['A2'] = '整数'
ws['B2'] = 1234567
ws['C2'] = 1234567
ws['C2'].number_format = '#,##0'        # 千分位分隔

ws['A3'] = '小数保留2位'
ws['B3'] = 3.14159
ws['C3'] = 3.14159
ws['C3'].number_format = '0.00'         # 保留两位小数

ws['A4'] = '百分比'
ws['B4'] = 0.856
ws['C4'] = 0.856
ws['C4'].number_format = '0.00%'       # 百分比格式

ws['A5'] = '货币'
ws['B5'] = 1280.5
ws['C5'] = 1280.5
ws['C5'].number_format = '¥#,##0.00'   # 人民币格式

ws['A6'] = '日期'
from datetime import date
ws['B6'] = date.today()
ws['C6'] = date.today()
ws['C6'].number_format = 'yyyy-mm-dd'   # 日期格式

# 自动调整列宽（辅助函数）
def auto_fit_column_width(ws, min_width=8, max_width=50):
    for column_cells in ws.columns:
        max_length = 0
        col_letter = column_cells[0].column_letter
        for cell in column_cells:
            if cell.value:
                # 中文字符按2个宽度计算
                cell_len = sum(2 if ord(c) > 127 else 1 for c in str(cell.value))
                max_length = max(max_length, cell_len)
        adjusted_width = min(max(max_length + 2, min_width), max_width)
        ws.column_dimensions[col_letter].width = adjusted_width

auto_fit_column_width(ws)

# 合并单元格
ws.merge_cells('A10:D10')
ws['A10'].value = '这是合并单元格（跨A列到D列）'
ws['A10'].alignment = Alignment(horizontal='center', vertical='center')

# 取消合并单元格
# ws.unmerge_cells('A10:D10')

wb.save('number_format.xlsx')
wb.close()

要点提示：数字格式字符串遵循Excel内置格式语法，最常用的格式包括：'#,##0'（千分位）、'0.00'（两位小数）、'0.00%'（百分比）、'yyyy-mm-dd'（日期）。合并单元格在读取时，只有左上角单元格保留值，其他单元格值为None，公式中引用合并单元格需特别注意。

六、公式与函数

Excel的强大之处在于其丰富的公式和函数体系。openpyxl 支持将Excel公式以字符串形式写入单元格，也可以注册自定义函数。需要注意的是，openpyxl 本身不是Excel计算引擎，它默认存储公式文本而非计算结果，但可以通过启用公式计算选项来获取计算结果。

写入Excel公式

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

# 准备数据
headers = ['商品', '单价', '数量', '小计']
data = [
    ['苹果', 5.5, 100],
    ['香蕉', 3.0, 150],
    ['橘子', 4.2, 120],
]

# 写入表头和数据
for col, header in enumerate(headers, 1):
    ws.cell(row=1, column=col, value=header)

for row_idx, row_data in enumerate(data, 2):
    for col_idx, value in enumerate(row_data, 1):
        ws.cell(row=row_idx, column=col_idx, value=value)

# 写入公式：小计 = 单价 * 数量
for row in range(2, 5):
    ws[f'D{row}'] = f'=B{row}*C{row}'

# 写入汇总公式
ws['A6'] = '合计'
ws['D6'] = '=SUM(D2:D4)'

# 平均值
ws['A7'] = '平均单价'
ws['B7'] = '=AVERAGE(B2:B4)'

# 最大值和最小值
ws['A8'] = '最高数量'
ws['C8'] = '=MAX(C2:C4)'
ws['A9'] = '最低数量'
ws['C9'] = '=MIN(C2:C4)'

# 统计条目数
ws['A10'] = '商品种类数'
ws['B10'] = '=COUNTA(A2:A4)'

wb.save('formulas_basic.xlsx')
wb.close()

条件判断与查找函数

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

# 准备成绩数据
headers = ['姓名', '语文', '数学', '英语', '总分', '评级']
students = [
    ['张三', 85, 92, 78],
    ['李四', 92, 88, 95],
    ['王五', 60, 65, 70],
    ['赵六', 45, 55, 50],
]

for col, header in enumerate(headers, 1):
    ws.cell(row=1, column=col, value=header)

for row_idx, student in enumerate(students, 2):
    for col_idx, value in enumerate(student, 1):
        ws.cell(row=row_idx, column=col_idx, value=value)

# 总分公式
for row in range(2, 6):
    ws[f'E{row}'] = f'=SUM(B{row}:D{row})'

# IF嵌套实现等级评定
for row in range(2, 6):
    ws[f'F{row}'] = f'=IF(E{row}>=270,"优秀",IF(E{row}>=240,"良好",IF(E{row}>=180,"及格","不及格")))'

# VLOOKUP 示例（需要先有查找表）
# 创建评分标准表（放在右侧区域）
ws['H1'] = '分数下限'
ws['I1'] = '等级'
grade_data = [(270, '优秀'), (240, '良好'), (180, '及格'), (0, '不及格')]
for idx, (score, grade) in enumerate(grade_data, 2):
    ws.cell(row=idx, column=8, value=score)
    ws.cell(row=idx, column=9, value=grade)

# 使用VLOOKUP替代IF（更加简洁）
for row in range(2, 6):
    # 从H2:I5范围查找E列总分对应的等级
    ws[f'G{row}'] = f'=VLOOKUP(E{row},$H$2:$I$5,2,TRUE)'
ws['G1'] = 'VLOOKUP评级'

# COUNTIF 统计
ws['A10'] = '优秀人数'
ws['B10'] = '=COUNTIF(F2:F5,"优秀")'

ws['A11'] = '及格人数'
ws['B11'] = '=COUNTIF(F2:F5,">=180")'

wb.save('formulas_advanced.xlsx')
wb.close()

公式计算与自定义函数

from openpyxl import Workbook
from openpyxl.formula import Tokenizer
from openpyxl.utils import FORMULAE

wb = Workbook()
ws = wb.active

ws['A1'] = 10
ws['B1'] = 20
ws['C1'] = '=A1+B1'

# 检查公式是否被openpyxl支持
print(f"SUM 是否支持: {'SUM' in FORMULAE}")
print(f"VLOOKUP 是否支持: {'VLOOKUP' in FORMULAE}")

# 解析公式 Token
tokenizer = Tokenizer('=SUM(A1:A10)*2+IF(B1>0,B1,0)')
for token in tokenizer.items:
    print(f'  Token类型: {token.type}, 值: {token.value}')

# 使用 openpyxl 的公式计算功能（需安装数学引擎支持）
# 注意：基础版 openpyxl 不做公式求值，仅在Excel中打开时才计算

# 建议：如果需要在 Python 中计算，
# 可以先在 Python 中计算好结果，再将结果和公式一起写入
a1_val = 10
b1_val = 20
result = a1_val + b1_val

# 同时写入公式和计算值
ws['C1'] = result                    # 写入计算结果
ws['D1'] = '=A1+B1'                  # 同时保留公式
ws['D1'].value = result               # 覆盖公式为值

# 最佳实践：计算和公式并存
for row in range(2, 100):
    a = row * 10
    b = row * 20
    ws.cell(row=row, column=1, value=a)
    ws.cell(row=row, column=2, value=b)
    # 写入公式，同时写入用Python计算的结果值
    ws.cell(row=row, column=3, value=a + b)
    ws.cell(row=row, column=4, value=f'=A{row}+B{row}')

wb.save('formulas_compute.xlsx')
wb.close()

要点提示：写入公式后，在Excel中打开文件时会自动计算。如果需要在Python中获取公式结果，可以使用 data_only=True 模式加载工作簿（读取上次保存时的缓存结果），或者在Python中自行计算后同时写入值和公式。对于复杂的Excel模型，推荐在Python中完成所有计算逻辑，只把最终结果和公式一起写入Excel。

七、数据操作进阶

除了基本的读写和格式化，openpyxl 还提供了数据排序、筛选、数据验证和条件格式等进阶功能。这些功能可以让生成的Excel文件具有更好的交互性和数据完整性保障，提升报表的用户体验。

数据排序与筛选

from openpyxl import Workbook
from openpyxl.worksheet.datavalidation import DataValidation

wb = Workbook()
ws = wb.active

# 准备数据
headers = ['姓名', '部门', '工资', '入职日期']
data = [
    ['张三', '技术部', 12000, '2020-03-15'],
    ['李四', '市场部', 10000, '2019-07-22'],
    ['王五', '技术部', 15000, '2021-01-10'],
    ['赵六', '人事部', 9000, '2018-11-05'],
    ['钱七', '市场部', 11000, '2022-06-20'],
]

ws.append(headers)
for row in data:
    ws.append(row)

# 添加自动筛选
ws.auto_filter.ref = 'A1:D6'

# 设置筛选条件（部门为技术部）
ws.auto_filter.add_filter_column(1, ['技术部'])

# 对工资列进行排序（降序）
from openpyxl.worksheet.filters import SortCondition
ws.auto_filter.sort_state.add_sort_condition(SortCondition(
    ref='C2:C6',
    descending=True
))

wb.save('filter_sort.xlsx')
wb.close()

数据验证

from openpyxl import Workbook
from openpyxl.worksheet.datavalidation import DataValidation

wb = Workbook()
ws = wb.active

# 准备数据输入模板
headers = ['姓名', '性别', '年龄', '部门', '邮箱']
ws.append(headers)

# 1. 列表验证（限制输入为指定值）
gender_validation = DataValidation(
    type='list',
    formula1='"男,女"',
    allow_blank=False
)
gender_validation.error = '请选择性别'
gender_validation.errorTitle = '输入错误'
gender_validation.prompt = '请从下拉列表中选择性别'
gender_validation.promptTitle = '性别选择'
ws.add_data_validation(gender_validation)
gender_validation.add('B2:B100')

# 2. 整数范围验证
age_validation = DataValidation(
    type='whole',
    operator='between',
    formula1='18',
    formula2='65'
)
age_validation.error = '年龄必须在18到65之间'
ws.add_data_validation(age_validation)
age_validation.add('C2:C100')

# 3. 自定义公式验证（邮箱格式检查）
email_validation = DataValidation(
    type='custom',
    formula1='=ISNUMBER(FIND("@",E2))'
)
email_validation.error = '请输入有效的邮箱地址'
ws.add_data_validation(email_validation)
email_validation.add('E2:E100')

# 4. 下拉列表验证（部门）
dept_validation = DataValidation(
    type='list',
    formula1='"技术部,市场部,人事部,财务部,运营部"'
)
dept_validation.error = '请选择有效部门'
ws.add_data_validation(dept_validation)
dept_validation.add('D2:D100')

# 插入示例数据验证效果
ws.append(['张三', '男', 28, '技术部', 'zhangsan@company.com'])

wb.save('data_validation.xlsx')
wb.close()

条件格式入门

from openpyxl import Workbook
from openpyxl.formatting.rule import CellIsRule, FormulaRule

wb = Workbook()
ws = wb.active

# 准备数据
headers = ['姓名', '语文', '数学', '英语']
data = [
    ['张三', 85, 92, 78],
    ['李四', 92, 88, 95],
    ['王五', 60, 65, 70],
    ['赵六', 45, 55, 50],
]

ws.append(headers)
for row in data:
    ws.append(row)

# 1. 单元格值条件格式：成绩>=90标记为绿色
from openpyxl.styles import PatternFill, Font
green_fill = PatternFill(start_color='C6EFCE', end_color='C6EFCE', fill_type='solid')
green_font = Font(color='006100')

ws.conditional_formatting.add(
    'B2:D5',
    CellIsRule(operator='greaterThanOrEqual', formula=['90'],
               fill=green_fill, font=green_font)
)

# 2. 单元格值条件格式：成绩<60标记为红色
red_fill = PatternFill(start_color='FFC7CE', end_color='FFC7CE', fill_type='solid')
red_font = Font(color='9C0006')

ws.conditional_formatting.add(
    'B2:D5',
    CellIsRule(operator='lessThan', formula=['60'],
               fill=red_fill, font=red_font)
)

# 3. 使用公式：标记总分>=270的学生整行
yellow_fill = PatternFill(start_color='FFEB9C', end_color='FFEB9C', fill_type='solid')
ws.conditional_formatting.add(
    'A2:D5',
    FormulaRule(formula=['$B2+$C2+$D2>=270'], fill=yellow_fill)
)

# 4. 色阶条件格式（数据条/色阶需要更复杂的设置）
# 这里使用 ColorScaleRule 实现
from openpyxl.formatting.rule import ColorScaleRule
ws.conditional_formatting.add(
    'B2:D5',
    ColorScaleRule(
        start_type='min', start_color='F8696B',
        mid_type='percentile', mid_value=50, mid_color='FFEB84',
        end_type='max', end_color='63BE7B'
    )
)

wb.save('conditional_formatting.xlsx')
wb.close()

要点提示：数据验证和条件格式是基于Excel原生功能实现的，在Excel中打开时生效。条件格式的优先级规则为：越靠后添加的规则优先级越高。如果多条规则冲突，以优先级最高的规则为准。数据验证的下拉列表值不要超过255个字符，否则需要将列表数据放在工作表的某个区域并通过公式引用。

八、性能优化

当处理包含数万行甚至数十万行数据的大型Excel文件时，性能问题和内存占用就成了必须面对的挑战。openpyxl 为此提供了专门优化模式：只读模式（read_only）和只写模式（write_only），合理地选择和使用这些模式可以显著提升处理效率并降低资源消耗。

逐行写入优化

from openpyxl import Workbook
import time

# 方法一（慢速）：逐个单元格赋值
def slow_write():
    wb = Workbook()
    ws = wb.active
    for row in range(1, 1001):
        for col in range(1, 21):
            ws.cell(row=row, column=col, value=f'数据{row}-{col}')
    wb.save('slow.xlsx')
    wb.close()

# 方法二（快速）：使用 append 逐行追加
def fast_write():
    wb = Workbook()
    ws = wb.active
    for row in range(1, 1001):
        row_data = [f'数据{row}-{col}' for col in range(1, 21)]
        ws.append(row_data)
    wb.save('fast.xlsx')
    wb.close()

# 方法三（最快）：只写模式
def write_only_mode():
    wb = Workbook(write_only=True)
    ws = wb.create_sheet()
    for row in range(1, 1001):
        row_data = [f'数据{row}-{col}' for col in range(1, 21)]
        ws.append(row_data)
    wb.save('fastest.xlsx')
    wb.close()

print("性能对比（1000行 x 20列）：")
for name, func in [('逐个单元格', slow_write),
                    ('逐行追加', fast_write),
                    ('只写模式', write_only_mode)]:
    start = time.time()
    func()
    elapsed = time.time() - start
    print(f'{name}: {elapsed:.3f}秒')

只读模式与大文件处理

from openpyxl import load_workbook

# 只读模式：逐行流式读取，适合超大文件
def read_large_file(filename):
    wb = load_workbook(filename, read_only=True)
    ws = wb.active

    row_count = 0
    for row in ws.iter_rows():
        row_count += 1
        # 处理每一行数据
        values = [cell.value for cell in row]
        # 此处可以插入业务逻辑，如数据清洗、转换等
        if row_count > 100:   # 示例只处理前100行
            break

    wb.close()   # 只读模式必须关闭以释放文件锁
    return row_count

# 文件大小检查
import os
def check_file_size(filepath):
    size_mb = os.path.getsize(filepath) / (1024 * 1024)
    print(f'文件大小: {size_mb:.2f} MB')
    if size_mb > 50:
        print('警告：文件超过50MB，建议使用只读/只写模式')
    return size_mb

内存优化最佳实践

from openpyxl import Workbook, load_workbook
from openpyxl.utils import get_column_letter
import gc

class ExcelProcessor:
    """大文件Excel处理器示例"""

    @staticmethod
    def optimize_write(data_generator, output_path, batch_size=1000):
        """
        使用只写模式 + 分块生成数据，最大限度降低内存占用
        data_generator: 数据生成器（而非列表）
        """
        wb = Workbook(write_only=True)
        ws = wb.create_sheet()

        batch = []
        for idx, row_data in enumerate(data_generator, 1):
            batch.append(row_data)
            if idx % batch_size == 0:
                for row in batch:
                    ws.append(row)
                batch.clear()  # 及时释放内存

        # 处理剩余数据
        for row in batch:
            ws.append(row)

        wb.save(output_path)
        wb.close()

    @staticmethod
    def optimize_read(input_path, process_func, chunk_size=5000):
        """
        只读模式分块处理，每块调用一次处理函数
        """
        wb = load_workbook(input_path, read_only=True)
        ws = wb.active

        chunk = []
        for row in ws.iter_rows(values_only=True):
            chunk.append(row)
            if len(chunk) >= chunk_size:
                process_func(chunk)
                chunk.clear()
                gc.collect()  # 主动触发垃圾回收

        # 处理剩余数据
        if chunk:
            process_func(chunk)

        wb.close()

# 使用示例：生成100万行数据
def generate_mock_data(count=1000000):
    for i in range(count):
        yield [i, f'用户{i}', 20 + i % 50, 60 + i % 41]

# 注意：实际运行时取消以下注释
# processor = ExcelProcessor()
# processor.optimize_write(generate_mock_data(), 'million_rows.xlsx')
print('ExcelProcessor 类已定义，可处理百万级数据行')

要点提示：处理大文件的黄金法则：写入用 write_only=True + append()，读取用 read_only=True + iter_rows()。不要一次性将所有数据加载到内存中，使用生成器（yield）分批处理。在只读/只写模式下，不能访问单个单元格样式，也不能修改已有数据。如果需要对大文件的部分单元格设置样式，可以考虑分步处理：先用只写模式写入数据，再用标准模式加载并设置样式。

九、实战案例

理论知识的最终目的是解决实际问题。本节通过三个完整的实战案例，展示 openpyxl 在实际工作中的典型应用场景，帮助读者将前面学习的各项技能融会贯通。

案例一：销售数据报表生成

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.chart import BarChart, Reference
from datetime import date

def create_sales_report(output_path):
    """生成月度销售数据报表"""
    wb = Workbook()
    ws = wb.active
    ws.title = '月度销售报表'

    # 样式定义
    header_font = Font(name='Microsoft YaHei', bold=True, color='FFFFFF', size=11)
    header_fill = PatternFill(start_color='2E7D32', end_color='2E7D32', fill_type='solid')
    header_align = Alignment(horizontal='center', vertical='center')
    thin_border = Border(
        left=Side(style='thin'), right=Side(style='thin'),
        top=Side(style='thin'), bottom=Side(style='thin')
    )

    # 标题行
    ws.merge_cells('A1:F1')
    ws['A1'].value = f'2026年5月销售数据报表'
    ws['A1'].font = Font(name='Microsoft YaHei', bold=True, size=16, color='2E7D32')
    ws['A1'].alignment = Alignment(horizontal='center', vertical='center')
    ws.row_dimensions[1].height = 40

    # 表头
    headers = ['日期', '产品名称', '销售区域', '销售数量', '单价(元)', '销售额(元)']
    for col, header in enumerate(headers, 1):
        cell = ws.cell(row=3, column=col, value=header)
        cell.font = header_font
        cell.fill = header_fill
        cell.alignment = header_align
        cell.border = thin_border

    # 模拟销售数据
    sales_data = [
        [date(2026, 5, 1), '笔记本电脑', '华东', 15, 5999, 15*5999],
        [date(2026, 5, 2), '无线鼠标', '华东', 50, 129, 50*129],
        [date(2026, 5, 3), '机械键盘', '华南', 32, 399, 32*399],
        [date(2026, 5, 4), '显示器', '华北', 18, 1899, 18*1899],
        [date(2026, 5, 5), '笔记本电脑', '华南', 12, 5999, 12*5999],
        [date(2026, 5, 6), '平板电脑', '华东', 25, 3299, 25*3299],
        [date(2026, 5, 7), '无线耳机', '华北', 68, 199, 68*199],
        [date(2026, 5, 8), '充电宝', '华南', 45, 89, 45*89],
    ]

    # 写入数据
    for row_idx, row_data in enumerate(sales_data, 4):
        for col_idx, value in enumerate(row_data, 1):
            cell = ws.cell(row=row_idx, column=col_idx, value=value)
            cell.border = thin_border
            cell.alignment = Alignment(horizontal='center', vertical='center')
            # 金额列设置数字格式
            if col_idx in (5, 6):
                cell.number_format = '¥#,##0.00'

    # 汇总行
    summary_row = 4 + len(sales_data)
    ws.cell(row=summary_row, column=1, value='合计').font = Font(bold=True)
    ws.cell(row=summary_row, column=1).border = thin_border
    ws.cell(row=summary_row, column=6, value=f'=SUM(F4:F{summary_row-1})')
    ws.cell(row=summary_row, column=6).font = Font(bold=True, color='2E7D32')
    ws.cell(row=summary_row, column=6).number_format = '¥#,##0.00'
    ws.cell(row=summary_row, column=6).border = thin_border

    # 添加图表
    chart = BarChart()
    chart.type = 'col'
    chart.title = '产品销售数量对比'
    chart.y_axis.title = '销售数量'
    chart.x_axis.title = '产品'
    chart.style = 10

    data_ref = Reference(ws, min_col=4, min_row=3, max_row=summary_row-1)
    cats_ref = Reference(ws, min_col=2, min_row=4, max_row=summary_row-1)
    chart.add_data(data_ref, titles_from_data=True)
    chart.set_categories(cats_ref)
    chart.shape = 4

    ws.add_chart(chart, 'A12')

    # 自动调整列宽
    for col in ws.columns:
        max_length = max(len(str(cell.value or '')) for cell in col)
        ws.column_dimensions[col[0].column_letter].width = max_length + 4

    wb.save(output_path)
    wb.close()
    print(f'报表已生成：{output_path}')

# 生成报表
create_sales_report('sales_report.xlsx')

案例二：学生成绩表创建

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter

def create_grade_report(output_path):
    """生成学生成绩分析表"""
    wb = Workbook()
    ws = wb.active
    ws.title = '学生成绩表'

    # 准备数据
    subjects = ['语文', '数学', '英语', '物理', '化学']
    students = [
        ['张伟', 90, 85, 92, 78, 88],
        ['李娜', 95, 92, 98, 85, 90],
        ['王强', 75, 68, 80, 72, 65],
        ['赵敏', 88, 94, 85, 90, 92],
        ['刘洋', 60, 55, 62, 58, 50],
        ['陈静', 82, 78, 85, 80, 75],
    ]

    # 样式设置
    header_font = Font(bold=True, color='FFFFFF', size=11)
    header_fill = PatternFill(start_color='1F4E79', end_color='1F4E79', fill_type='solid')
    header_align = Alignment(horizontal='center', vertical='center')
    thin_border = Border(
        left=Side(style='thin'), right=Side(style='thin'),
        top=Side(style='thin'), bottom=Side(style='thin')
    )
    pass_fill = PatternFill(start_color='C6EFCE', end_color='C6EFCE', fill_type='solid')
    fail_fill = PatternFill(start_color='FFC7CE', end_color='FFC7CE', fill_type='solid')

    # 表头
    headers = ['姓名'] + subjects + ['总分', '平均分', '排名', '评价']
    for col, header in enumerate(headers, 1):
        cell = ws.cell(row=1, column=col, value=header)
        cell.font = header_font
        cell.fill = header_fill
        cell.alignment = header_align
        cell.border = thin_border

    # 写入数据 + 公式
    for row_idx, student in enumerate(students, 2):
        name = student[0]
        scores = student[1:]

        ws.cell(row=row_idx, column=1, value=name)
        for col_idx, score in enumerate(scores, 2):
            cell = ws.cell(row=row_idx, column=col_idx, value=score)
            cell.alignment = Alignment(horizontal='center')
            cell.border = thin_border
            # 标记不及格（<60）
            if score < 60:
                cell.fill = fail_fill

        # 总分公式
        total_col = len(subjects) + 2  # G列
        ws.cell(row=row_idx, column=total_col, value=f'=SUM(B{row_idx}:F{row_idx})')
        ws.cell(row=row_idx, column=total_col).border = thin_border
        ws.cell(row=row_idx, column=total_col).font = Font(bold=True)
        ws.cell(row=row_idx, column=total_col).alignment = Alignment(horizontal='center')

        # 平均分公式
        avg_col = total_col + 1  # H列
        ws.cell(row=row_idx, column=avg_col, value=f'=AVERAGE(B{row_idx}:F{row_idx})')
        ws.cell(row=row_idx, column=avg_col).number_format = '0.0'
        ws.cell(row=row_idx, column=avg_col).border = thin_border
        ws.cell(row=row_idx, column=avg_col).alignment = Alignment(horizontal='center')

    # 排名公式（使用RANK函数）
    last_row = 1 + len(students)
    total_col = len(subjects) + 2
    for row_idx in range(2, last_row + 1):
        rank_col = total_col + 2  # I列
        ws.cell(row=row_idx, column=rank_col,
                value=f'=RANK(G{row_idx},$G$2:$G${last_row})')
        ws.cell(row=row_idx, column=rank_col).alignment = Alignment(horizontal='center')
        ws.cell(row=row_idx, column=rank_col).border = thin_border

        # 评价公式
        eval_col = rank_col + 1  # J列
        avg_col_letter = get_column_letter(total_col + 1)
        ws.cell(row=row_idx, column=eval_col,
                value=f'=IF({avg_col_letter}{row_idx}>=90,"优秀",'
                      f'IF({avg_col_letter}{row_idx}>=75,"良好",'
                      f'IF({avg_col_letter}{row_idx}>=60,"及格","不及格")))')
        ws.cell(row=row_idx, column=eval_col).alignment = Alignment(horizontal='center')
        ws.cell(row=row_idx, column=eval_col).border = thin_border

    # 设置列宽
    col_widths = [10, 8, 8, 8, 8, 8, 8, 8, 8, 10]
    for idx, width in enumerate(col_widths, 1):
        ws.column_dimensions[get_column_letter(idx)].width = width

    # 冻结窗口
    ws.freeze_panes = 'B2'

    wb.save(output_path)
    wb.close()
    print(f'成绩表已生成：{output_path}')

create_grade_report('grade_report.xlsx')

案例三：库存清单管理

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side, numbers
from openpyxl.formatting.rule import CellIsRule, FormulaRule
from openpyxl.worksheet.datavalidation import DataValidation

def create_inventory_report(output_path):
    """生成库存清单管理表"""
    wb = Workbook()
    ws = wb.active
    ws.title = '库存清单'

    # 样式定义
    header_font = Font(bold=True, color='FFFFFF', size=11)
    header_fill = PatternFill(start_color='4472C4', end_color='4472C4', fill_type='solid')
    center_align = Alignment(horizontal='center', vertical='center')
    thin_border = Border(
        left=Side(style='thin'), right=Side(style='thin'),
        top=Side(style='thin'), bottom=Side(style='thin')
    )

    # 表头
    headers = ['商品编号', '商品名称', '分类', '库存数量', '安全库存', '库存状态',
               '单价(元)', '库存金额(元)', '供应商', '上次盘点日期']
    for col, header in enumerate(headers, 1):
        cell = ws.cell(row=1, column=col, value=header)
        cell.font = header_font
        cell.fill = header_fill
        cell.alignment = center_align
        cell.border = thin_border

    # 库存数据
    inventory = [
        ['P001', 'USB-C数据线', '配件', 200, 50, None, 29.9, None, '深圳电子', '2026-04-15'],
        ['P002', '无线充电器', '配件', 30, 50, None, 129, None, '深圳电子', '2026-04-10'],
        ['P003', '蓝牙耳机', '音频', 15, 30, None, 299, None, '东莞声学', '2026-03-28'],
        ['P004', '机械键盘', '外设', 80, 40, None, 399, None, '苏州科技', '2026-04-20'],
        ['P005', '移动硬盘', '存储', 120, 60, None, 499, None, '合肥存储', '2026-04-25'],
        ['P006', '笔记本电脑', '电脑', 8, 10, None, 5999, None, '昆山制造', '2026-04-01'],
        ['P007', '显示器27寸', '显示', 25, 20, None, 1899, None, '厦门光电', '2026-04-18'],
        ['P008', '鼠标垫', '配件', 500, 100, None, 19.9, None, '义乌商贸', '2026-04-22'],
    ]

    for row_idx, item in enumerate(inventory, 2):
        for col_idx, value in enumerate(item, 1):
            cell = ws.cell(row=row_idx, column=col_idx, value=value)
            cell.alignment = center_align
            cell.border = thin_border
            if col_idx == 7:  # 单价货币格式
                cell.number_format = '¥#,##0.00'

    # 库存状态公式（E列库存 vs F列安全库存）
    for row_idx in range(2, 2 + len(inventory)):
        status_col = 6  # F列
        qty_col_letter = 'E'
        safe_col_letter = 'F'
        ws.cell(row=row_idx, column=status_col, value=f'=IF({qty_col_letter}{row_idx}<=0,"缺货",'
                f'IF({qty_col_letter}{row_idx}<{safe_col_letter}{row_idx},"补货中",'
                f'"正常"))')
        ws.cell(row=row_idx, column=status_col).alignment = center_align
        ws.cell(row=row_idx, column=status_col).border = thin_border

    # 库存金额公式
    for row_idx in range(2, 2 + len(inventory)):
        ws.cell(row=row_idx, column=8, value=f'=E{row_idx}*G{row_idx}')
        ws.cell(row=row_idx, column=8).number_format = '¥#,##0.00'
        ws.cell(row=row_idx, column=8).alignment = center_align
        ws.cell(row=row_idx, column=8).border = thin_border

    # 条件格式：库存状态颜色标记
    # 使用公式法整行染色
    warn_fill = PatternFill(start_color='FFF2CC', end_color='FFF2CC', fill_type='solid')
    danger_fill = PatternFill(start_color='FCE4EC', end_color='FCE4EC', fill_type='solid')

    last_data_row = 1 + len(inventory)
    ws.conditional_formatting.add(
        f'A1:J{last_data_row}',
        FormulaRule(formula=[f'$F2="补货中"'], fill=warn_fill)
    )
    ws.conditional_formatting.add(
        f'A1:J{last_data_row}',
        FormulaRule(formula=[f'$F2="缺货"'], fill=danger_fill)
    )

    # 数据验证：分类列添加下拉选择
    category_validation = DataValidation(
        type='list',
        formula1='"配件,音频,外设,存储,电脑,显示,网络"',
        allow_blank=True
    )
    category_validation.error = '请选择有效的商品分类'
    ws.add_data_validation(category_validation)
    category_validation.add(f'C2:C{last_data_row}')

    # 设置列宽
    col_widths = [12, 16, 10, 12, 12, 12, 12, 14, 12, 14]
    for idx, width in enumerate(col_widths, 1):
        ws.column_dimensions[chr(64 + idx)].width = width

    wb.save(output_path)
    wb.close()
    print(f'库存清单已生成：{output_path}')

create_inventory_report('inventory_report.xlsx')

十、常见问题与最佳实践

在实际使用 openpyxl 进行Excel自动化开发的过程中，开发者经常会遇到编码、性能、兼容性等方面的问题。本节汇总了最常见的问题及解决方案，并总结了一系列经过实践检验的最佳实践建议，帮助读者少走弯路。

常见问题及解决方案

问题	原因	解决方案
文件打开报错或损坏	文件被占用或路径包含中文	使用完整路径，避免中文字符；操作前确保文件未被其他程序打开
中文乱码	编码格式不统一	始终使用 UTF-8 编码保存源文件；openpyxl内部已支持Unicode
读取不到公式结果	data_only=False（默认）	使用 load_workbook(..., data_only=True) 加载
大文件内存溢出	一次性加载所有数据	使用 read_only=True 或 write_only=True 模式
单元格样式丢失	在只写模式下设置样式	只写模式不支持样式，需改用标准模式
xls文件无法打开	openpyxl仅支持xlsx格式	使用xlrd/xlwt处理xls，或先转换为xlsx
图片插入失败	缺少 Pillow 库	安装 Pillow: pip install Pillow
合并单元格读取异常	除左上角外其余cell值为None	使用 ws.merged_cells.ranges 获取合并范围

编码问题详细处理

from openpyxl import Workbook, load_workbook

# 问题：读取文件时中文内容出现乱码
# 解决方案：确保文件路径和内容编码正确

# 1. 文件路径中的中文处理
import os

# 方法一：使用 raw string
filepath = r'D:\data\销售报表.xlsx'

# 方法二：使用 os.path 处理路径
filepath = os.path.join('D:', 'data', '销售报表.xlsx')

# 方法三：使用 pathlib（推荐）
from pathlib import Path
filepath = Path('D:/data/销售报表.xlsx')

# 2. 写入包含中文的内容
wb = Workbook()
ws = wb.active
ws['A1'] = '中文内容完全支持'   # openpyxl原生支持Unicode
ws['A2'] = '價格：¥100'        # 特殊符号也无需额外处理
wb.save(str(filepath))
wb.close()

# 3. 读取包含中文的文件
wb = load_workbook(filepath)
ws = wb.active
print(ws['A1'].value)  # 正常输出：中文内容完全支持
wb.close()

# 4. 处理 CSV 转 Excel 时的编码问题
import csv

def csv_to_xlsx(csv_path, xlsx_path, encoding='utf-8'):
    """将CSV文件转换为格式化的xlsx文件"""
    try:
        with open(csv_path, 'r', encoding=encoding) as f:
            reader = csv.reader(f)
            wb = Workbook()
            ws = wb.active
            for row_data in reader:
                ws.append(row_data)
            wb.save(xlsx_path)
            wb.close()
    except UnicodeDecodeError:
        # 如果UTF-8失败，尝试其他编码
        encodings = ['gbk', 'gb2312', 'utf-16', 'latin-1']
        for enc in encodings:
            try:
                with open(csv_path, 'r', encoding=enc) as f:
                    reader = csv.reader(f)
                    wb = Workbook()
                    ws = wb.active
                    for row_data in reader:
                        ws.append(row_data)
                    wb.save(xlsx_path)
                    wb.close()
                print(f'使用编码 {enc} 成功转换')
                return
            except UnicodeDecodeError:
                continue
        print('所有编码均尝试失败')

csv_to_xlsx('data.csv', 'data.xlsx')

兼容性问题和调试技巧

from openpyxl import Workbook, load_workbook
from openpyxl.styles import Font
import warnings

# 1. Excel版本兼容性
# openpyxl 4.0+ 不再支持 xlsm 中的宏(VBA)
# 如果需要保留宏，使用 xlwings 或 win32com

# 2. 日期时间兼容性
from datetime import datetime, timezone, timedelta

wb = Workbook()
ws = wb.active

# 时区无关的datetime（推荐）
ws['A1'] = datetime.now()
ws['A1'].number_format = 'yyyy-mm-dd hh:mm:ss'

# 带时区的datetime（某些Excel版本可能不兼容）
ws['A2'] = datetime.now(timezone(timedelta(hours=8)))
ws['A2'].number_format = 'yyyy-mm-dd hh:mm:ss'

# 3. 空值处理安全操作
def safe_get_cell_value(cell, default=''):
    """安全获取单元格值，避免None引发异常"""
    return cell.value if cell.value is not None else default

# 4. 常用调试技巧
def debug_workbook(filepath):
    """调试工作簿结构"""
    wb = load_workbook(filepath)
    print(f'工作表数量: {len(wb.sheetnames)}')
    print(f'工作表列表: {wb.sheetnames}')

    for sheet_name in wb.sheetnames:
        ws = wb[sheet_name]
        print(f'\n===== 工作表: {sheet_name} =====')
        print(f'  维度: {ws.dimensions}')
        print(f'  最大行: {ws.max_row}, 最大列: {ws.max_column}')
        print(f'  合并单元格: {list(ws.merged_cells.ranges)}')

        # 打印前5行数据
        for row_idx, row in enumerate(ws.iter_rows(
                max_row=min(5, ws.max_row), values_only=True), 1):
            print(f'  行{row_idx}: {row}')

    wb.close()

# 5. 抑制openpyxl的警告
warnings.filterwarnings('ignore', category=UserWarning, module='openpyxl')

# 6. 版本检查
print(f'openpyxl版本: {__import__("openpyxl").__version__}')
# 不同版本的API可能有细微差异，查看官方文档确认

最佳实践总结

最佳实践清单：

1. 资源管理：操作完成后始终调用 wb.close() 释放文件资源，或使用 with 语句上下文管理器（openpyxl 3.0+ 支持）。

2. 数据优先，样式次之：建议先用只写模式写入大批量原始数据，再用标准模式加载相同文件并应用样式，可大幅降低内存峰值。

3. 公式与值并存：在Python中预先计算结果并同时写入值和公式，确保在Excel中打开时立即显示正确结果。

4. 使用命名约定：对工作表和单元格区域使用有意义的命名，避免使用默认的"Sheet1""Sheet2"等名称。

5. 异常处理：始终使用 try/except/finally 包裹文件操作，确保异常发生时也能正确关闭文件句柄。

6. 增量保存：长时间运行的任务应定期保存中间结果，防止程序崩溃导致全部数据丢失。

7. 单元测试：对关键的Excel生成逻辑编写单元测试，验证生成的文件结构和数据正确性。

8. 版本锁定：在 requirements.txt 中锁定 openpyxl 版本，避免因升级导致的API不兼容。

完整的异常处理模板

from openpyxl import Workbook, load_workbook
from openpyxl.utils.exceptions import InvalidFileException
import logging

# 配置日志
logging.basicConfig(level=logging.INFO,
                    format='%(asctime)s - %(levelname)s - %(message)s')
logger = logging.getLogger(__name__)

class ExcelManager:
    """Excel文件管理器（包含完整的异常处理）"""

    @staticmethod
    def safe_open(filepath, **kwargs):
        """安全地打开Excel文件"""
        try:
            wb = load_workbook(filepath, **kwargs)
            logger.info(f'成功打开文件: {filepath}')
            return wb
        except FileNotFoundError:
            logger.error(f'文件不存在: {filepath}')
            raise
        except InvalidFileException:
            logger.error(f'无效的Excel文件格式: {filepath}')
            raise
        except Exception as e:
            logger.error(f'打开文件异常: {e}')
            raise

    @staticmethod
    def safe_save(wb, filepath):
        """安全地保存Excel文件"""
        try:
            # 确保目标目录存在
            import os
            os.makedirs(os.path.dirname(filepath) or '.', exist_ok=True)

            wb.save(filepath)
            logger.info(f'文件已保存: {filepath}')
        except PermissionError:
            logger.error(f'文件写入权限不足: {filepath}')
            raise
        except OSError as e:
            logger.error(f'文件保存失败: {e}')
            raise

    @staticmethod
    def safe_close(wb):
        """安全地关闭工作簿"""
        try:
            wb.close()
            logger.debug('工作簿已关闭')
        except Exception as e:
            logger.warning(f'关闭工作簿时发生异常（可忽略）: {e}')

    @classmethod
    def process_excel(cls, filepath, process_func, mode='standard', **kwargs):
        """
        通用的Excel处理模板

        用法:
            def my_process(ws):
                ws['A1'] = 'Hello'

            ExcelManager.process_excel('test.xlsx', my_process)
        """
        wb = None
        try:
            if mode == 'new':
                wb = Workbook()
                ws = wb.active
            else:
                wb = cls.safe_open(filepath, **kwargs)
                ws = wb.active

            process_func(ws)
            cls.safe_save(wb, filepath)

        except Exception as e:
            logger.error(f'处理Excel文件失败: {e}')
            raise
        finally:
            if wb:
                cls.safe_close(wb)

# 使用示例
def demo_handler(ws):
    ws['A1'] = 'Excel处理模板演示'
    ws['A2'] = '自动管理打开、保存和关闭'

# ExcelManager.process_excel('demo.xlsx', demo_handler, mode='new')
print('ExcelManager 类已定义，提供了完整的异常处理机制')