使用 openpyxl 操作 .xlsx 文件：从入门到实战（创建、读取、样式、公式全解析）

你是否经常需要：

• 手动给 Excel 表格加粗标题、标红异常数据？
• 在多个工作表中填写相同模板？
• 插入 SUM、VLOOKUP 等公式却担心出错？

其实，这些重复性工作完全可以用 Python 自动完成！而 openpyxl 正是处理 .xlsx 文件的官方推荐库——它不仅能读写数据，还能精确控制单元格样式、插入公式、设置边框颜色，甚至生成专业级报表！

本文将带你系统掌握 openpyxl 的核心用法，涵盖：

• 创建/读取/保存 Excel 文件
• 单元格读写与批量操作
• 字体、对齐、边框、填充等样式设置
• 公式与函数插入
• 实战：自动生成带格式的销售日报

一、为什么选择 openpyxl？

结论：如果你需要精细控制 Excel 格式（如公司日报、财务报表），openpyxl 是不二之选！

二、安装与基础概念

1. 安装

pip install openpyxl

注意：仅支持 .xlsx（Office 2007+），不支持 .xls。

2. 核心对象关系

Workbook（工作簿）
└── Worksheet（工作表）
└── Cell（单元格）

• 一个 Excel 文件 = 1 个 Workbook
• 一个 Workbook 包含多个 Worksheet（如 Sheet1, Sheet2）
• 每个 Worksheet 由 Cell 组成，可通过坐标（如 A1）或行列号（row=1, column=1）访问

三、创建与保存 Excel 文件

1. 创建新文件

from openpyxl import Workbook

wb = Workbook() # 创建新工作簿
ws = wb.active # 获取默认工作表（Sheet）
ws.title = "销售日报" # 重命名工作表

ws["A1"] = "产品名称"
ws["B1"] = "销售额"

wb.save("sales_report.xlsx") # 保存文件
print("文件已创建！")

2. 读取现有文件

from openpyxl import load_workbook

wb = load_workbook("template.xlsx") # 加载现有文件
ws = wb["数据表"] # 指定工作表名

print(ws["A1"].value) # 读取 A1 单元格值

四、单元格操作：读、写、遍历

1. 写入数据（多种方式）

ws["C1"] = 100 # 方式1：坐标赋值
ws.cell(row=1, column=4, value="备注") # 方式2：行列号赋值

# 批量写入（推荐用于列表/字典数据）
data = [
["手机", 5000],
["电脑", 8000],
["平板", 3000]
]
for row in data:
ws.append(row) # 追加到末尾

2. 读取数据

# 遍历所有有数据的单元格
for row in ws.iter_rows(values_only=True):
print(row)

# 获取最大行列
print("最大行:", ws.max_row)
print("最大列:", ws.max_column)

五、样式设置：让报表更专业

openpyxl 提供了丰富的样式类：

1. 导入样式模块

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

2. 设置标题样式（加粗+居中+背景色）

header_font = Font(name="微软雅黑", size=12, bold=True, color="FFFFFF")
header_fill = PatternFill(start_color="4F81BD", end_color="4F81BD", fill_type="solid")
header_align = Alignment(horizontal="center", vertical="center")

for col in ["A", "B", "C", "D"]:
cell = ws[f"{col}1"]
cell.font = header_font
cell.fill = header_fill
cell.alignment = header_align

3. 设置边框

thin_border = Border(
left=Side(style="thin"),
right=Side(style="thin"),
top=Side(style="thin"),
bottom=Side(style="thin")
)

# 为数据区域添加边框
for row in ws.iter_rows(min_row=1, max_row=ws.max_row, min_col=1, max_col=4):
for cell in row:
cell.border = thin_border

4. 条件格式（示例：金额 > 5000 标绿）

openpyxl 不直接支持 Excel 的“条件格式规则”，但可手动判断后设置样式：

for row in range(2, ws.max_row + 1):
amount = ws[f"B{row}"].value
if amount and amount > 5000:
ws[f"B{row}"].fill = PatternFill(start_color="C6EFCE", end_color="C6EFCE", fill_type="solid")

六、插入公式与函数

openpyxl 支持原生 Excel 公式！

# 在 C5 插入求和公式
ws["C5"] = "=SUM(B2:B4)"

# 插入平均值
ws["C6"] = "=AVERAGE(B2:B4)"

# 注意：公式以字符串形式写入，Excel 打开时会自动计算

公式在 Excel 中显示为 =SUM(…)，而非计算结果（符合预期）。

七、实战：自动生成带格式的销售日报

需求：

• 读取原始数据（可用 pandas 或列表）
• 生成带标题、边框、汇总公式的日报表
• 保存为 销售日报_20260225.xlsx

完整代码：

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

# 1. 准备数据
sales_data = [
["产品", "销售额"],
["手机", 5200],
["电脑", 8900],
["平板", 3100],
]

# 2. 创建工作簿
wb = Workbook()
ws = wb.active
ws.title = "销售日报"

# 3. 写入数据
for row in sales_data:
ws.append(row)

# 4. 添加汇总行
ws.append(["总计", f"=SUM(B2:B{len(sales_data)})"])

# 5. 设置样式
header_font = Font(bold=True, color="FFFFFF")
header_fill = PatternFill(start_color="4F81BD", end_color="4F81BD", fill_type="solid")
align_center = Alignment(horizontal="center", vertical="center")

# 标题行
for col in ["A", "B"]:
cell = ws[f"{col}1"]
cell.font = header_font
cell.fill = header_fill
cell.alignment = align_center

# 总计行加粗
ws["A4"].font = Font(bold=True)
ws["B4"].font = Font(bold=True)

# 边框
thin = Side(style="thin")
border = Border(top=thin, left=thin, right=thin, bottom=thin)
for row in ws.iter_rows(min_row=1, max_row=4, min_col=1, max_col=2):
for cell in row:
cell.border = border
cell.alignment = align_center

# 6. 保存
from datetime import datetime
today = datetime.now().strftime("%Y%m%d")
wb.save(f"销售日报_{today}.xlsx")
print("销售日报生成成功！")

生成效果：打开 Excel 后，你会看到一个带蓝色标题、边框、自动求和公式的专业报表！

八、常见问题与技巧

九、总结

openpyxl 是 Python 操作 .xlsx 文件的全能选手：

• 精准控制每个单元格的内容与样式
• 原生支持 Excel 公式
• 适合生成固定模板的业务报表

自动化不是为了炫技，而是把时间还给自己。

下次当同事还在手动调格式时，你只需运行脚本，一杯咖啡的时间，报表已就绪！

如果本文对你有帮助，欢迎点赞 、收藏 、关注我！
你希望用 Python 自动生成哪种 Excel 报表？留言告诉我，下期安排！

版权声明：本文为原创，转载请注明出处。代码可免费用于学习与商业项目。