首页 › 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")