当前位置：首页 > news >正文

Openpyxl：Python操作Excel的利器

news 2025/7/13 19:49:25

Excel作为最广泛使用的办公软件之一，其文件操作在数据处理中至关重要。Openpyxl正是Python生态中处理Excel文件的利器，专为读写.xlsx文件设计。本文将全面解析Openpyxl的核心功能与应用技巧。

一、简介与安装

Openpyxl是一个纯Python库，支持读写.xlsx文件格式，无需安装Excel软件。主要特性包括：

完整支持Excel公式、图表、样式设置
内存优化处理大文件（>10MB）
兼容Pandas等数据分析库
支持数据验证、条件格式等高级功能

安装命令：

pip install openpyxl

二、核心概念

工作簿(Workbook)：整个Excel文件
工作表(Worksheet)：工作簿中的单个Sheet
单元格(Cell)：工作表的最小数据单元，通过行列坐标定位

三、基础操作

1. 创建工作簿

from openpyxl import Workbook# 创建新工作簿并激活默认工作表
wb = Workbook()
ws = wb.active  # 获取活动工作表
ws.title = "销售数据"  # 重命名工作表

2. 写入数据

# 单单元格写入
ws['A1'] = "商品名称"
ws.cell(row=1, column=2, value="销售额")# 批量写入（推荐高效方式）
data_rows = [["手机", 1200000],["笔记本", 980000],["平板", 750000]
]
for row in data_rows:ws.append(row)  # 自动追加到末尾

3. 读取数据

# 单单元格读取
value = ws['A2'].value)  # 输出：手机# 遍历区域（B1到B3）
for cell in ws['B1':'B3']:print(cell[0].value)  # 输出销售额数值# 按行遍历
for row in ws.iter_rows(min_row=1, values_only=True):print(row)  # 输出每行元组

4. 保存文件

wb.save("sales_report.xlsx")  # 保存为新文件

5. 工作表操作

新增、重命名与删除

ws1 = wb.create_sheet("新表单")
ws1.title = "销售数据"# 删除工作表
del wb["Sheet"]# 获取所有表名
print(wb.sheetnames)

四、进阶功能实战

1. 样式设置

openpyxl 支持对字体、填充、边框、对齐等样式进行设置。

from openpyxl.styles import Font, Alignment, Border,  PatternFill, Side# 设置字体、颜色
header_font = Font(name='Arial', size=12, bold=True, color="FFFFFF")# 设置单元格背景色
header_fill = PatternFill(start_color="3366FF", end_color="3366FF", fill_type="solid")# 设置边框
border = Border(bottom=Side(style="thick"))# 设置居中对齐
for cell in ws[1]:  # 第一行cell.font = header_fontcell.fill = header_fillcell.border = bordercell.alignment = Alignment(horizontal="center", vertical="center")

2. 公式计算

注意：openpyxl 不会计算公式，只能写入/读取公式。要计算结果需要在 Excel 中打开。

# 在B5单元格添加求和公式
ws['B5'] = "=SUM(B2:B4)"# 读取公式结果（需手动计算）
print(ws['B5'].value)  # 输出公式文本print(ws['B5'].value)  # 输出计算结果（需设置data_only=True打开）

想获得公式，data_only=False
想获得计算结果，data_only=True
想同时获得两者，需要两次加载（两个 Workbook 实例）

3. 合并、拆分单元格

# 合并A1到C1为一个单元格
ws.merge_cells('A1:C1')
ws['A1'] = "2023年度销售报告"# 拆分单元格
ws.unmerge_cells('A1:C1')

4. 数据筛选与排序

# 添加筛选器
ws.auto_filter.ref = "A1:B4"# 按销售额降序排序
ws.sort_values(by="B", ascending=False)

5. 插入图片和图表

插入图片

from openpyxl.drawing.image import Imageimg = Image("logo.png")
ws.add_image(img, "E5")

插入柱状图

from openpyxl.chart import BarChart, Reference# 准备数据
for i in range(1, 6):ws.append([i, i * 10])chart = BarChart()
data = Reference(ws, min_col=2, min_row=1, max_row=5)
chart.add_data(data)
ws.add_chart(chart, "F1")

五、高效处理大型文件

当处理>10MB文件时，如果你只需要读取不需要修改，可以使用 read_only=True：

# 启用只读模式（内存优化）
from openpyxl import load_workbook
wb = load_workbook('large_file.xlsx', read_only=True)

对于写入大量数据，使用 write_only=True 模式：

# 启用只写模式
wb = Workbook(write_only=True)
ws = wb.create_sheet()# 分批写入数据
for row in large_data_set:ws.append(row)wb.save("big.xlsx")

六、完整示例：销售报告生成器

from openpyxl import Workbook
from openpyxl.styles import Font, Alignment# 初始化工作簿
wb = Workbook()
ws = wb.active
ws.title = "销售汇总"# 构建表头
headers = ["产品", "季度1", "季度2", "季度3", "季度4", "年度总计"]
ws.append(headers)# 设置标题样式
for col in range(1, 7):cell = ws.cell(row=1, column=col)cell.font = Font(bold=True)cell.alignment = Alignment(horizontal='center')# 填充数据
sales_data = [["手机", 300, 420, 380, 500],["平板", 150, 180, 210, 240],["电脑", 200, 230, 250, 280]
]for data in sales_data:ws.append(data + [f"=SUM(B{ws.max_row}:E{ws.max_row})"])# 添加格式
for row in ws.iter_rows(min_row=2, max_col=6):for cell in row:if cell.column_letter == 'F':  # 年度列特殊格式cell.font = Font(color="FF0000", bold=True)# 保存文件
wb.save("年度销售报告.xlsx")