openpyxl3.0.3 中文手册--性能及优化模式

badboy

性能

openpyxl 尝试平衡功能和性能,但将重点放在优化功能上,因为建立 API 以后,性能调整将变得更加容易。 与其他库和应用程序相比,openpyxl 内存使用率很高,约为原始文件大小的 50 倍,例如 50 MB 的 Excel 文件使用内存为 2.5GB。 由于许多用例仅涉及读取或写入文件,因此“优化模式”模式意味着问题不大。

基准测试

所有基准测试都是综合性的,并且高度依赖于硬件,但是它们仍然可以提供指示。

写性能

可以调整基准代码以使用更多的工作表,并调整字符串数据的比例。由于使用的 Python 版本也会显著影响性能,因此驱动程序脚本也可以用于在有 tox 环境的不同 Python 版本上进行测试。
openpyxl 性能与出色的库 xlsxwriter 进行了比较:

Versions:
python: 3.6.9
openpyxl: 3.0.1
xlsxwriter: 1.2.5

Dimensions:
    Rows = 1000
    Cols = 50
    Sheets = 1
    Proportion text = 0.10

Times:
    xlsxwriter            :   0.59
    xlsxwriter (optimised):   0.54
    openpyxl              :   0.73
    openpyxl (optimised)  :   0.61


Versions:
python: 3.7.5
openpyxl: 3.0.1
xlsxwriter: 1.2.5

Dimensions:
    Rows = 1000
    Cols = 50
    Sheets = 1
    Proportion text = 0.10

Times:
    xlsxwriter            :   0.65
    xlsxwriter (optimised):   0.53
    openpyxl              :   0.70
    openpyxl (optimised)  :   0.63


Versions:
python: 3.8.0
openpyxl: 3.0.1
xlsxwriter: 1.2.5

Dimensions:
    Rows = 1000
    Cols = 50
    Sheets = 1
    Proportion text = 0.10

Times:
    xlsxwriter            :   0.54
    xlsxwriter (optimised):   0.50
    openpyxl              :   1.10
    openpyxl (optimised)  :   0.57

写性能

openpyxl 使用以前的 bug 报告文件来衡量性能,并与较早的 xlrd 库进行比较。xlrd 主要用于 .XLS 较旧的 BIFF 文件格式,它对XLSX的支持很有限。
在处理文件时基准测试代码展示了选择正确选项的重要性。在这种情况下,禁用外部链接将停止 openpyxl 打开链接工作表的缓存副本。
openpyxl 和 xlrd 的主要区别是:openpyxl 的只读模式几乎可以立即打开工作簿,使得它适应多个进程,这也大大减少了内存的使用;xlrd 不能自动将日期和时间转换为 Python 日期时间,它会相应地注释单元格,但是在客户端代码中这样做会大大降低性能。

Versions:
python: 3.6.9
xlread: 1.2.0
openpyxl: 3.0.1

openpyxl, read-only
    Workbook loaded 1.14s
    OptimizationData 23.17s
    Output Model 0.00s
    >>DATA>> 0.00s
    Store days 0% 23.92s
    Store days 100% 17.35s
    Total time 65.59s
    0 cells in total

Versions:
python: 3.7.5
xlread: 1.2.0
openpyxl: 3.0.1

openpyxl, read-only
    Workbook loaded 0.98s
    OptimizationData 21.35s
    Output Model 0.00s
    >>DATA>> 0.00s
    Store days 0% 20.70s
    Store days 100% 16.16s
    Total time 59.19s
    0 cells in total

Versions:
python: 3.8.0
xlread: 1.2.0
openpyxl: 3.0.1

openpyxl, read-only
    Workbook loaded 0.90s
    OptimizationData 19.58s
    Output Model 0.00s
    >>DATA>> 0.00s
    Store days 0% 19.35s
    Store days 100% 15.02s
    Total time 54.85s
    0 cells in total

并行化

读取工作表会占用大量 CPU 资源,这限制了通过并行化获得的任何好处。如果主要工作是转储工作簿的内容的话,那么可以使用 openpyxl 的只读模式利用多个CPU打开多个工作簿实例。
使用与读取性能相同的源文件的示例代码表明,由于创建了额外的 Python 进程,则可以以较小的开销合理地扩展性能。

优化模式

只读模式

当打开或写入的 XLSX 文件非常大时,openpyxl 中的通用例程将无法处理该负载,但有两种模式可以使得在恒定的内存消耗下读写无限量的数据。
现介绍一下 openpyxl.worksheet._read_only.ReadOnlyWorksheet:

from openpyxl import load_workbook
wb = load_workbook(filename='large_file.xlsx', read_only=True)
ws = wb['big_data']

for row in ws.rows:
    for cell in row:
        print(cell.value)

警告:openpyxl.worksheet._read_only.ReadOnlyWorksheet 是只读模式

只读模式返回的单元格不是常规的 openpyxl.cell.cell.Cell,而是 openpyxl.cell._read_only.ReadOnlyCell。

工作表尺寸
只读模式依赖于创建文件的应用程序和库,该文件提供有关工作表的正确信息,尤其是文件已用部分的尺寸。可以使用 ws.calculate_dimension() 检查工作表尺寸,如果返回的维度不正确,使用 max_row 和 max_column 属性重置工作表尺寸后可以使用该文件:

ws.reset_dimensions()

只写模式

同样,常规的 openpyxl.worksheet.worksheet.Worksheet 被更快的 openpyxl.worksheet._write_only.WriteOnlyWorksheet 取代。使用openpyxl.worksheet._write_only.WriteOnlyWorksheet 转储大量数据时需要提前安装 lxml。

from openpyxl import Workbook
wb = Workbook(write_only=True)
ws = wb.create_sheet()

# 填充 100行 x 100列 的数据表
for irow in range(100):
    ws.append(['%d' % i for i in range(200)])
# 保存文件
wb.save('new_big_file.xlsx') # doctest: +SKIP

若单元格要使用样式或注释,需要 openpyxl.cell.WriteOnlyCell() 方法:

from openpyxl import Workbook

wb = Workbook(write_only = True)
ws = wb.create_sheet()

from openpyxl.cell import WriteOnlyCell
from openpyxl.comments import Comment
from openpyxl.styles import Font

cell = WriteOnlyCell(ws, value="hello world")
cell.font = Font(name='Courier', size=36)
cell.comment = Comment(text="A comment", author="Author's Name")
ws.append([cell, 3.14, None])
wb.save('write_only_file.xlsx')

以上代码创建一个只有一个工作表的只写工作簿,并在工作表中添加一行,包含3个单元格,一个带有自定义字体和注释的文本单元格、一个浮点数单元格和一个空单元格(无论如何将被丢弃)。

警告:
1.与普通工作簿不同,新创建的只写工作簿不包含任何工作表, 必须使用 create_sheet() 方法专门创建工作表。
2.在只写工作簿中,只能使用 append() 添加行, 无法使用 cell() 或 iter_rows() 在任意位置写入(或读取)单元格。
3.只写工作簿能够导出无限量的数据(甚至比Excel实际处理的数据还要多),同时将内存使用量保持在 10Mb 以下。
4.只写工作簿只能保存一次,之后每次保存工作簿或 用append() 方法追加数据到现有工作表都会引发 openpyxl.utils.exceptions.WorkbookAlreadySaved 异常。
5.在单元格实际添加到工作表之前,必须创建所添加单元格所有内容。例如,应在添加单元格之前设置 freeze_panes。

阅读 2.6k
4 声望
0 粉丝
0 条评论
你知道吗?

4 声望
0 粉丝
文章目录
宣传栏