首页 › Python 实用脚本 › 用 openpyxl 处理 Excel

用 openpyxl 处理 Excel

办公中大量数据存放在 Excel 里。openpyxl 可以读写 .xlsx 文件,适合做报表自动化。

$ pip install openpyxl

创建工作簿

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

wb = Workbook()
ws = wb.active
ws.title = "销售汇总"

ws.append(["地区", "1月", "2月", "3月", "合计"])
rows = [("华东", 120, 150, 168), ("华南", 98, 110, 135), ("华北", 87, 95, 102)]
for r in rows:
    ws.append(list(r))

for row in range(2, ws.max_row + 1):
    ws.cell(row=row, column=5, value=f"=SUM(B{row}:D{row})")   # 写入公式

header_font = Font(bold=True, color="FFFFFF")
header_fill = PatternFill("solid", fgColor="3A8C5E")
for cell in ws[1]:
    cell.font = header_font
    cell.fill = header_fill
    cell.alignment = Alignment(horizontal="center")

ws.column_dimensions["A"].width = 12
wb.save("销售汇总.xlsx")

读取工作簿

from openpyxl import load_workbook

wb = load_workbook("销售汇总.xlsx", data_only=True)   # data_only 读取公式的计算结果
ws = wb["销售汇总"]

for row in ws.iter_rows(min_row=2, values_only=True):
    region, *months, total = row
    print(region, months)

注意:用 openpyxl 写入的公式没有计算结果,必须用 Excel 打开并保存一次后,data_only=True 才能读到数值。

合并多个文件

from pathlib import Path
from openpyxl import Workbook, load_workbook

merged = Workbook()
out = merged.active
out.append(["来源文件", "姓名", "金额"])

for path in sorted(Path("reports").glob("*.xlsx")):
    ws = load_workbook(path, read_only=True).active
    for row in ws.iter_rows(min_row=2, values_only=True):
        out.append([path.stem, *row[:2]])

merged.save("合并结果.xlsx")

与 Pandas 配合

数据处理逻辑复杂时,用 Pandas 计算、用 openpyxl 调整格式是常见的组合:

import pandas as pd

df = pd.read_excel("销售汇总.xlsx")
summary = df.groupby("地区").sum(numeric_only=True)
with pd.ExcelWriter("分析结果.xlsx", engine="openpyxl") as writer:
    summary.to_excel(writer, sheet_name="汇总")

练习题

学完本节后,试着独立完成下面的练习,再展开参考答案对照。

练习 1 用 openpyxl 创建一个九九乘法表的 Excel 文件。

查看参考答案
from openpyxl import Workbook
wb = Workbook()
ws = wb.active
for i in range(1, 10):
    for j in range(1, i + 1):
        ws.cell(row=i, column=j, value=f"{j}×{i}={i * j}")
wb.save("九九乘法表.xlsx")