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

一、 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、获取openpyxl库的帮助信息

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

Help on package openpyxl:

NAME
    openpyxl - # Copyright (c) 2010-2024 openpyxl

PACKAGE CONTENTS
    _constants
    cell (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)

SUBMODULES
    constants

DATA
    DEBUG = False
    DEFUSEDXML = False
    LXML = True
    NUMPY = True
    __author_email__ = '[email protected]'
    __license__ = 'MIT'
    __maintainer_email__ = '[email protected]'
    __url__ = 'https://openpyxl.readthedocs.io'

VERSION
    3.1.3

AUTHOR
    See AUTHORS

FILE
    e:\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'].value
    print(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")

五、推荐阅读

1、Python筑基之旅

2、Python函数之旅

3、Python算法之旅

4、Python魔法之旅

5、博客个人主页