Python酷库之旅-第三方库openpyxl(01)

一、 openpyxl库的由来

1、背景

2、起源

3、发展

4、特点

4-1、支持.xlsx格式

4-2、读写Excel文件

4-3、操作单元格

4-4、创建和修改工作表

4-5、样式设置

4-6、图表和公式

4-7、支持数字和日期格式

二、openpyxl库的优缺点

1、优点

1-1、支持现代Excel格式

1-2、功能丰富

1-3、易于使用

1-4、与Excel兼容性

1-5、性能良好

1-6、社区支持

1-7、跨平台

2、缺点

2-1、不支持旧版格式

2-2、某些特性支持有限

2-3、内存占用

2-4、文档和示例可能不足

2-5.、依赖关系

2-6、学习曲线

三、openpyxl库的用途

1、读取Excel文件

2、写入Excel文件

3、修改Excel文件

4、自动化

5、与Excel交互

6、数据迁移和转换

7、创建模板化的报告

四、如何学好openpyxl库？

1、获取openpyxl库的属性和方法

2、获取xlwt库的帮助信息

3、用法精讲

3-1、openpyxl.load_workbook函数

3-1-1、语法

3-1-2、参数

3-1-3、功能

3-1-4、返回值

3-1-5、说明

3-1-6、用法

3-2、openpyxl.Workbook.add_named_style方法

3-2-1、语法

3-2-2、参数

3-2-3、功能

3-2-4、返回值

3-2-5、说明

3-2-6、用法

3-3、openpyxl.Workbook.close方法

3-3-1、语法

3-3-2、参数

3-3-3、功能

3-3-4、返回值

3-3-5、说明

3-3-6、用法

3-4、openpyxl.Workbook.copy_worksheet方法

3-4-1、语法

3-4-2、参数

3-4-3、功能

3-4-4、返回值

3-4-5、说明

3-4-6、用法

3-5、openpyxl.Workbook.create_chartsheet方法

3-5-1、语法

3-5-2、参数

3-5-3、功能

3-5-4、返回值

3-5-5、说明

3-5-6、用法

五、推荐阅读

1、Python筑基之旅

2、Python函数之旅

3、Python算法之旅

4、Python魔法之旅

5、博客个人主页

一、 openpyxl库的由来

openpyxl库的由来可以总结为以下几点：

1、背景

在openpyxl库诞生之前，Python中缺乏一个专门用于读取和编写Office Open XML格式(如Excel 2010及更高版本的.xlsx文件)的库。

2、起源

openpyxl库的创建是为了解决上述提到的Python在处理Excel文件时的不足，它的开发受到了PHPExcel团队的启发，因为openpyxl最初是基于PHPExcel的。

3、发展

随着时间的推移，openpyxl逐渐发展成为一个功能强大的Python库，专门用于处理Excel文件。它支持Excel 2010及更高版本的文件格式，并提供了丰富的API，用于读取、写入、修改Excel文件。

4、特点

4-1、支持.xlsx格式

openpyxl主要用于处理Excel 2010及更新版本的.xlsx文件。

4-2、读写Excel文件

使用openpyxl可以读取现有的Excel文件，获取数据，修改数据，并保存到新的文件中。

4-3、操作单元格

openpyxl允许用户按行、列或具体的单元格进行数据的读取和写入。

4-4、创建和修改工作表

用户可以创建新的工作表，复制和删除现有的工作表，设置工作表的属性等。

4-5、样式设置

openpyxl支持设置单元格的字体、颜色、边框等样式。

4-6、图表和公式

用户可以通过openpyxl创建图表、添加公式等。

4-7、支持数字和日期格式

openpyxl能够正确处理数字和日期格式，确保在Excel中显示正确的格式。

综上所述，openpyxl库的出现填补了Python在处理Excel文件时的空白，经过不断的发展和完善，成为了一个功能丰富、易于使用的Python库。

二、openpyxl库的优缺点

openpyxl库是一个用于读写Excel 2010 xlsx/xlsm/xltx/xltm文件的Python库，它基于Python，并且对于处理Excel文件提供了很多便利的功能，其主要优缺点有：

1、优点

1-1、支持现代Excel格式

openpyxl支持.xlsx格式的Excel文件，这是Excel 2010及更高版本使用的格式，也是目前广泛使用的格式。

1-2、功能丰富

openpyxl提供了创建、修改和保存Excel工作簿、工作表、单元格、图表、公式、图像等功能。

1-3、易于使用

openpyxl的API设计得相对直观，使得Python开发者能够很容易地掌握和使用。

1-4、与Excel兼容性

openpyxl能够处理Excel文件中的很多复杂特性，如公式、样式、条件格式等，这确保了与Excel的良好兼容性。

1-5、性能良好

在处理大型Excel文件时，openpyxl通常能够保持较好的性能。

1-6、社区支持

openpyxl是一个开源项目，拥有活跃的社区支持和维护，这意味着开发者可以获得帮助和修复错误的快速响应。

1-7、跨平台

openpyxl可以在不同的操作系统上运行，包括Windows、Linux和macOS等。

2、缺点

2-1、不支持旧版格式

openpyxl不支持较旧的.xls格式(Excel 97-2003)。如果需要处理这种格式的文件，需要使用其他库如xlrd和xlwt(尽管这些库也面临一些兼容性和维护问题)。

2-2、某些特性支持有限

虽然openpyxl支持许多Excel特性，但可能对于某些高级或特定的Excel功能支持有限或不支持。

2-3、内存占用

在处理大型Excel文件时，openpyxl可能会占用较多的内存。这是因为openpyxl会将整个工作簿加载到内存中。

2-4、文档和示例可能不足

尽管openpyxl的文档相对完整，但对于某些高级功能或特定用例，可能缺乏足够的示例或详细解释。

2-5.、依赖关系

openpyxl依赖于lxml和et_xmlfile这两个Python库来处理XML和Excel文件，在某些环境中，可能需要额外安装这些依赖项。

2-6、学习曲线

虽然openpyxl的API设计得相对直观，但对于初学者来说，可能需要一些时间来熟悉和掌握其用法。

三、openpyxl库的用途

openpyxl是一个用于读写Excel 2010 xlsx/xlsm/xltx/xltm文件的Python库。它是用Python编写的，不需要Microsoft Excel，并且支持多种Excel数据类型，包括图表、图像、公式等，其主要用途有：

1、读取Excel文件

你可以使用openpyxl来读取 Excel 文件中的数据，如单元格值、工作表名称、公式等，它支持多种数据类型，如字符串、数字、日期等。

2、写入Excel文件

使用openpyxl，你可以创建新的Excel文件或向现有文件添加数据，你可以设置单元格的字体、颜色、边框等样式，你还可以添加图表、图像和其他复杂的Excel功能。

3、修改Excel文件

你可以使用openpyxl来修改现有的Excel文件，如更改单元格值、添加或删除工作表等，这对于自动化数据处理和报告生成非常有用。

4、自动化

openpyxl可以与其他Python库和框架(如 pandas、numpy、matplotlib 等)结合使用，以自动化数据处理和分析任务。你可以编写脚本来从多个数据源收集数据，将数据整合到 Excel 文件中，并执行各种数据分析任务。

5、与Excel交互

如果你正在开发需要与Excel交互的应用程序或工具，openpyxl可以提供一个强大的API来处理Excel文件，它允许你读取和写入Excel文件，而无需依赖Microsoft Excel或其他第三方库。

6、数据迁移和转换

使用openpyxl，你可以轻松地将数据从Excel文件迁移到其他数据库或文件格式，或将其他数据源的数据导入到Excel文件中。

7、创建模板化的报告

你可以使用openpyxl来创建模板化的Excel报告，并在需要时填充数据，这对于需要定期生成具有一致格式和布局的报告的场景非常有用。

总之，openpyxl是一个功能强大的库，可用于在Python中处理Excel文件，它提供了灵活的API来读取、写入、修改和自动化Excel文件的各个方面。

四、如何学好openpyxl库？

1、获取openpyxl库的属性和方法

用print()和dir()两个函数获取openpyxl库所有属性和方法的列表

# ['DEBUG', 'DEFUSEDXML', 'LXML', 'NUMPY', 'Workbook', '__author__', '__author_email__', '__builtins__', '__cached__', 
# '__doc__', '__file__', '__license__', '__loader__', '__maintainer_email__', '__name__', '__package__', '__path__',
# '__spec__', '__url__', '__version__', '_constants', 'cell', 'chart', 'chartsheet', 'comments', 'compat', 'constants',
# 'descriptors', 'drawing', 'formatting', 'formula', 'load_workbook', 'open', 'packaging', 'pivot', 'reader', 'styles',
# 'utils', 'workbook', 'worksheet', 'writer', 'xml']

2、获取xlwt库的帮助信息

用help()函数获取openpyxl库的帮助信息

Help on package openpyxl:NAMEopenpyxl - # Copyright (c) 2010-2024 openpyxlPACKAGE CONTENTS_constantscell (package)chart (package)chartsheet (package)comments (package)compat (package)descriptors (package)drawing (package)formatting (package)formula (package)packaging (package)pivot (package)reader (package)styles (package)utils (package)workbook (package)worksheet (package)writer (package)xml (package)SUBMODULESconstantsDATADEBUG = FalseDEFUSEDXML = FalseLXML = TrueNUMPY = True__author_email__ = 'charlie.clark@clark-consulting.eu'__license__ = 'MIT'__maintainer_email__ = 'openpyxl-users@googlegroups.com'__url__ = 'https://openpyxl.readthedocs.io'VERSION3.1.3AUTHORSee AUTHORSFILEe:\python_workspace\pythonproject\lib\site-packages\openpyxl\__init__.py

3、用法精讲

3-1、openpyxl.load_workbook函数

3-1-1、语法

load_workbook(filename, read_only=False, keep_vba=False, data_only=False, keep_links=True, rich_text=False)Open the given filename and return the workbook:param filename: the path to open or a file-like object:type filename: string or a file-like object open in binary mode c.f., :class:`zipfile.ZipFile`:param read_only: optimised for reading, content cannot be edited:type read_only: bool:param keep_vba: preserve vba content (this does NOT mean you can use it):type keep_vba: bool:param data_only: controls whether cells with formulae have either the formula (default) or the value stored the last time Excel read the sheet:type data_only: bool:param keep_links: whether links to external workbooks should be preserved. The default is True:type keep_links: bool:param rich_text: if set to True openpyxl will preserve any rich text formatting in cells. The default is False:type rich_text: bool:rtype: :class:`openpyxl.workbook.Workbook`.. note::When using lazy load, all worksheets will be :class:`openpyxl.worksheet.iter_worksheet.IterableWorksheet`and the returned workbook will be read-only.

3-1-2、参数

3-1-2-1、filename(必须)：一个字符串或二进制模式下打开的类似文件的对象，表示要打开的Excel文件的路径或类似文件的对象。

3-1-2-2、read_only(可选)：一个布尔值，默认值为False(工作簿不是只读的，可以编辑)，表示是否以只读模式加载工作簿。如果为True，则工作簿内容不可编辑，这通常可以加快加载速度。

3-1-2-3、keep_vba(可选)：一个布尔值，默认值为False(不保留VBA内容)，表示是否保留VBA(Visual Basic for Applications)内容。但请注意，即使设置为True，openpyxl也不支持编辑或运行VBA代码。

3-1-2-4、data_only(可选)：一个布尔值，默认值为False(加载公式)，表示对于包含公式的单元格，决定是加载公式本身(默认)还是加载公式上次计算的值。

3-1-2-5、keep_links(可选)：一个布尔值，默认值为True(保留链接)，表示是否保留指向外部工作簿的链接。

3-1-2-6、rich_text(可选)：一个布尔值，默认值为False(不保留富文本格式)，表示是否保留单元格中的富文本格式(如不同颜色、字体等)。

3-1-3、功能

用于加载.xlsx或.xlsm格式的Excel文件。

3-1-4、返回值

返回值是一个工作簿(workbook)对象。

3-1-5、说明

无

3-1-6、用法

# 1、openpyxl.load_workbook函数
# 1-1、加载并读取Excel文件
from openpyxl import load_workbook
# 加载 Excel 文件
wb = load_workbook('example.xlsx')
# 获取活动工作表（通常是第一个工作表）
ws = wb.active
# 读取单元格 A1 的值
cell_value = ws['A1'].value
# 打印单元格的值
print(f"单元格 A1 的值是: {cell_value}")# 1-2、加载并写入Excel文件
from openpyxl import load_workbook
# 加载 Excel 文件
wb = load_workbook('example.xlsx')
# 获取活动工作表
ws = wb.active
# 写入数据到单元格 B1
ws['B1'] = 'Hello, Openpyxl!'
# 保存对文件的更改
wb.save('example_modified.xlsx')
# 注意：这里我们保存到了一个新的文件 'example_modified.xlsx'，而不是直接修改原始文件# 1-3、加载Excel文件并读取多个单元格
from openpyxl import load_workbook
# 加载 Excel 文件
wb = load_workbook('example.xlsx')
# 获取活动工作表
ws = wb.active
# 读取并打印单元格 A1, B1, C1 的值
for col in ['A', 'B', 'C']:cell_value = ws[f'{col}1'].valueprint(f"单元格 {col}1 的值是: {cell_value}")# 1-4、加载Excel文件并读取整个工作表的数据
from openpyxl import load_workbook
# 加载 Excel 文件
wb = load_workbook('example.xlsx')
# 获取活动工作表
ws = wb.active
# 遍历每一行并打印其内容
for row in ws.iter_rows(min_row=1, max_col=ws.max_column, values_only=True):print(row)# 1-5、加载Excel文件并添加一个新的工作表
from openpyxl import load_workbook
# 加载 Excel 文件
wb = load_workbook('example.xlsx')
# 创建一个新的工作表
ws_new = wb.create_sheet("New Sheet")
# 写入数据到新工作表的单元格 A1
ws_new['A1'] = 'This is a new sheet.'
# 保存对文件的更改
wb.save('example_with_new_sheet.xlsx')

3-2、openpyxl.Workbook.add_named_style方法

3-2-1、语法

add_named_style(self, style)Add a named style

3-2-2、参数

3-2-2-1、self(必须)：一个对实例对象本身的引用，在类的所有方法中都会自动传递。

3-2-2-2、style(必须)：

3-2-2-2-1、name(必需)：表示样式的名称。这个名称必须是唯一的，并且在工作簿的样式列表中是新的。

3-2-2-2-2、dxf(可选)：一个openpyxl.styles.DifferentialStyle对象，它定义了这个命名样式与默认样式的差异。如果你不提供这个参数，那么你将需要设置后续的其他参数来定义样式差异。

如果未提供dxf参数，你可以直接通过以下关键字参数来定义样式的差异：

3-2-2-2-3、front(可选)：一个openpyxl.styles.Font对象，定义了字体的属性，如名称、大小、颜色、下划线等。

3-2-2-2-4、border(可选)：一个openpyxl.styles.Border对象，定义了边框的样式，如线条样式、颜色等。

3-2-2-2-5、fill(可选)：一个openpyxl.styles.Fill对象，定义了单元格的填充颜色或图案。

3-2-2-2-6、number_format(可选)：一个字符串，定义了数字格式。例如，'0.00%' 用于百分比格式。

3-2-2-2-7、protection(可选)：一个openpyxl.styles.Protection对象，定义了单元格的保护设置，如是否锁定或隐藏。

3-2-2-2-8、alignment(可选)：一个openpyxl.styles.Alignment对象，定义了文本的对齐方式。

3-2-2-2-9、extension_list(可选)：一个列表，包含了扩展的样式信息(较少使用)。

3-2-2-2-10、builtionId(可选)：一个整数，用于指定一个内置的样式ID(较少使用)。

3-2-3、功能

用于向工作簿添加一个命名的样式(named style)，命名的样式允许你为多个单元格或范围应用一组预定义的格式设置，从而简化格式管理。

3-2-4、返回值

方法的主要目的是向工作簿添加一个命名的样式，因此它可能不返回任何有用的值，或者返回None。

3-2-5、说明

3-2-5-1、如果同时提供了dxf和其他关键字参数，那么dxf将被优先使用，并且其他关键字参数将被忽略。

3-2-5-2、在创建命名样式之前，最好先检查是否已经存在相同名称的样式，以避免冲突。

3-2-5-3、使用命名样式后，可以通过设置单元格的style属性来应用这个样式。

3-2-6、用法

# 2、openpyxl.Workbook.add_named_style方法
from openpyxl import Workbook
from openpyxl.styles import Font, Color, PatternFill, Border, Side, Alignment, NamedStyle
# 创建一个新的工作簿
wb = Workbook()
ws = wb.active
# 定义一个命名样式的属性
named_style_attrs = {'name': 'MyNamedStyle','font': Font(name='Calibri', size=14, bold=True, italic=True, color=Color('FF0000')),  # 红色字体'fill': PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid'),  # 黄色填充'border': Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'),bottom=Side(style='thin')),  # 细边框'alignment': Alignment(horizontal='center', vertical='center'),  # 居中对齐'number_format': '0.00%',  # 百分比格式
}
# 将命名样式添加到工作簿
named_style = NamedStyle(**named_style_attrs)
wb.add_named_style(named_style)
# 在单元格 A1 中应用命名样式
ws['A1'] = 'Hello, World!'
ws['A1'].style = 'MyNamedStyle'
# 在另一个单元格中设置不同的样式属性作为对比
ws['B1'] = 'No Style'
ws['B1'].font = Font(name='Arial', size=12)
# 保存工作簿
wb.save('styled_workbook.xlsx')

3-3、openpyxl.Workbook.close方法

3-3-1、语法

close(self)Close workbook file if open. Only affects read-only and write-only modes

3-3-2、参数

3-3-2-1、self(必须)：一个对实例对象本身的引用，在类的所有方法中都会自动传递。

3-3-3、功能

用于关闭文件句柄，释放与文件关联的资源。

3-3-4、返回值

没有返回值(或者返回None)，它的主要目的是执行清理操作。

3-3-5、说明

无

3-3-6、用法

openpyxl的工作簿对象本身并不持有需要显式关闭的资源。它只是一个Python对象，当不再需要时，Python的垃圾回收机制会负责清理它。

3-4、openpyxl.Workbook.copy_worksheet方法

3-4-1、语法

copy_worksheet(self, from_worksheet)Copy an existing worksheet in the current workbook.. warning::This function cannot copy worksheets between workbooks.worksheets can only be copied within the workbook that they belong:param from_worksheet: the worksheet to be copied from:return: copy of the initial worksheet

3-4-2、参数

3-4-2-1、self(必须)：一个对实例对象本身的引用，在类的所有方法中都会自动传递。

3-4-2-2、from_worksheet(必须)：一个Worksheet对象，表示源工作表。

3-4-3、功能

用于复制一个工作表(Worksheet)到同一个工作簿(Workbook)或另一个工作簿中。

3-4-4、返回值

返回值是一个新的Worksheet对象，它表示从原始工作表复制而来的新工作表。这个新工作表与原始工作表具有相同的内容和格式(在可能的情况下)，但是它是作为一个新的工作表对象存在的。

3-4-5、说明

无

3-4-6、用法

# 4、openpyxl.Workbook.copy_worksheet方法
import openpyxl
# 打开工作簿
workbook = openpyxl.load_workbook('example.xlsx')
# 选择要复制的工作表
source_sheet = workbook['Sheet1']
# 创建目标工作表
target_sheet = workbook.copy_worksheet(source_sheet)
target_sheet.title = 'NewSheet'
# 保存工作簿
workbook.save('example_copy.xlsx')

3-5、openpyxl.Workbook.create_chartsheet方法

3-5-1、语法

create_chartsheet(self, title=None, index=None)

3-5-2、参数

3-5-2-1、self(必须)：一个对实例对象本身的引用，在类的所有方法中都会自动传递。

3-5-2-2、title(可选)：一个字符串(默认值为None)，表示新图表工作表的标题(即工作表名称)。如果未提供此参数，则默认会使用一个自动生成的名称，如“Chart 1”。

3-5-2-3、index(可选)：一个整数(默认值为None)，表示新图表工作表在工作簿中的位置索引。默认情况下，新图表工作表会被添加到工作簿的末尾。如果你指定了一个索引值，openpyxl会尝试将新的图表工作表插入到该索引位置。如果指定的索引位置已经存在工作表，openpyxl可能会将其他工作表向后移动以腾出空间。但是，请注意，在某些版本的 openpyxl 中，index参数可能不被支持或可能不起作用。

3-5-3、功能

在当前工作簿中创建一个新的图表工作表(Chartsheet)。

3-5-4、返回值

返回一个Chartsheet对象，代表新创建的图表工作表。

3-5-5、说明

图表工作表是一种特殊的工作表，它只显示图表，而不显示数据。

3-5-6、用法

# 5、openpyxl.Workbook.create_chartsheet方法
from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference, Series
from openpyxl.worksheet.datavalidation import DataValidation
# 创建一个新的工作簿
wb = Workbook()
ws = wb.active
# 在工作表中添加一些示例数据
ws.append(["Number", "Batch 1", "Batch 2"])
ws.append([2, 40, 30])
ws.append([3, 40, 25])
ws.append([4, 50, 30])
ws.append([5, 30, 10])
ws.append([6, 25, 5])
# 创建一个柱状图
values = Reference(ws, min_col=2, min_row=1, max_row=6)
categories = Reference(ws, min_col=1, min_row=2, max_row=6)
chart = BarChart()
chart.title = "Batch Comparison"
chart.x_axis.title = "Number"
chart.y_axis.title = "Value"
data = Series(values, categories)
chart.append(data)
# 创建一个图表工作表，指定标题和位置（假设我们要将它放在第二个位置）
index_to_insert = 1  # 注意：索引是基于0的，所以1表示第二个位置
cs = wb.create_chartsheet(title="Chart 1", index=index_to_insert)
# 将图表添加到图表工作表中
cs.add_chart(chart)
# 保存工作簿
wb.save("chartsheet_example.xlsx")