Python 读写 Excel 文件-2 (openpyxl 进阶)

Python 读写 Excel 文件（进阶篇）

1. openpyxl 简介：现代办公自动化的首选

在上一章中，我们学习了处理旧版 .xls 文件的工具。而对于现代标准的 .xlsx 文件，openpyxl 是目前 Python 生态中最流行、功能最全的库。

与 xlrd/xlwt 组合相比，openpyxl 具有显著优势：

  • 读写合一：同一个对象既可以读取数据，也可以直接修改并保存。
  • 语法直观：支持像操作 Excel 公式一样直接通过 A1、B2 这种坐标定位单元格。
  • 功能强大：原生支持样式编辑、公式注入、数据透视以及直接绘制原生 Excel 图表。

注意：openpyxl 专门为 Office 2007 及更高版本的 XML 格式设计，不支持古老的 .xls 文件。

安装指令

pip install openpyxl

2. 读取 Excel 文件：灵活的坐标定位

openpyxl 提供了多种方式定位数据。除了传统的行列索引外，直接使用 Excel 坐标字符串 是其一大特色。

实战：读取股票数据

import datetime
import openpyxl

# 加载工作簿
wb = openpyxl.load_workbook('阿里巴巴2020年股票数据.xlsx')
sheet = wb.worksheets[0]

# 1. 快速获取属性
print(f"有效区域: {sheet.dimensions}") # 例如 A1:G255
print(f"最大行: {sheet.max_row}, 最大列: {sheet.max_column}")

# 2. 多样化的单元格访问
# 方式 A: 通过 cell 方法 (注意：索引从 1 开始)
val1 = sheet.cell(row=3, column=3).value
# 方式 B: 通过坐标索引 (最推荐，符合 Excel 习惯)
val2 = sheet['C3'].value

# 3. 切片操作：获取一个区域的单元格
cells_range = sheet['A2:C5'] # 返回嵌套元组

# 4. 遍历处理不同类型数据
for row in sheet.iter_rows(min_row=2, values_only=True):
    # row 是一个包含当前行所有值的元组
    for value in row:
        if isinstance(value, datetime.datetime):
            print(value.strftime('%Y-%m-%d'), end='\t')
        else:
            print(f"{value}\t", end='')
    print()

3. 写入与样式定制

在 Daoman Python AI 的报表开发中，整洁的样式是专业性的体现。openpyxl 将样式细分为 Font（字体）、Alignment（对齐）、Border（边框）等模块。

import openpyxl
from openpyxl.styles import Font, Alignment, Border, Side

wb = openpyxl.Workbook()
sheet = wb.active
sheet.title = '成绩统计'

# 设置样式模板
header_font = Font(size=14, bold=True, color='FF0000', name='微软雅黑')
center_align = Alignment(horizontal='center', vertical='center')
thin_side = Side(style='thin', color='000000')
border = Border(left=thin_side, top=thin_side, right=thin_side, bottom=thin_side)

# 写入数据并应用样式
headers = ['姓名', '分数', '评级']
for col, text in enumerate(headers, 1):
    cell = sheet.cell(1, col, text)
    cell.font = header_font
    cell.alignment = center_align
    cell.border = border

# 写入公式：计算平均分
# openpyxl 会将字符串开头的 '=' 识别为 Excel 公式
sheet['B2'] = 85
sheet['B3'] = 90
sheet['B4'] = '=AVERAGE(B2:B3)'

wb.save('自动化报表.xlsx')

4. 生成专业统计图表

这是 openpyxl 最强大的功能之一。它生成的不是图片，而是原生的 Excel 图表对象，用户打开 Excel 后依然可以点击图表查看数据源。

from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference

wb = Workbook()
sheet = wb.active

# 准备数据
data_rows = [
    ['商品', '销量'],
    ['手机', 5000],
    ['平板', 3000],
    ['笔记本', 4500]
]
for row in data_rows:
    sheet.append(row)

# 1. 创建柱状图对象
chart = BarChart()
chart.title = "产品销量统计"
chart.y_axis.title = '数量'
chart.x_axis.title = '类别'

# 2. 引用数据范围 (注意包含标题或排除标题的设置)
values = Reference(sheet, min_col=2, min_row=1, max_row=4)
categories = Reference(sheet, min_col=1, min_row=2, max_row=4)

# 3. 绑定数据
chart.add_data(values, titles_from_data=True)
chart.set_categories(categories)

# 4. 插入到指定位置
sheet.add_chart(chart, "E2")

wb.save('销量分析图.xlsx')

5. 总结与建议

  • openpyxl 是处理 .xlsx 文件的全能选手，尤其适合需要保留 Excel 原生功能（如公式、图表）的场景。
  • 坐标访问 (sheet['A1']) 比行列索引更易读，建议优先使用。
  • 性能提醒：如果需要处理超大规模（数百万行）的数据，且不关心样式和图表，建议切换到 Pandas。Pandas 底层虽然也调用 openpyxl，但在纯数据计算和转换效率上具有压倒性优势。