使用openpyxl轻松操控Excel文件

1. openpyxl 简介

Python 的 openpyxl 库是处理 Excel 文件的强大工具。在数据分析、自动化办公和报告生成等领域，经常需要对 Excel 文件进行读取、修改和创建操作。openpyxl 提供了全面的功能，允许开发者以编程方式操纵 Excel 文件，从而提高工作效率并减少手动操作的错误。

具体来讲，openpyxl 是一个用于读取和写入 Microsoft Excel 2010 xlsx/xlsm/xltx/xltm 文件的开源库。与其他处理 Excel 文件的库相比，openpyxl 专注于对现代 Excel 格式（即基于 XML 的 .xlsx 文件）的支持。

主要特点包括：

• 读取和写入 Excel 文件：支持对单元格、行、列、工作表和工作簿的操作。
• 格式化：可以修改单元格的字体、颜色、边框和对齐方式等格式属性。
• 图表和图片：支持在工作表中插入图表和图片，增强数据的可视化。
• 公式：能够读取和设置单元格中的公式，支持计算公式结果。

2. 安装与快速入门

2.1 安装 openpyxl

在开始使用 openpyxl 之前，需要先进行安装。可以通过 pip 进行安装：

pip install openpyxl

确保您的 Python 版本在 3.6 及以上，因为较早的版本可能不支持部分功能。

2.2 快速创建一个 Excel 文件

以下是一个使用 openpyxl 创建简单 Excel 文件的示例：

import openpyxl
from openpyxl import Workbook

# 创建一个新的工作簿
wb = Workbook()

# 激活默认的工作表
ws = wb.active

# 在单元格中写入数据
ws['A1'] = 'Hello'
ws['B1'] = 'World!'

# 保存工作簿
wb.save('hello_world.xlsx')

运行上述代码后，将在当前目录下生成一个名为 hello_world.xlsx 的 Excel 文件，内容如下：

2.3 读取 Excel 文件

openpyxl 还可以读取已有的 Excel 文件。下面是读取刚才创建的文件并打印单元格内容的示例：

import openpyxl

# 加载已有的工作簿
wb = openpyxl.load_workbook('hello_world.xlsx')

# 选择活动的工作表
ws = wb.active

# 读取并打印单元格内容
print(ws['A1'].value)  # 输出: Hello
print(ws['B1'].value)  # 输出: World!

通过以上简单的示例，我们已经了解了如何使用 openpyxl 创建和读取 Excel 文件。在接下来的章节中，我们将详细介绍 openpyxl 的核心概念和高级功能。

3. openpyxl 的核心概念

在使用 openpyxl 操作 Excel 文件时，需要理解一些核心概念，这将有助于我们更有效地利用该库。

3.1 工作簿（Workbook）

工作簿是 Excel 文件的顶层容器，包含了所有的工作表、全局设置和属性。

• 创建工作簿：wb = Workbook()
• 加载工作簿：wb = load_workbook(filename)

3.2 工作表（Worksheet）

工作簿包含一个或多个工作表，每个工作表都是一个二维的单元格网格。

• 激活工作表：ws = wb.active
• 创建新工作表：ws = wb.create_sheet(title='SheetName')
• 访问特定工作表：ws = wb['SheetName']

3.3 单元格（Cell）

单元格是工作表中的基本元素，包含数据、样式和其他属性。

• 访问单元格：cell = ws['A1'] 或 cell = ws.cell(row=1, column=1)
• 设置单元格值：ws['A1'] = 'Data'
• 获取单元格值：value = ws['A1'].value

3.4 行与列

可以通过行和列来批量操作单元格。

• 迭代行：for row in ws.iter_rows(min_row=1, max_col=3, max_row=2):
• 迭代列：for col in ws.iter_cols(min_row=1, max_col=3, max_row=2):

3.5 范围（Range）

范围是指一组连续的单元格，可以对其进行批量操作。

• 访问范围：cell_range = ws['A1:C3']

3.6 公式与函数

可以在单元格中使用公式，openpyxl 支持 Excel 的大部分公式。

• 设置公式：ws['A2'] = '=SUM(A1:A10)'
• 读取公式结果：需要在 Excel 中打开文件，或使用 data_only 参数加载工作簿。

wb = load_workbook('formula.xlsx', data_only=True)

3.7 样式与格式

openpyxl 提供了丰富的样式和格式选项，可以设置字体、颜色、对齐方式等。

设置字体：

from openpyxl.styles import Font
ws['A1'].font = Font(name='Calibri', size=14, bold=True)

设置填充颜色：

from openpyxl.styles import PatternFill
ws['A1'].fill = PatternFill(fill_type='solid', start_color='FF0000')

4. Excel 文件的读取与写入

在数据处理过程中，读取和写入 Excel 文件是最常见的操作。openpyxl 提供了简单直观的方法来完成这些任务。

4.1 读取 Excel 文件

加载工作簿

使用 load_workbook 函数加载已有的 Excel 文件：

from openpyxl import load_workbook

wb = load_workbook('data.xlsx')

选择工作表

可以通过多种方式选择工作表：

使用工作表名称：

ws = wb['Sheet1']

使用 active 属性：

ws = wb.active

读取单元格数据

读取单元格的值：

value = ws['A1'].value

或者使用行列索引：

value = ws.cell(row=1, column=1).value

遍历数据

遍历所有行和列：

for row in ws.iter_rows(values_only=True):
    print(row)

4.2 写入 Excel 文件

写入单元格

在特定单元格中写入数据：

ws['A1'] = 'New Data'

或者使用行列索引：

ws.cell(row=1, column=1, value='New Data')

插入行和列

插入一行：

ws.insert_rows(idx=2)

插入一列：

ws.insert_cols(idx=3)

删除行和列

删除一行：

ws.delete_rows(idx=2)

删除一列：

ws.delete_cols(idx=3)

保存工作簿

完成所有修改后，保存工作簿：

wb.save('modified_data.xlsx')

4.3 实践：批量修改单元格

假设我们有一个成绩表，需要将所有学生的成绩提高 5 分。

from openpyxl import load_workbook

wb = load_workbook('scores.xlsx')
ws = wb.active

# 假设成绩在 B 列，从第 2 行开始
for row in ws.iter_rows(min_row=2, min_col=2, max_col=2):
    for cell in row:
        if isinstance(cell.value, (int, float)):
            cell.value += 5

wb.save('updated_scores.xlsx')

通过以上操作，我们成功地批量修改了 Excel 文件中的数据。

5. 高级功能详解

openpyxl 不仅可以进行基本的读取和写入操作，还提供了许多高级功能，例如图表、图片和数据验证等。

5.1 操作公式

可以在单元格中写入公式：

ws['A10'] = '=SUM(A1:A9)'

openpyxl 不会自动计算公式结果，但可以通过设置 data_only=True 来读取公式计算后的值：

wb = load_workbook('formula.xlsx', data_only=True)
ws = wb.active
print(ws['A10'].value)

5.2 添加图表

以创建柱状图为例：

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, 'E5')

可以设置图表的标题、轴标签等属性：

chart.title = "Sales Chart"
chart.x_axis.title = "Month"
chart.y_axis.title = "Sales"

5.3 插入图片

from openpyxl.drawing.image import Image

img = Image('logo.png')

添加图片到工作表：

ws.add_image(img, 'A1')

5.4 数据验证

可以添加数据验证来限制单元格的输入内容。

创建数据验证对象

from openpyxl.worksheet.datavalidation import DataValidation

dv = DataValidation(type="whole", operator="between", formula1=1, formula2=100)

添加数据验证到工作表

dv.add('A1:A10')
ws.add_data_validation(dv)

5.5 条件格式

根据单元格的值，动态地改变单元格的格式。

创建条件格式规则

from openpyxl.formatting.rule import CellIsRule
from openpyxl.styles import PatternFill

red_fill = PatternFill(start_color='FFC7CE', end_color='FFC7CE', fill_type='solid')

ws.conditional_formatting.add('A1:A10', CellIsRule(operator='greaterThan', formula=['80'], fill=red_fill))

通过以上高级功能的介绍，可以看出 openpyxl 在处理 Excel 文件时具有非常强大的能力，足以满足大多数复杂的需求。

6. 样式与格式设置

为了使 Excel 文件更加美观和专业，openpyxl 提供了丰富的样式和格式设置选项。

6.1 字体样式

设置字体

from openpyxl.styles import Font

bold_font = Font(bold=True)
italic_font = Font(italic=True)

ws['A1'].font = bold_font
ws['A2'].font = italic_font

设置字体颜色和大小

from openpyxl.styles import Font, colors

font_style = Font(color=colors.RED, size=14, name='Arial')
ws['A1'].font = font_style

6.2 单元格填充

设置背景颜色

from openpyxl.styles import PatternFill

fill = PatternFill(fill_type='solid', start_color='FFFF00', end_color='FFFF00')
ws['A1'].fill = fill

6.3 边框设置

添加边框

from openpyxl.styles import Border, Side

thin_border = Border(left=Side(style='thin'),
                     right=Side(style='thin'),
                     top=Side(style='thin'),
                     bottom=Side(style='thin'))

ws['A1'].border = thin_border

6.4 对齐方式

设置对齐方式

from openpyxl.styles import Alignment

alignment = Alignment(horizontal='center', vertical='center')
ws['A1'].alignment = alignment

6.5 数字格式

设置数字格式

ws['A1'].number_format = '0.00'  # 保留两位小数

6.6 合并与拆分单元格

合并单元格

ws.merge_cells('A1:C1')

拆分单元格

ws.unmerge_cells('A1:C1')

6.7 实践：制作格式化报表

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

wb = Workbook()
ws = wb.active

# 写入表头
headers = ['姓名', '数学', '英语', '总分']
ws.append(headers)

# 添加数据
data = [
    ['张三', 85, 92],
    ['李四', 78, 88],
    ['王五', 90, 95]
]

for row in data:
    total = row[1] + row[2]
    row.append(total)
    ws.append(row)

# 设置表头样式
for cell in ws[1]:
    cell.font = Font(bold=True)
    cell.alignment = Alignment(horizontal='center')
    cell.border = Border(bottom=Side(style='thin'))

# 设置列宽
ws.column_dimensions['A'].width = 15
for col in ['B', 'C', 'D']:
    ws.column_dimensions[col].width = 10

# 保存工作簿
wb.save('report.xlsx')

通过以上代码，我们生成了一个格式化的成绩报表，包含表头加粗、单元格对齐、边框和列宽设置。