Python使用openpyxl自动化处理Excel数据与文件详解

花开有时

发布时间：2026-10-10 | 4人浏览过

原创

职场中重复的数据录入与报表汇总极易出错且耗时。本文演示如何利用Python的openpyxl库完成Excel自动化流程，涵盖工作簿加载、单元格读写、公式写入、批量样式美化、工作表管理及动态图表嵌入，并提供模板复用策略与常见避坑指南，帮助读者快速构建稳定可靠的自动化办公脚本。

为什么选择 openpyxl 及环境准备

在Python生态中，处理现代Excel文件（.xlsx）且需兼顾数据读写、样式保留与图表操作的场景下，openpyxl是最常用的通用方案。该库原生支持Excel 2010及以上版本，修改单元格时可完整保留原有字体、颜色、边框与内置公式。对于内存敏感的大文件，它还提供只读与只写模式以优化性能。

开始前请确保开发环境已安装对应依赖：

pip install openpyxl

基础操作：创建工作簿与工作表管理

第1步：加载工作簿并提取单元格数据

使用load_workbook()打开目标文件后，可通过名称或直接获取活动表。读取指定单元格时推荐使用行列索引法，遍历全部数据则建议使用iter_rows(values_only=True)以提升执行效率。操作结束后务必调用workbook.close()释放系统资源。

from openpyxl import load_workbook

workbook = load_workbook('销售数据.xlsx')
sheet = workbook.active

cell_a1 = sheet['A1'].value
print(f"A1单元格的内容是：{cell_a1}")

cell_b2 = sheet.cell(row=2, column=2).value
print(f"B2单元格的内容是：{cell_b2}")

print("--- 工作表全部数据 ---")
for row in sheet.iter_rows(values_only=True):
    print(row)

workbook.close()

第2步：新建工作簿并写入数据与计算公式

使用Workbook()实例化新文件后，通过append()方法按行追加表头与业务数据。需要Excel引擎自动计算时，直接在目标单元格赋值对应的英文函数字符串即可。保存时使用独立文件名以避免覆盖源文件。

from openpyxl import Workbook

workbook = Workbook()
sheet = workbook.active
sheet.title = "手机销售数据"

headers = ['销售员', '产品', '销量', '单价', '总价']
sheet.append(headers)

raw_data = [
    ['张三', 'iPhone 15', 10, 6000],
    ['李四', '小米14', 15, 4000],
    ['王五', '华为Mate 60', 8, 7000],
]
sheet.append(raw_data)

for i in range(2, 5):
    sheet[f'E{i}'] = f'=C{i}*D{i}'

workbook.save('手机销售报表_生成.xlsx')
print("报表生成成功！")

第3步：复制、创建与删除工作表

复杂报表常需多页维护。可通过create_sheet(name, position)指定插入位置，利用copy_worksheet(source)克隆结构，并通过字典键删除冗余Sheet。此功能在批量套用固定模板时尤为高效。

from openpyxl import Workbook

workbook = Workbook()
sheet1 = workbook.create_sheet('产品销售')
sheet2 = workbook.create_sheet('销售员绩效', 0)

print(workbook.sheetnames)

del workbook['Sheet']

if '产品销售' in workbook.sheetnames:
    source_sheet = workbook['产品销售']
    workbook.copy_worksheet(source_sheet)

print(workbook.sheetnames)

workbook.save('工作表操作示例.xlsx')

样式控制：批量美化表格与格式化数值

第4步：统一设置表头样式、边框与列宽

借助openpyxl.styles模块可实现像素级排版控制。定义Font、PatternFill、Border与Alignment对象后，遍历目标区域批量赋值，最后调整各列字符宽度，使输出表格符合企业公文标准。

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

workbook = Workbook()
sheet = workbook.active
sheet.title = "销售数据报表"

headers = ['产品名称', '销量', '单价', '总价']
data = [
    ['键盘', 100, 120.00],
    ['鼠标', 150, 80.50],
    ['显示器', 50, 1200.00],
]
sheet.append(headers)
for row in data:
    sheet.append(row)

sheet['D2'] = '=B2*C2'
sheet['D3'] = '=B3*C3'
sheet['D4'] = '=B4*C4'

header_font = Font(name='微软雅黑', bold=True, size=12, color='000000FF')
header_fill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')
header_alignment = Alignment(horizontal='center', vertical='center')

for cell in sheet['1']:
    cell.font = header_font
    cell.fill = header_fill
    cell.alignment = header_alignment

thin_border = Border(
    left=Side(style='thin'),
    right=Side(style='thin'),
    top=Side(style='thin'),
    bottom=Side(style='thin')
)
for row in sheet.iter_rows(min_row=1, max_row=sheet.max_row, min_col=1, max_col=sheet.max_column):
    for cell in row:
        cell.border = thin_border

sheet.column_dimensions['A'].width = 20
sheet.column_dimensions['B'].width = 10
sheet.column_dimensions['C'].width = 15
sheet.column_dimensions['D'].width = 15

workbook.save('手机销售报表_美化.xlsx')
print("美化完成！")

第5步：配置货币格式与对齐方式

财务与业绩报表中，数值显示格式直接影响可读性。引入numbers.FORMAT_CURRENCY_CN可一键切换人民币符号与小数位，结合居中对齐指令，确保跨部门流转时的视觉一致性。

数据可视化与模板复用技巧

第6步：基于数据范围生成嵌入式柱状图

openpyxl支持在工作表内直接渲染图形对象。通过BarChart实例绑定目标列数据区（Reference），并关联分类轴标签，随后调用add_chart(chart, anchor_cell)将图形锚定于指定坐标。源数据变更时，保存文件后在Excel端刷新即可同步更新。

from openpyxl import load_workbook
from openpyxl.chart import BarChart, Reference

workbook = load_workbook('手机销售报表_美化.xlsx')
sheet = workbook['销售数据报表']

chart = BarChart()
chart.title = "产品销售额分析"
chart.x_axis.title = "产品名称"
chart.y_axis.title = "销售额（元）"

data = Reference(sheet, min_col=4, min_row=2, max_row=4)
categories = Reference(sheet, min_col=1, min_row=2, max_row=4)

chart.add_data(data, titles_from_data=False)
chart.set_categories(categories)

sheet.add_chart(chart, 'E1')

workbook.save('手机销售报表_含图表.xlsx')
print("图表创建成功！")

第7步：利用预设模板填充动态数据

企业级自动化通常采用“模板+填充”模式。先在Excel中搭建包含Logo、页眉、占位符与静态公式的标准版式，代码层仅负责加载模板、定位数据区域执行循环写入，最后另存为带日期的新文件。该架构将排版与维护解耦，后续只需替换模板即可迭代报表外观。

常见避坑指南与操作完成判断

格式兼容性：openpyxl不兼容旧版.xls二进制文件，遇此格式需先转换为.xlsx或改用其他解析器。
公式语法：写入的计算表达式必须严格遵循英文函数名与逗号分隔规则（如=SUM(A1:A10)），中文标点会导致Excel无法识别。
大文件内存：单次处理超几十MB文件时，默认全量加载易触发OOM。应显式开启read_only=True或write_only=True进行流式处理。
图表刷新机制：直接打开含图表的模板并仅修改底层数据后保存，部分版本可能清空图形。稳妥做法是在代码中重建Reference并重新绑定数据范围。