Python自动化办公：用openpyxl处理Excel的10个实战技巧

为什么选 openpyxl

Python 处理 Excel 的库有不少：pandas 适合数据分析，openpyxl 适合 保留原格式 的读写操作。如果你需要批量修改现有 Excel 文件（而不是从头生成），openpyxl 是唯一选择。

技巧 1：批量读取指定列

from openpyxl import load_workbook
wb = load_workbook('数据.xlsx')
ws = wb.active
# 读取 A 列到第 10 行
values = [ws[f'A{i}'].value for i in range(1, 11)]

技巧 2：按条件筛选并高亮

把金额大于 10000 的单元格标红：

from openpyxl.styles import Font
red = Font(color='FF0000', bold=True)
for row in ws.iter_rows(min_row=2, max_col=4, max_row=ws.max_row):
    if row[3].value and row[3].value > 10000:
        row[3].font = red

技巧 3：批量合并多个工作表

把同文件夹下所有 Excel 的第一个 sheet 合并到一个文件：

import os, glob
from openpyxl import load_workbook, Workbook

target_wb = Workbook()
target_ws = target_wb.active
target_ws.title = "合并结果"

for file in glob.glob("*.xlsx"):
    src_wb = load_workbook(file)
    src_ws = src_wb.active
    for row in src_ws.iter_rows(values_only=True):
        target_ws.append(row)

技巧 4：自动调整列宽

from openpyxl.utils import get_column_letter
for col in ws.columns:
    max_len = 0
    col_letter = get_column_letter(col[0].column)
    for cell in col:
        if cell.value:
            max_len = max(max_len, len(str(cell.value)))
    ws.column_dimensions[col_letter].width = max_len + 6

技巧 5：插入公式而非值

# 写入 Excel 公式，打开文件时会自动计算
ws['E2'] = '=SUM(A2:D2)'
ws['F2'] = '=IF(E2>10000," 高 "," 低 ")'

技巧 6：批量生成图表

from openpyxl.chart import BarChart, Reference
chart = BarChart()
data = Reference(ws, min_col=2, min_row=1, max_row=10)
chart.add_data(data, titles_from_data=True)
ws.add_chart(chart, "H2")

技巧 7：保护特定单元格

# 先解锁所有单元格，再锁定特定区域
from openpyxl.styles import Protection
for row in ws.iter_rows():
    for cell in row:
        cell.protection = Protection(locked=False)
# 锁定表头
for cell in ws[1]:
    cell.protection = Protection(locked=True)
ws.protection.password = '123456'

技巧 8：批量替换文本

for row in ws.iter_rows():
    for cell in row:
        if cell.value and isinstance(cell.value, str):
            cell.value = cell.value.replace('旧文本', '新文本')

技巧 9：按 sheet 名批量操作

for sheet_name in wb.sheetnames:
    ws = wb[sheet_name]
    print(f"处理工作表: {sheet_name}, 共 {ws.max_row} 行")

技巧 10：保存为 PDF（需安装额外库）

# 需要先 pip install pywin32（仅 Windows）import win32com.client
excel = win32com.client.Dispatch('Excel.Application')
wb = excel.Workbooks.Open(r'C:\ 完整路径 \ 文件.xlsx')
wb.ExportAsFixedFormat(0, r'C:\ 完整路径 \ 输出.pdf')

总结

openpyxl 的强大之处在于 它能完整保留原文件的公式、格式、图表，这是 pandas 做不到的。对于日常办公自动化，这 10 个技巧覆盖了 80% 的场景。