第 9 周：`openpyxl` 工作簿基础

本周进入 Excel 自动化。重点不是记住所有 openpyxl API，而是建立“工作簿、工作表、单元格、值、公式、保存”之间的对象关系，并能安全读取和修改文件。

建议用时：8-10 小时。

本周目标#

学完本周，你应该能够：

解释工作簿、工作表、行、列、单元格之间的关系。
加载已有工作簿并选择工作表。
使用 A1 坐标和数字行列访问单元格。
读取表头和数据区域。
创建新工作簿、工作表和追加数据。
修改单元格内容并写入公式字符串。
设置基本的表头样式和列宽。
另存工作簿，确保不覆盖输入原件。
单独测试公式、行号和数据范围。

9.1 Excel 对象层级#

一个 Excel 文件通常包含：

工作簿 workbook
├── 工作表 sheet
│   ├── 行 row
│   ├── 列 column
│   └── 单元格 cell
└── 另一个工作表

对应代码：

from openpyxl import load_workbook

workbook = load_workbook("students.xlsx")
worksheet = workbook["成绩"]
cell = worksheet["A1"]

print(workbook.sheetnames)
print(worksheet.title)
print(cell.value)

常见命名：

wb：workbook，工作簿。
ws：worksheet，工作表。
ws1、grade_ws：具体工作表。

名称短可以少打字，但复杂项目应使用能说明业务的名称。

9.2 加载已有工作簿#

from openpyxl import load_workbook

wb = load_workbook("students.xlsx")
print(wb.sheetnames)

默认 load_workbook() 读取公式字符串，而不是计算后的值。data_only 参数在第 9.11 节解释。

选择工作表#

ws = wb["成绩"]

工作表名称必须与文件中一致，包括空格和中文。名称不存在时会报 KeyError。

也可以按索引选择：

first_sheet = wb.worksheets[0]

或者使用当前活动表：

active_sheet = wb.active

按名称选择更清楚。除非题目明确说“第一张表”，否则不要假设活动表就是目标表。

遍历工作表名称#

for sheet_name in wb.sheetnames:
    print(sheet_name)

9.3 单元格坐标#

Excel 坐标由列字母和行号组成：

A1  第 1 行第 1 列
B2  第 2 行第 2 列
C5  第 5 行第 3 列

ws["A1"] = "学号"
print(ws["A1"].value)  # 学号

ws["A1"] 返回单元格对象。真正保存的数据在 .value 中。

使用数字行列#

cell = ws.cell(row=2, column=3)
print(cell.value)

ws.cell(row=2, column=4, value=90)

行列编号从 1 开始，不是从 0 开始。row=2 是 Excel 第 2 行，column=3 是 C 列。

列字母与列号转换#

from openpyxl.utils import get_column_letter

print(get_column_letter(1))  # A
print(get_column_letter(26)) # Z
print(get_column_letter(27)) # AA

动态向整行写公式时，不必手写每个字母，可以根据列号生成。

9.4 读取表格区域#

输入工作簿“成绩”表有 11 行、6 列：

学号 | 姓名 | 班级 | 信息科技 | 数学 | 英语
S001 | 张晨 | 高一(1)班 | 86 | 91 | 78
...
S010 | 钱可 | 高一(2)班 | 84 | 80 | 86

读取行范围：

ws = wb["成绩"]

for row in ws.iter_rows(
    min_row=2,
    max_row=ws.max_row,
    min_col=1,
    max_col=6,
    values_only=True,
):
    student_id, name, class_name, it_score, math_score, english_score = row
    print(student_id, name, it_score + math_score + english_score)

参数：

min_row：最小行号，包含。
max_row：最大行号，包含。
min_col、max_col：列范围，包含。
values_only=True：每行直接返回值的元组，不返回单元格对象。

`max_row` 和 `max_column`#

print(ws.max_row)     # 11
print(ws.max_column)  # 6

它们表示工作表当前有数据的最大边界。某些带格式的空单元格也可能扩大边界，因此关键程序还应检查值是否为空。

按单元格读取#

for row_index in range(2, ws.max_row + 1):
    student_id = ws.cell(row=row_index, column=1).value
    name = ws.cell(row=row_index, column=2).value
    print(row_index, student_id, name)

range(2, ws.max_row + 1) 才能包含最后一行。例如 max_row 是 11 时，range(2, 12) 产生 2 到 11。

9.5 修改单元格#

ws["B2"] = "张晨"
ws["D2"] = 90
ws["G2"] = "=SUM(D2:F2)"

写入数字时可以直接赋值整数或浮点数；写入公式时必须使用以 = 开头的字符串。

添加表头#

ws["G1"] = "总分"
ws["H1"] = "平均分"

增加和删除行列#

ws.insert_rows(3)
ws.delete_rows(5)
ws.insert_cols(2)
ws.delete_cols(4)

插入和删除会移动其他单元格，公式中的引用是否自动调整取决于 Excel 和具体操作。竞赛中应先确认题目要求，不要随意整理原表结构。

9.6 创建新工作簿#

from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.title = "学生信息"

ws.append(["学号", "姓名", "班级", "分数"])
ws.append(["S001", "张晨", "高一(1)班", 88])
ws.append(["S002", "李雨", "高一(2)班", 95])

wb.save("students_new.xlsx")

新建工作簿默认包含一个工作表。wb.active 取到当前活动表，append() 在下一空白行追加一组值。

追加多行：

rows = [
    ["S003", "王浩", "高一(1)班", 72],
    ["S004", "陈欣", "高一(2)班", 90],
]

for row in rows:
    ws.append(row)

append() 每调用一次写一行，不会自动跳过已存在的非连续空行。

9.7 创建、删除和复制工作表#

summary_ws = wb.create_sheet("统计")
detail_ws = wb.create_sheet("明细", 1)

第二个参数是插入位置索引。省略时通常放在末尾。

del wb["临时表"]
wb.remove(wb["另一个临时表"])

删除不存在的表会报 KeyError。真正执行前先检查名称。

复制工作表：

copied_ws = wb.copy_worksheet(ws)
copied_ws.title = "数据副本"

复制结果会加入同一个工作簿。第 10 周会把它用于第二阶段综合题。

9.8 写入公式#

公式是字符串：

ws["G2"] = "=SUM(D2:F2)"
ws["H2"] = "=AVERAGE(D2:F2)"

按行批量填写：

for row_index in range(2, ws.max_row + 1):
    ws[f"G{row_index}"] = f"=SUM(D{row_index}:F{row_index})"
    ws[f"H{row_index}"] = f"=AVERAGE(D{row_index}:F{row_index})"

关键点：

公式必须是以 = 开头的字符串。
行列引用不能全部写死为第 2 行。
Python 的 f-string 会替换花括号中的 row_index。

最终单元格内容类似：

=SUM(D2:F2)
=SUM(D3:F3)
...

`openpyxl` 不会替你计算公式#

保存后，文件中记录的是公式字符串。用 Excel 或 WPS 打开时，软件会计算公式。程序再次读取时，如果没有缓存计算值，可能看到公式字符串或 None。这是正常行为。

如果只是用 Python 检查公式是否正确，保留默认 data_only=False：

wb_formula = load_workbook("result.xlsx", data_only=False)
print(wb_formula["成绩"]["G2"].value)  # =SUM(D2:F2)

9.9 基本样式#

竞赛通常更重视数据正确，但如果要求“表头清晰”，可以设置基础样式：

from openpyxl.styles import Alignment, Font, PatternFill

header_fill = PatternFill("solid", fgColor="D9EAF7")

for cell in ws[1]:
    cell.font = Font(bold=True)
    cell.fill = header_fill
    cell.alignment = Alignment(horizontal="center", vertical="center")

调整列宽：

ws.column_dimensions["A"].width = 12
ws.column_dimensions["B"].width = 16

冻结首行：

ws.freeze_panes = "A2"

样式是可选的表达方式，不能代替正确数据和公式。先完成功能，再处理外观。

9.10 保存与关闭#

output_path = "output/students_updated.xlsx"
wb.save(output_path)
wb.close()

save() 才是真正写出文件。只在内存中修改 ws 而不调用 save()，原文件不会被改变，也不会生成输出。

保存前确认：

输出目录存在。
输出文件名符合题目要求。
输出路径不是输入路径。
需要的工作表都存在。
公式和数据范围覆盖最后一行。

如果目标文件已在 Excel 中打开，Windows 上可能因文件被占用而保存失败。先关闭目标工作簿再运行程序。

9.11 `data_only` 参数#

wb_formula = load_workbook("scores.xlsx", data_only=False)
wb_value = load_workbook("scores.xlsx", data_only=True)

data_only=False：读取公式字符串。
data_only=True：尝试读取上次由 Excel 计算并缓存的值。

如果文件从未被 Excel/WPS 打开计算并保存，公式缓存值可能是 None。所以不能简单认为 data_only=True 一定会算出公式。

考试检查公式时通常应使用默认方式加载，确认单元格中是 =SUM(...) 等字符串。

9.12 读取时不要修改原数据#

常见错误：

wb = load_workbook(input_path)
ws = wb["成绩"]
ws["G1"] = "总分"
wb.save(input_path)

这会直接覆盖原始输入。更安全：

output_path = "output/第二阶段_成绩.xlsx"
...
wb.save(output_path)

保留输入原件，才能反复测试和对比。

9.13 本周作品：学生信息工作簿编辑器#

需求#

创建一个包含“学号、姓名、班级、信息科技、数学、英语”的工作簿，然后：

读取每个学生。
查找指定学号，修改某科成绩。
追加一名新学生。
增加“总分”列。
另存为新文件。

参考程序#

import os

from openpyxl import load_workbook

input_path = "students.xlsx"
output_path = "output/students_updated.xlsx"

os.makedirs("output", exist_ok=True)

try:
    wb = load_workbook(input_path)
    ws = wb["学生信息"]

    target_id = "S002"
    new_math_score = 96
    found = False

    for row_index in range(2, ws.max_row + 1):
        student_id = ws.cell(row=row_index, column=1).value
        if student_id == target_id:
            ws.cell(row=row_index, column=5, value=new_math_score)
            found = True
            break

    if not found:
        print("没有找到学号：", target_id)

    next_row = ws.max_row + 1
    ws.cell(row=next_row, column=1, value="S003")
    ws.cell(row=next_row, column=2, value="王浩")
    ws.cell(row=next_row, column=3, value="高一(1)班")
    ws.cell(row=next_row, column=4, value=75)
    ws.cell(row=next_row, column=5, value=82)
    ws.cell(row=next_row, column=6, value=79)

    ws.cell(row=1, column=7, value="总分")
    for row_index in range(2, ws.max_row + 1):
        ws.cell(
            row=row_index,
            column=7,
            value=f"=SUM(D{row_index}:F{row_index})",
        )

    wb.save(output_path)
    wb.close()
    print("已保存：", output_path)

except FileNotFoundError:
    print("错误：找不到输入工作簿", input_path)
except KeyError:
    print("错误：工作簿中没有“学生信息”工作表")

在使用前需要有符合结构的 students.xlsx。可以先用下面的程序创建练习输入：

from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.title = "学生信息"

ws.append(["学号", "姓名", "班级", "信息科技", "数学", "英语"])
ws.append(["S001", "张晨", "高一(1)班", 86, 91, 78])
ws.append(["S002", "李雨", "高一(1)班", 92, 88, 90])

wb.save("students.xlsx")
wb.close()

验证输出#

输入文件与输出文件都存在。
输入文件内容没有被改变。
S002 的数学成绩确实为 96。
S003 追加到最后一行之后。
G 列每一行都有对应公式。
公式行号与学生行号一致。
工作簿可以被 Excel/WPS 正常打开。

9.14 独立练习#

A. 快速判断题#

ws["B3"] 是第几行第几列？
ws.cell(row=4, column=2) 是哪个坐标？
单元格中的数据写在哪里？
ws.max_row 为 10 时，最后一行行号是多少？
写入公式时，公式字符串以什么符号开头？
wb.save() 后是否还需要手工保存？
data_only=True 为什么可能读到 None？
加载输入工作簿后修改并 save(input_path) 有什么风险？
新工作簿默认是否有一张工作表？
copy_worksheet() 复制到哪个工作簿中？

B. 编程题#

读取“成绩”表第 2 行到最后一行的 A 列和 B 列。
在 G1、H1 写入“总分”“平均分”。
为每个学生填写总分和平均分公式。
创建“统计”工作表。
把表头设为粗体并调整 A 列宽度。
读取所有学生姓名，输出没有姓名的数据行号。
修改一个学生的英语分数，保存为 updated.xlsx。
复制“成绩”表并命名为“数据副本”。

9.15 练习答案与解释#

A. 快速判断题答案#

第 3 行第 2 列。
B4。
单元格对象的 .value 属性。
10。
=。
不需要。save() 已经是写出操作，但仍应调用 close() 释放对象。
文件中的公式尚未被 Excel/WPS 计算并保存缓存值。
会覆盖原始数据，失去对照，且失败时可能损坏输入文件。
是，默认有一张工作表。
原工作簿 wb。

B. 编程题参考答案#

读取 A、B 两列：

for student_id, name in ws.iter_rows(
    min_row=2,
    max_row=ws.max_row,
    min_col=1,
    max_col=2,
    values_only=True,
):
    print(student_id, name)

写入表头：

ws["G1"] = "总分"
ws["H1"] = "平均分"

批量公式：

for row_index in range(2, ws.max_row + 1):
    ws[f"G{row_index}"] = f"=SUM(D{row_index}:F{row_index})"
    ws[f"H{row_index}"] = f"=AVERAGE(D{row_index}:F{row_index})"

创建统计表：
```
summary_ws = wb.create_sheet("统计")
```

表头样式和列宽：

from openpyxl.styles import Font

for cell in ws[1]:
    cell.font = Font(bold=True)
ws.column_dimensions["A"].width = 14

找出空姓名行：

for row_index in range(2, ws.max_row + 1):
    name = ws.cell(row=row_index, column=2).value
    if name is None or str(name).strip() == "":
        print("姓名为空，行号：", row_index)

修改英语成绩：

for row_index in range(2, ws.max_row + 1):
    if ws.cell(row=row_index, column=1).value == "S002":
        ws.cell(row=row_index, column=6, value=95)
        break

wb.save("updated.xlsx")