Python操作Excel：Openpyxl库深入应用

本文还有配套的精品资源，点击获取

简介：Openpyxl是Python中用于操作Excel文件的强大库，支持包括样式、图表、公式等在内的现代Excel特性。本文深入介绍openpyxl库的基本概念、工作簿和工作表的创建与操作、单元格的数据读写、样式和格式化、公式计算、数据验证、图表创建以及高级功能的使用。掌握openpyxl将提升Python在数据处理、自动化报告、数据清洗和分析方面的能力，并可与pandas、numpy等库结合用于构建高效的数据分析流程。

1. openpyxl库基础

在现代数据分析与处理中， openpyxl 库因其强大的功能和灵活性成为了Python处理Excel文件的首选库。本章将介绍 openpyxl 库的基本使用方法、特点以及如何在项目中安装和配置这一库。

首先，我们将探讨 openpyxl 库的设计理念和主要用途。它不仅支持读写Excel 2010 xlsx/xlsm/xltx/xltm文件格式，还可以创建和修改这些文件，使其非常适合需要处理Excel文档的应用程序。接着，我们会了解安装 openpyxl 的几种方法，包括使用 pip 安装和通过虚拟环境管理工具如 virtualenv 和 conda 进行安装。本章还将简要概述如何创建一个简单的 openpyxl 应用程序，从而带领读者入门这一库的使用。通过本章的学习，读者将能够为后续章节中更复杂的操作打下坚实的基础。

安装示例代码：

pip install openpyxl

或者使用conda进行安装：

conda install -c anaconda openpyxl

安装完成后，我们可以通过创建一个简单的Excel文件来验证安装是否成功：

from openpyxl import Workbook

# 创建一个新的工作簿
wb = Workbook()
# 保存工作簿到文件
wb.save("simple.xlsx")

上述代码创建了一个包含默认工作表的Excel文件，并将其保存到本地磁盘。这不仅验证了安装是否成功，也展示了如何使用 openpyxl 进行基本的Excel文件操作。

2. Excel工作簿与工作表操作

2.1 Excel工作簿的管理

2.1.1 创建和打开工作簿

在Python中，使用openpyxl库创建和打开一个Excel工作簿是一个非常直接的过程。我们可以使用 Workbook 类来创建一个新的工作簿，而 load_workbook 函数可以用来打开一个已经存在的工作簿。

from openpyxl import Workbook, load_workbook

# 创建一个新的工作簿
wb = Workbook()
# 保存工作簿，参数可以是文件名
wb.save('example.xlsx')

# 打开一个已存在的工作簿
wb = load_workbook('example.xlsx')

创建和保存工作簿的操作通常很直接，需要注意的是保存时指定的文件名。在打开工作簿时，如果没有指定路径，则会在当前工作目录下寻找指定的文件。

2.1.2 保存和关闭工作簿

工作簿的保存和关闭确保了所有更改都被写入磁盘，并释放了相关资源。这是防止数据丢失的重要步骤。

# 保存工作簿
wb.save('example.xlsx')
# 关闭工作簿
wb.close()

代码逻辑解析：
- save 方法用于将工作簿保存到文件系统。如果没有提供参数，则默认保存到当前工作簿的文件名。
- close 方法用于关闭工作簿。在关闭工作簿之前，所有未保存的更改将丢失。确保在关闭之前保存所有更改。

参数说明：
- save 方法的参数可以是文件路径，也可以是文件名。如果文件已经存在，原有的内容会被覆盖。
- close 方法不接受任何参数。

操作优化：
- 在处理较大的工作簿或者有自动保存机制的环境中，推荐使用工作簿的 with 语句，这样可以保证文件在操作完成后被正确关闭。

with Workbook() as wb:
    # 进行操作
    pass  # 这里可以用实际的代码替换
# 文件在with块结束时自动关闭

2.2 Excel工作表的基本操作

2.2.1 工作表的选择与切换

在openpyxl中，一个工作簿可以包含多个工作表，每个工作表由一个 Worksheet 对象表示。我们可以使用 get_sheet_by_name 方法或 active 属性来选择和切换工作表。

# 通过名称获取工作表
sheet = wb.get_sheet_by_name('Sheet1')
# 或者直接获取当前活动的工作表
sheet = wb.active

代码逻辑解析：
- get_sheet_by_name 方法允许通过工作表的名称来获取工作表对象。
- active 属性返回当前活动的工作表。如果之前没有操作过工作表，它通常指向第一个工作表。

参数说明：
- get_sheet_by_name 方法接受一个字符串参数，即工作表的名称。
- active 不需要任何参数。

操作优化：
- 可以创建一个字典来存储所有工作表，方便切换和管理：

sheets = {ws.title: ws for ws in wb.worksheets}
active_sheet = sheets[wb.active.title]

2.2.2 工作表的添加与删除

在处理工作簿时，我们经常需要添加或删除工作表。使用 create_sheet 方法可以添加新的工作表，而 remove 方法可以删除指定的工作表。

# 添加一个新的工作表
wb.create_sheet('New_Sheet')
# 删除指定的工作表
wb.remove(wb.get_sheet_by_name('Old_Sheet'))

代码逻辑解析：
- create_sheet 方法默认添加一个新的工作表到工作簿的末尾，也可以指定索引来插入到特定位置。
- remove 方法需要传入一个工作表对象作为参数，用于删除该工作表。

参数说明：
- create_sheet 方法可以接受一个可选的索引参数，用于指定新工作表的位置。
- remove 方法的参数是工作表对象。

操作优化：
- 添加工作表时，如果要插入到特定位置，需要使用索引参数：

# 将工作表添加到特定位置
wb.create_sheet(title='New_Sheet', index=0) # 添加到第一位

2.3 高级工作表操作技巧

2.3.1 工作表的重命名和移动

重命名和移动工作表是高级操作中常见需求，可以使用 title 属性来重命名工作表，而通过指定索引来移动工作表。

# 重命名工作表
sheet.title = 'Renamed_Sheet'
# 移动工作表到新的位置
wb.move_sheet(sheet, 0)

代码逻辑解析：
- title 属性可以修改工作表的名称。
- move_sheet 方法可以移动工作表到工作簿的新位置。它的第一个参数是工作表对象，第二个参数是新的位置索引。

参数说明：
- title 属性接受一个字符串参数作为新的工作表名称。
- move_sheet 方法接受两个参数：要移动的工作表对象和新的索引位置。

操作优化：
- 在移动工作表之前，最好检查新的索引是否有效，以及目标位置是否需要特殊处理（如替换现有工作表）。

# 检查目标索引是否有效
new_index = 2
if 0 <= new_index < len(wb.worksheets):
    wb.move_sheet(sheet, new_index)

2.3.2 工作表的复制和共享

在某些情况下，可能需要复制整个工作表的内容，或者将工作表内容共享给其他程序。openpyxl提供了 copy_worksheet 方法来实现工作表的复制。

# 复制工作表到新工作簿
new_wb = Workbook()
new_wb.active.title = 'Copy_Sheet'
wb.copy_worksheet(sheet, new_wb.active)
new_wb.save('copied_example.xlsx')

代码逻辑解析：
- copy_worksheet 方法复制一个工作表的内容到另一个工作簿或工作表。
- 这个方法需要两个参数：源工作表和目标工作表。

参数说明：
- copy_worksheet 方法需要两个工作表对象作为参数，分别表示源工作表和目标工作表。

操作优化：
- 当需要分享工作表内容时，可以通过复制到新的工作簿并保存为XLSM格式（宏启用的Excel工作簿），以确保宏和VBA代码也被保留。

# 复制工作表并保存为宏启用的工作簿
new_wb = Workbook()
new_sheet = new_wb.create_sheet(title=sheet.title)
wb.copy_worksheet(sheet, new_sheet)
new_wb.save('shared_example.xlsm')

在这一章节中，我们介绍了如何管理工作簿和执行基本与高级的工作表操作。在接下来的章节中，我们将继续深入探讨如何在openpyxl中操作单元格数据，以及如何应用样式和格式来定制我们的Excel工作簿。

3. 单元格数据读写

在处理Excel文件时，单元格数据的读写是最基础也是最核心的操作之一。本章将深入探讨如何使用openpyxl库对单元格数据进行高效的读取和写入，并且讲解如何在Excel中处理公式以及对不同数据范围进行操作。

3.1 单元格的基本操作

单元格是Excel中存储数据的基本单元，对其进行读取和写入是处理Excel文件的日常工作。

3.1.1 单元格的读取和写入

读取单元格内容非常简单，我们只需要指定工作表以及单元格的坐标即可：

from openpyxl import load_workbook

# 加载工作簿
wb = load_workbook('example.xlsx')

# 选择工作表
sheet = wb['Sheet1']

# 读取单元格A1的内容
cell_value = sheet['A1'].value
print(cell_value)

在上述代码中，我们首先导入了openpyxl库中的 load_workbook 函数来打开一个Excel文件，然后通过工作簿对象访问了名为 Sheet1 的工作表，并读取了位于A1位置的单元格内容。

写入单元格的操作也类似：

# 写入单元格A1的内容
sheet['A1'] = 'Hello, openpyxl!'

这段代码将会把字符串 Hello, openpyxl! 写入到 example.xlsx 文件的 Sheet1 工作表中A1单元格内。

3.1.2 单元格的公式输入

在Excel中，单元格不仅可以存储静态数据，还可以输入公式进行计算。在openpyxl中，我们可以如下方式插入公式：

# 在B1单元格内输入公式
sheet['B1'] = '=A1+10'

这段代码将在B1单元格内插入了一个简单的公式，表示将A1单元格的值加10。当单元格内容被读取时，openpyxl会返回公式而非计算结果：

# 读取B1单元格公式
formula = sheet['B1'].value
print(formula)

3.2 数据范围的操作

在Excel中，我们经常需要处理的不仅是单个单元格，还有多个单元格组成的数据范围。我们可以对这样的范围进行读取和写入。

3.2.1 读写连续单元格区域

连续单元格区域可以使用切片的方式来读取和写入。例如，要读取 Sheet1 工作表中A1到B2的区域：

# 读取A1到B2的区域数据
range_value = sheet['A1':'B2'].value

如果要将一个区域的内容写入到另一个区域，需要首先确保目标区域已经存在，然后执行赋值操作：

# 定义要写入的数据
data_to_write = [['x', 'y'], ['z', 'w']]

# 将数据写入到B1到C2区域
sheet['B1':'C2'] = data_to_write

3.2.2 读写不连续的单元格区域

openpyxl也支持不连续的单元格区域操作，但需要注意的是，我们不能像连续区域那样直接写入一个值，而是需要使用 cell 方法逐个处理：

# 创建一个不连续的区域
for row in range(1, 3):
    for col in range(3, 5):
        sheet.cell(row=row, column=col, value=f'Cell {row}{col}')

# 读取不连续的区域数据
non_contiguous_range = [sheet.cell(row=i, column=j).value for j in range(3, 5) for i in range(1, 3)]

在这里，我们创建了一个由四个单元格组成的不连续区域，并对每个单元格分别赋值。然后通过列表推导式读取了这个区域的所有数据。

通过上述示例，我们可以看到openpyxl在处理单元格和数据范围方面的强大功能。接下来的章节将介绍如何进一步定制Excel样式与格式，并探索openpyxl库的高级功能。

4. Excel样式与格式定制

4.1 字体与颜色设置

4.1.1 字体样式定制

在处理Excel文件时，良好的视觉效果往往能增强数据的可读性和美观性。使用openpyxl库，我们可以轻松地自定义字体样式，包括字体类型、大小、粗细以及斜体等属性。

首先，我们需要从 openpyxl.styles 模块导入 Font 类。然后创建一个 Font 实例，并设置我们想要的属性。例如，想要改变字体样式为粗体、16号字，且使用斜体：

from openpyxl.styles import Font

# 创建一个Font对象并设置属性
my_font = Font(name='Arial', size=16, bold=True, italic=True)

创建完 Font 对象后，我们需要将其应用到工作表中的某个单元格上。这可以通过获取单元格的样式（ style 属性），然后调用 update 方法来实现：

from openpyxl import Workbook

# 创建工作簿和工作表
wb = Workbook()
ws = wb.active

# 设置单元格的字体样式
ws['A1'].style.font = my_font

# 写入一些示例数据
ws['A1'] = 'Styled Text'

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

以上代码将创建一个包含一个单元格 A1 的Excel工作簿，单元格中的文本将显示为粗体、斜体、16号字的Arial字体。通过这种方式，我们可以针对不同内容定制不同的字体样式，以达到预期的视觉效果。

4.1.2 字体颜色的配置

除了字体样式外，字体颜色也是影响文档可读性的重要因素。通过openpyxl，我们可以为字体设置各种颜色。

首先，导入 openpyxl.styles 模块中的 colors 模块，然后创建一个 Font 对象，再通过设置 color 属性来改变字体颜色：

from openpyxl.styles import Font, colors

# 创建一个Font对象
my_font = Font(name='Arial', size=16, color="FF0000")  # FF0000 是红色的十六进制代码

# 应用字体样式到单元格
ws['A2'].style.font = my_font
ws['A2'].value = 'Colored Text'

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

在上面的例子中，单元格 A2 中的文本将显示为红色。 color 属性接受一个字符串类型的十六进制颜色代码，它定义了字体的颜色。openpyxl内置了许多常用颜色的字符串表示，也可以使用自定义的十六进制颜色代码。

通过组合字体样式与颜色配置，我们可以创建出更加个性化和专业的Excel文档。这对于生成报告或演示文稿尤为重要。

4.2 单元格对齐和边框

4.2.1 对齐方式的调整

Excel中的数据对齐是增强数据展示效果的重要手段。在openpyxl中，我们可以定制单元格中的数据对齐方式，包括水平对齐和垂直对齐。

以下是如何通过openpyxl设置单元格对齐方式的步骤：

1. 从 openpyxl.styles 导入 Alignment 类。
2. 创建一个 Alignment 对象，并设置相应的对齐属性。
3. 将 Alignment 对象赋值给单元格的 style.alignment 属性。

例如，我们希望将单元格中的文本水平居中对齐，并垂直居中：

from openpyxl.styles import Alignment

# 创建Alignment对象并设置水平和垂直对齐
cell_alignment = Alignment(horizontal='center', vertical='center')

# 应用到单元格上
ws['B1'].style.alignment = cell_alignment

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

在上述代码片段中， B1 单元格的内容将被设置为水平和垂直居中。 Alignment 类支持的对齐选项包括 left , center , right （水平对齐）以及 top , center , bottom （垂直对齐）。

此外，还可以调整文本的阅读方向，设置单元格内容的缩进，以及文本的旋转角度等。所有这些属性都可以通过 Alignment 类中的属性进行配置。

4.2.2 边框样式的定制

Excel中的边框样式能够突出显示数据或区分不同的数据区域。在openpyxl中，我们可以通过定制边框样式来实现更丰富的视觉效果。

要设置单元格的边框样式，需要执行以下步骤：

1. 从 openpyxl.styles 导入 Side 和 Border 类。
2. 创建一个或多个 Side 对象，定义边框的样式和颜色。
3. 创建一个 Border 对象，并将 Side 对象分配给四个方向的边框。
4. 应用 Border 对象到单元格的 style.border 属性。

例如，为单元格 B2 添加一个细的红色实线边框：

from openpyxl.styles import Side, Border, StyleArray
from openpyxl.styles import colors

# 创建边框的四个方向样式
thin_red = Side(style='thin', color='FF0000')  # FF0000是红色的十六进制代码

# 创建Border对象并分配边框
border = Border(left=thin_red, right=thin_red, top=thin_red, bottom=thin_red)

# 应用到单元格
ws['B2'].style.border = border

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

在上述代码中，我们创建了一个 Border 对象，它将应用于 B2 单元格的四个方向，分别设置为细的红色实线。通过指定 left , right , top , bottom 属性，可以实现对不同方向边框样式的定制。

4.3 样式和格式的批量应用

4.3.1 应用预设样式

当我们在Excel中处理大量数据时，经常需要对多个单元格应用相同的格式和样式。手动设置将非常耗时，因此在openpyxl中，我们可以创建和应用预设的样式，以便于批量操作。

创建一个预设样式的步骤如下：

1. 从 openpyxl.styles 模块导入需要的样式类，例如 Font , Fill , Border 等。
2. 创建这些样式的实例，并设置相应的属性。
3. 将这些样式组合成一个样式实例。
4. 将预设样式应用到指定的单元格、行、列或范围上。

以下是一个示例，说明如何创建并应用一个包含字体、填充和边框的预设样式：

from openpyxl.styles import Font, Fill, Border, StyleArray
from openpyxl.styles.fills import PatternFill
from openpyxl.styles.borders import Border, Side

# 创建字体样式
font_style = Font(name='Arial', size=12, bold=True)

# 创建填充样式
fill_style = PatternFill(fill_type='solid', start_color='FFFF00', end_color='FFFF00')

# 创建边框样式
thin_red_border = Side(style='thin', color='FF0000')
border_style = Border(left=thin_red_border, right=thin_red_border, top=thin_red_border, bottom=thin_red_border)

# 将字体、填充和边框组合成预设样式
preset_style = StyleArray([font_style, fill_style, border_style])

# 应用预设样式
ws['A3:D7'].style = preset_style

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

在这个例子中，我们定义了一个预设样式，它将应用到工作表的 A3:D7 范围内。该范围内的所有单元格都会具有相同的字体、填充和边框样式。通过这种方式，我们可以快速地对大量单元格应用统一的格式，显著提高工作效率。

4.3.2 样式复制与粘贴

除了创建预设样式外，openpyxl还支持样式复制和粘贴的功能。这允许我们将一个单元格或范围的样式应用到另一个单元格或范围上。这在需要将特定的格式化应用到多个位置时非常有用。

要复制并粘贴样式，我们可以使用以下步骤：

1. 获取想要复制样式的单元格。
2. 创建一个新的或指定的单元格，用于粘贴样式。
3. 使用 copy_style 方法将样式从源单元格复制到目标单元格。

例如，假设我们想将 B1 单元格的样式复制到 C1 单元格：

from openpyxl import load_workbook

# 加载现有工作簿
wb = load_workbook('styled_workbook.xlsx')

# 从工作簿中获取工作表
ws = wb.active

# 获取源单元格（具有我们想要复制的样式）
source_cell = ws['B1']

# 创建目标单元格（将应用复制的样式）
target_cell = ws['C1']

# 将源单元格的样式复制到目标单元格
target_cell._style = source_cell._style

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

在上面的代码示例中，我们将 B1 单元格的样式复制到了 C1 单元格。使用 copy_style 方法之前，确保目标单元格已经存在，否则可能会因为引用错误导致程序异常。

这种方式可以快速将样式复制到其他单元格上，而不必每次都从头开始定义样式。这对于大规模格式化工作非常有帮助，可以大幅节省时间。不过需要注意的是，复制粘贴的仅仅是样式，而不是单元格的数据内容。

5. openpyxl高级功能探索

5.1 Excel公式和计算功能

openpyxl库不仅仅能够处理静态的数据，它还提供了强大的公式和计算功能，使得我们可以直接在Python代码中构建动态的Excel表格。

5.1.1 公式的编写和应用

首先，我们来看如何在openpyxl中编写和应用公式。公式通常被设置在单元格对象的 formula 属性上。例如，我们要在 Sheet1 的 A1 单元格中设置一个简单的求和公式：

from openpyxl import Workbook

# 创建一个工作簿
wb = Workbook()
sheet = wb.active
sheet.title = 'Sheet1'

# 在A1单元格设置公式
sheet['A1'] = '=SUM(1,2,3)'

如果我们要引用其他单元格的内容作为公式的一部分，我们可以直接使用单元格的坐标。下面是一个例子，我们在 A2 单元格中引用了 B1 单元格的数据进行求和：

# 假设B1单元格已经设置了一个数值，比如3
sheet['B1'] = 3

# 在A2单元格引用B1单元格的数据进行求和
sheet['A2'] = '=SUM(B1, 5)'

5.1.2 公式的错误检查和调试

在编写公式时，错误是不可避免的。openpyxl能够识别一些简单的错误并给出反馈。我们可以通过 is_valid 方法来检查公式的有效性：

from openpyxl.utils import get_column_letter

# 假设我们要检查A1单元格的公式
result = sheet['A1'].is_valid
if not result:
    print("公式无效，请检查！")

如果公式本身没有错误，我们还可以使用 check_error 方法来获取可能的计算错误信息：

error = sheet['A2'].check_error
if error:
    print(f"单元格 {get_column_letter(sheet['A2'].column)}{sheet['A2'].row} 出现了错误: {error}")

openpyxl还支持更多复杂的公式，包括但不限于条件公式、查找引用等，可以根据需要进行相应的应用。

5.2 数据验证规则的设置

数据验证是保证Excel数据输入正确性的重要手段，openpyxl对此提供了很好的支持。

5.2.1 数据验证的目的和场景

数据验证允许我们为单元格设置输入规则，比如限制输入的类型、设定取值范围等。这在实际工作中是非常有用的，它可以避免错误的数据输入，保证数据的质量和一致性。

5.2.2 实现数据验证的代码示例

下面是一个设置数据验证规则的简单示例，我们将对 Sheet1 的B列设置一个整数范围的验证，要求只能输入1到10之间的整数：

from openpyxl.worksheet.datavalidation import DataValidation

# 创建数据验证规则
dv = DataValidation(min_value=1, max_value=10, type="whole", error='输入的数值不在1到10之间', error_title='输入错误')

# 应用数据验证到B列的第2行到第10行
for row in range(2, 11):
    sheet.add_data_validation(dv)
    dv.add(f'B{row}')

# 注意：一定要在数据验证添加完毕后，才能启用它
dv.title = '数据验证提示'
dv.prompt = '请输入1到10之间的整数！'
dv.error_message = '输入的数值不在1到10之间'
dv.apply()

在上面的代码中，我们首先创建了一个 DataValidation 对象，并设置了它的参数，如 min_value 和 max_value 来限制输入范围。然后我们指定它应用到 Sheet1 的B列的第2行到第10行。通过 add_data_validation 方法将验证规则添加到工作表，并使用 apply 方法使其生效。

这只是数据验证功能的一个简单例子。实际上，openpyxl支持更多的数据验证功能，比如使用公式进行条件验证、设置自定义验证消息等。

接下来，我们将探讨如何使用openpyxl创建和配置图表，这是数据分析工作中不可或缺的一个环节。

本文还有配套的精品资源，点击获取

简介：Openpyxl是Python中用于操作Excel文件的强大库，支持包括样式、图表、公式等在内的现代Excel特性。本文深入介绍openpyxl库的基本概念、工作簿和工作表的创建与操作、单元格的数据读写、样式和格式化、公式计算、数据验证、图表创建以及高级功能的使用。掌握openpyxl将提升Python在数据处理、自动化报告、数据清洗和分析方面的能力，并可与pandas、numpy等库结合用于构建高效的数据分析流程。

本文还有配套的精品资源，点击获取