我要评论在职场中，excel无疑是最核心的数据处理工具之一。然而，面对每周、每日都要重复的数据录入、报表汇总和格式调整，人工操作不仅效率低下，还极易出错。进入我们学习计划的第四阶段（第36-50天），我们的核心目标是让机器人学会“读写算”，能够高效处理excel、pdf、csv等常见办公文档。本文将作为本阶段的开篇，深度聚焦于python操作excel的利器——openpyxl库，带你从零开始，掌握读写单元格、应用公式、美化样式、管理工作表乃至创建动态图表的全套技能。
一、 为什么是openpyxl？—— 自动化办公的第一选择
在python生态中，操作excel的库有很多，如pandas、xlrd/xlwt、xlwings等。但如果你需要处理的是现代excel文件（.xlsx格式），且希望在保留原有格式的基础上进行读写，甚至操作图表和样式，openpyxl无疑是最佳通用选择 。
核心优势：
- 原生支持.xlsx：专门针对excel 2010及以后的版本设计。
- 保留样式与公式：不同于某些仅处理数据的库，openpyxl在修改单元格内容时，能够完美保留原有的字体、颜色、边框和公式 。
- 功能全面：不仅支持读写数据，还支持创建图表、设置样式、合并单元格、添加数据验证等高级功能 。
- 内存优化：对于大文件，openpyxl提供了只读和只写模式，可以有效降低内存消耗。
在开始我们的“读写算”之旅前，请先确保你的环境中已安装该库。打开终端，输入以下命令：
pip install openpyxl
二、 核心概念：工作簿、工作表、单元格
在开始编码之前，理解openpyxl的三个核心层级至关重要。你可以把excel文件想象成一本由若干页纸组成的账本 ：
- 工作簿（workbook）：代表整个excel文件（如“销售报表.xlsx”）。这是最顶层的容器。
- 工作表（worksheet）：代表工作簿中的每一页（如“sheet1”、“一月数据”）。一个工作簿可以包含多个工作表。
- 单元格（cell）：工作表中最基本的存储单元，由行和列的坐标定位（如a1， b3）。
我们对excel的所有操作，本质上都是通过openpyxl创建或加载一个workbook对象，然后从中获取指定的worksheet，最后对worksheet中的cell进行读写或样式设置。
三、读写单元格与公式应用——让python学会“读写”
3.1 读取excel数据：让python“看懂”表格
假设我们有一个现有的excel文件“销售数据.xlsx”，我们需要读取其中的数据。使用load_workbook()函数是读取的起点 。
from openpyxl import load_workbook
# 1. 加载工作簿
workbook = load_workbook('销售数据.xlsx')
# 2. 获取工作表 (通过名称或活动表)
# sheet = workbook['sheet1'] # 通过名称
sheet = workbook.active # 获取当前活动的工作表
# 3. 读取特定单元格的值
cell_a1 = sheet['a1'].value
print(f"a1单元格的内容是：{cell_a1}")
# 或者使用cell方法，指定行和列 (行和列索引都从1开始)
cell_b2 = sheet.cell(row=2, column=2).value
print(f"b2单元格的内容是：{cell_b2}")
# 4. 遍历整个工作表的数据
print("--- 工作表全部数据 ---")
for row in sheet.iter_rows(values_only=true): # values_only=true直接返回值，而不是cell对象
print(row)
# 操作完成后记得关闭工作簿释放资源
workbook.close()
应用场景：你可以将此代码嵌入到每日的数据汇总任务中，自动从多个部门发来的excel中提取关键指标，无需手动打开每个文件查看 。
3.2 写入数据与公式：让python“填写”报表
仅仅读取是不够的，我们更需要自动生成报表。接下来，我们将创建一个新的工作簿，并写入销售数据。同时，我们将展示如何写入excel公式，让excel自动计算“总价”，实现“算”的功能 。
from openpyxl import workbook
# 1. 创建一个新的工作簿
workbook = workbook()
sheet = workbook.active
sheet.title = "手机销售数据" # 重命名工作表
# 2. 写入表头
headers = ['销售员', '产品', '销量', '单价', '总价']
sheet.append(headers) # append方法可以方便地添加一行数据
# 3. 写入原始数据 (销量和单价)
raw_data = [
['张三', 'iphone 15', 10, 6000],
['李四', '小米14', 15, 4000],
['王五', '华为mate 60', 8, 7000],
]
for row_data in raw_data:
sheet.append(row_data)
# 4. 写入公式 (计算总价)
# 总价 = 销量 * 单价。对于第一行数据，销量在c2单元格，单价在d2单元格，所以公式是 "=c2*d2"
sheet['e2'] = '=c2*d2'
sheet['e3'] = '=c3*d3'
sheet['e4'] = '=c4*d4'
# 为了让效果更明显，我们也可以使用循环批量写入公式
# for i in range(2, 5):
# sheet[f'e{i}'] = f'=c{i}*d{i}'
# 5. 保存工作簿
workbook.save('手机销售报表_生成.xlsx')
print("报表生成成功！")
运行这段代码，你会发现在生成的excel文件中，“总价”列已经自动计算出了正确的结果。这正是“读写算”中“算”的初步体现。通过python写入公式，我们让excel引擎承担了计算工作，既准确又高效 。
四、美化样式——告别千篇一律的“黑白表格”
数据填充完毕，但一张专业的报表还需要清晰的格式。手动设置字体、对齐方式、背景色不仅枯燥，而且难以保证每次报表风格一致。openpyxl提供了强大的styles模块，让python替我们完成美化工作 。
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'
# ----- 开始美化 -----
# 1. 设置标题行样式：加粗、蓝色字体、黄色背景、居中
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
# 2. 为数据区域添加边框
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
# 3. 设置货币格式 (单价和总价列)
from openpyxl.styles import numbers
for row in range(2, sheet.max_row + 1):
sheet[f'c{row}'].number_format = numbers.format_currency_cn # 人民币格式
sheet[f'd{row}'].number_format = numbers.format_currency_cn
# 4. 调整列宽
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.1 管理工作表
随着业务复杂度增加，一个工作簿中往往包含多个工作表。openpyxl允许我们像操作excel一样，对工作表进行创建、复制和删除 。
from openpyxl import workbook
workbook = workbook()
# 默认会有一个名为'sheet'的工作表
# 1. 创建新工作表
sheet1 = workbook.create_sheet('产品销售') # 默认插在最后
sheet2 = workbook.create_sheet('销售员绩效', 0) # 指定位置，插在第一个（索引0）
# 2. 获取所有工作表名称
print(workbook.sheetnames) # 输出: ['销售员绩效', 'sheet', '产品销售']
# 3. 删除工作表
del workbook['sheet'] # 删除默认的工作表
# 4. 复制工作表
if '产品销售' in workbook.sheetnames:
source_sheet = workbook['产品销售']
# 复制的工作表会自动命名，如'产品销售 copy'
workbook.copy_worksheet(source_sheet)
print(workbook.sheetnames) # 输出: ['销售员绩效', '产品销售', '产品销售 copy']
workbook.save('工作表操作示例.xlsx')
这一功能在需要根据模板批量生成报表时非常实用 。
5.2 创建图表（数据可视化）
枯燥的数字很难让人一眼看出趋势。openpyxl支持直接在excel中嵌入图表，如柱状图、折线图等。下面，我们基于前面的销售数据，创建一个销售额的柱状图 。
from openpyxl import load_workbook
from openpyxl.chart import barchart, reference
# 加载我们之前美化过的文件
workbook = load_workbook('手机销售报表_美化.xlsx')
sheet = workbook['销售数据报表']
# 1. 创建一个柱状图对象
chart = barchart()
chart.title = "产品销售额分析"
chart.x_axis.title = "产品名称"
chart.y_axis.title = "销售额（元）"
# 2. 定义数据和分类的范围
# 数据：总价列的数据 (d2:d4)
data = reference(sheet, min_col=4, min_row=2, max_row=4)
# 分类：产品名称列 (a2:a4) 作为x轴的标签
categories = reference(sheet, min_col=1, min_row=2, max_row=4)
# 3. 将数据和分类添加到图表
chart.add_data(data, titles_from_data=false) # titles_from_data=false表示数据区域不包含标题
chart.set_categories(categories)
# 4. 将图表插入到工作表，例如e1单元格的位置
sheet.add_chart(chart, 'e1')
workbook.save('手机销售报表_含图表.xlsx')
print("图表创建成功！")
打开生成的excel文件，你会看到一个直观的柱状图已经呈现在表格旁边。图表会随着源数据的改变而自动更新。通过循环，我们甚至可以一次性为多个数据列创建多个图表 。这标志着我们不仅能让机器人“读写算”，还能让它产出具有洞察力的可视化报告。
六、 实战技巧：使用模板与注意事项
为了达到95分以上的高质量自动化，我们还需要掌握一些高级技巧。
6.1 高效使用模板
在实际企业应用中，更常见的做法不是用代码从头搭建报表，而是基于一个设计好的模板进行填充 。这样做的好处是：
- 格式分离：复杂的格式（logo、页眉页脚、合并单元格、预定义样式）可以在excel中由专业设计师完成。
- 维护方便：如果需要修改报表样式，只需修改模板文件，无需改动python代码。
实现步骤：
- 设计模板：在excel中创建一个
template.xlsx文件，设置好所有静态内容、标题、公式和占位符。 - 加载模板：
wb = openpyxl.load_workbook(‘template.xlsx’) - 填充数据：定位到占位符区域（如
a2开始），用循环sheet.append(data)填充动态数据。 - 另存为新文件：
wb.save(‘月度报告_2025年7月.xlsx’)
这种方式完美地保留了模板中的所有样式和静态公式，是生成周报、月报的标准姿势 。
6.2 避坑指南
- 仅支持.xlsx：openpyxl不能处理旧版的
.xls文件。如果遇到.xls，需要先另存为.xlsx或使用xlrd库读取 。 - 公式语言：写入公式时，必须使用英文函数名和英文分隔符（逗号），因为openpyxl生成的是英文版的excel公式。例如，求和要用
=sum(a1:a10)，而不是中文版的=求和(a1:a10)。 - 图表更新：如果你用openpyxl打开一个含有图表的模板，仅仅修改数据源后保存，图表有时会丢失。最稳妥的方法是，在代码中重新创建图表并绑定数据，正如我们在5.2节所做的那样 。
- 大文件性能：处理超大文件（几十mb）时，默认的加载模式会将整个文件读入内存，可能导致内存溢出。此时应考虑使用
read_only或write_only模式 。
七、 总结
通过本文的深度拆解，我们从零开始，完整地走通了openpyxl自动化excel的四大核心步骤：
- 读写单元格：掌握了
load_workbook和workbook的基本用法，实现了数据的输入与输出 。 - 应用公式：学会了如何在单元格中写入公式，让excel引擎完成计算任务 。
- 美化样式：利用
font、patternfill、border等组件，让报表告别粗糙，走向专业 。 - 高级操作：实现了工作表的创建与删除，并成功创建了动态图表，让数据可视化变得触手可及 。
到此这篇关于python使用openpyxl自动化处理excel数据与文件详解的文章就介绍到这了,更多相关python openpyxl处理excel内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！
发表评论