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

Python 读写 Excel 文件（进阶篇）

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

在上一章我们学习了旧版 .xls 文件的读写方法，但如今主流报表、统计表格早已全面转向 Office 2007 及之后版本使用的 .xlsx XML 格式。处理这种格式的 Python 工具中，openpyxl 是当之无愧的首选——它读写一体、功能丰富，而且语法设计非常贴合 Excel 原生操作习惯。

相比旧工具组合 xlrd / xlwt 在读写时需要切换不同库、且仅支持各自负责的旧格式，openpyxl 统一了所有操作，优势十分明显：

  • 读写修改一气呵成：同一个工作簿对象，既可以读取已有内容，也可以修改、追加新数据，无需在多个库之间来回转换。
  • 单元格定位更直观：直接使用 'A1'、'C4' 这样的 Excel 坐标字符串，省去记忆行号列号的烦恼。
  • 功能覆盖非常全面：原生支持字体、边框、对齐、填充等样式定制；可以直接写入真正可计算的 Excel 公式；还能在文件中嵌入可交互的原生图表（图表本身是 Excel 对象，不是静态图片）。

⚠️ 格式限制
openpyxl 仅支持 .xlsx 和 .xlsm 格式，无法处理旧版 .xls 文件。如果仍需要处理 .xls，请参考上一章介绍的专用工具。

安装指令

pip install openpyxl

# 如果下载速度较慢，可以使用国内镜像源
pip install openpyxl -i https://pypi.tuna.tsinghua.edu.cn/simple

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

openpyxl 在读取文件时提供了非常灵活的方式。除了普通的行列索引，最让人喜欢的就是原生 Excel 坐标字符串——写代码时不用再心算行列对应的数字，可读性极强，后续维护也省心。

实战：读取股票历史数据

import datetime
import openpyxl

# 1. 加载工作簿（默认 read_only=False，支持读写）
wb = openpyxl.load_workbook('阿里巴巴2020年股票数据.xlsx')

# 获取第一个工作表（可通过 worksheets[0] 或 active 属性）
sheet = wb.active

# 快速查看有效数据范围，避免写死行列数
print(f"有效区域：{sheet.dimensions}")  # 例如输出 A1:G255
print(f"行数：{sheet.max_row}, 列数：{sheet.max_column}")

# 2. 多种读取单元格的方式
# ✅ 方式1：Excel 坐标字符串（推荐）
open_price = sheet['C2'].value

# 📌 方式2：cell 方法（行列索引从 1 开始，而不是 0！）
close_price = sheet.cell(row=2, column=5).value

# ⚙️ 方式3：直接遍历整列或整行切片
c_col_cells = sheet['C']          # 获取 C 列所有单元格对象
dates = sheet['A2:A10']           # 获取指定区域的单元格对象

# 3. 高效遍历并处理数据（values_only=True 直接获取纯值，节省内存）
for row in sheet.iter_rows(min_row=2, values_only=True):
    for idx, value in enumerate(row):
        if isinstance(value, datetime.datetime):
            print(value.strftime('%Y-%m-%d'), end='\t')
        else:
            if idx >= 2:  # 假设第 2 列以后是数值，保留两位小数
                print(f"{value:.2f}\t", end='')
            else:
                print(f"{value}\t", end='')
    print()

小提示：sheet.dimensions 可以快速告诉你数据占用的矩形范围，例如 A1:G255，这在调试时非常有用。

3. 写入与样式定制：让报表专业又美观

openpyxl 将各种样式（字体、对齐、边框、填充等）设计成了独立的“积木块”，我们可以分别定义好，再按需组装到单元格上，实现高度可复用的样式模板。

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

# 新建工作簿（默认包含一个名为 Sheet 的工作表）
wb = openpyxl.Workbook()
sheet = wb.active
sheet.title = '高一1班期中成绩'

# ✨ 预先定义样式“积木”，方便后续重复使用
header_font = Font(size=14, bold=True, color='FFFFFF', name='微软雅黑')
header_fill = PatternFill(fill_type='solid', fgColor='4472C4')  # 经典表头蓝
center_align = Alignment(horizontal='center', vertical='center', wrap_text=True)
thin_side = Side(style='thin', color='000000')
full_border = Border(left=thin_side, top=thin_side, right=thin_side, bottom=thin_side)

# 1. 写入表头并应用样式
headers = ['姓名', '语文', '数学', '英语', '总分']
for col_idx, header_text in enumerate(headers, 1):
    cell = sheet.cell(1, col_idx, header_text)
    cell.font = header_font
    cell.fill = header_fill
    cell.alignment = center_align
    cell.border = full_border

# 2. 写入学生成绩数据
students = [
    ['张三', 92, 88, 95],
    ['李四', 85, 96, 90],
    ['王五', 78, 82, 87]
]
for row_idx, student_data in enumerate(students, 2):
    for col_idx, score in enumerate(student_data, 1):
        cell = sheet.cell(row_idx, col_idx, score)
        cell.alignment = center_align
        cell.border = full_border
        # 如果成绩低于 80 分，用红色背景提醒
        if isinstance(score, int) and score < 80:
            cell.fill = PatternFill(fill_type='solid', fgColor='FFC7CE')

# 3. 写入 Excel 原生公式（以 '=' 开头，Excel 打开后会自动计算结果）
for row_idx in range(2, 5):
    sheet.cell(row_idx, 5).value = f'=SUM(B{row_idx}:D{row_idx})'
    # 同时为公式单元格加上边框和对齐
    sheet.cell(row_idx, 5).alignment = center_align
    sheet.cell(row_idx, 5).border = full_border

# 4. 简单自动调整列宽（根据单元格内容长度大致估算）
for col in sheet.columns:
    max_length = 0
    column_letter = col[0].column_letter
    for cell in col:
        try:
            if len(str(cell.value)) > max_length:
                max_length = len(str(cell.value))
        except:
            pass
    adjusted_width = (max_length + 2) * 1.2
    sheet.column_dimensions[column_letter].width = adjusted_width

wb.save('高一1班期中成绩_美化版.xlsx')

提醒：公式中的函数名和标点符号必须使用英文格式，中文标点会导致公式写入失败。

4. 生成专业原生 Excel 图表

这是 openpyxl 最受办公与报表场景欢迎的核心功能之一——它生成的不是静态图片，而是真正可交互的 Excel 图表对象。打开文件后，可以随意点击图表、编辑数据源、更改配色甚至切换图表类型（例如从柱状图换成折线图，只需几次点击）。

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

wb = Workbook()
sheet = wb.active
sheet.title = '电商周销榜'

# 1. 准备销售数据
week_sales = [
    ['商品', '销量'],
    ['无线蓝牙耳机', 2100],
    ['智能手环', 1850],
    ['降噪耳机', 3200],
    ['笔记本支架', 2700]
]
for row in week_sales:
    sheet.append(row)

# 2. 创建柱状图（可选多种预设样式）
bar_chart = BarChart()
bar_chart.title = "本周热销榜 TOP4"
bar_chart.y_axis.title = '销量'
bar_chart.x_axis.title = '商品'
bar_chart.style = 10  # Excel 内置的渐变柱形图样式

# 3. 引用数据区域
values = Reference(sheet, min_col=2, min_row=1, max_row=5)
categories = Reference(sheet, min_col=1, min_row=2, max_row=5)

bar_chart.add_data(values, titles_from_data=True)
bar_chart.set_categories(categories)

# 4. 将图表插入到工作表的 E2 单元格位置
sheet.add_chart(bar_chart, "E2")

wb.save('电商周销榜_带原生图.xlsx')

如果需要制作折线图，只需将 BarChart 替换为 LineChart，其余用法几乎相同，非常方便。

5. 总结与避坑建议

openpyxl 是处理现代 .xlsx 文件的首选库，它非常适合需要精细控制样式、写入公式或生成图表的场景。如果你只是进行大规模数据清洗与分析，Pandas 通常更高效，不过 Pandas 在保存 .xlsx 时底层也常常依赖 openpyxl 引擎。

实用避坑提醒

  1. 行列索引从 1 开始：这一点与许多编程习惯不同，如果记混了可能导致取错数据。
  2. 大数据量记得开只读/只写模式：读取百万行数据时请使用 load_workbook(…, read_only=True)，写入大量数据时使用 Workbook(write_only=True)，以大幅降低内存占用。
  3. 公式一定要用英文标点：哪怕整个文件内容都是中文，公式部分也必须是半角英文符号，否则 openpyxl 会报错。
  4. 图表数据引用小心行列范围：Reference 的 min_row、max_row 一定要与数据源准确对应，否则图表可能显示异常。

掌握了这些技巧，你就能用 Python 高效地生成结构清晰、样式美观且带有专业图表的 Excel 报表，真正实现办公自动化。