性能

OpenPYXL试图平衡功能和性能。如果有疑问,我们将重点放在功能上而不是优化上:一旦建立了API,性能调整就更容易了。与其他库和应用程序相比,内存使用率相当高,大约是原始文件大小的50倍,例如,50 MB Excel文件的内存使用率为2.5 GB。由于许多用例只涉及读或写文件,因此 只读模式 模式意味着这不是一个问题。

基准点

所有基准点都是合成的,非常依赖于硬件,但它们仍然可以给出指示。

写入性能

这个 benchmark code 可以调整以使用更多的工作表,并调整字符串数据的比例。因为所使用的Python版本也会显著影响性能,所以 driver script 也可以用于在tox环境中使用不同的python版本进行测试。

性能与优秀的可选库XLSxWriter进行了比较


Versions:
python: 2.7.1
openpyxl: 2.6.0dev
xlsxwriter: 1.0.9

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

Times:
    xlsxwriter            :   2.45
    xlsxwriter (optimised):   2.64
    openpyxl              :   3.96
    openpyxl (optimised)  :   2.78


Versions:
python: 3.5.6
openpyxl: 2.6.0dev
xlsxwriter: 1.0.9

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

Times:
    xlsxwriter            :   2.29
    xlsxwriter (optimised):   2.22
    openpyxl              :   4.35
    openpyxl (optimised)  :   2.90


Versions:
python: 3.6.6
openpyxl: 2.6.0dev
xlsxwriter: 1.0.9

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

Times:
    xlsxwriter            :   2.32
    xlsxwriter (optimised):   2.22
    openpyxl              :   3.35
    openpyxl (optimised)  :   2.64


Versions:
python: 3.7.0
openpyxl: 2.6.0dev
xlsxwriter: 1.0.9

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

Times:
    xlsxwriter            :   2.34
    xlsxwriter (optimised):   2.23
    openpyxl              :   2.93
    openpyxl (optimised)  :   2.49

读取性能

性能是使用以前提供的文件来衡量的 bug report 与旧的XLRD库相比。XLRD主要用于.xls文件的旧BIFF文件格式,但它对XLSX的支持有限。

的代码 benchmark 显示使用文件时选择正确选项的重要性。在这种情况下,禁用外部链接会停止OpenPYXL打开链接工作表的缓存副本。

库之间的一个主要区别是OpenPYXL的只读模式几乎可以立即打开一个工作簿,使其适合多个进程,这也可以显著地读取内存使用。XLRD也不会自动将日期和时间转换为python日期时间,尽管它会相应地注释单元格,但在客户机代码中这样做会显著降低性能。


Versions:
python: 2.7.1
xlread: 1.1.0
openpyxl: 2.6.0dev

xlrd
    Workbook loaded 66.72s
    OptimizationData 0.19s
    Output Model 0.00s
    >>DATA>> 0.00s
    Store days 0% 0.07s
    Store days 100% 0.06s
    Total time 67.04s

openpyxl
    Workbook loaded 106.64s
    OptimizationData 0.00s
    Output Model 0.00s
    >>DATA>> 0.00s
    Store days 0% 0.00s
    Store days 100% 0.00s
    Total time 106.64s

openpyxl, read-only
    Workbook loaded 0.97s
    OptimizationData 24.77s
    Output Model 0.00s
    >>DATA>> 0.00s
    Store days 0% 24.19s
    Store days 100% 19.18s
    Total time 69.11s

openpyxl, read-only, values only
    Workbook loaded 0.95s
    OptimizationData 21.84s
    Output Model 0.00s
    >>DATA>> 0.00s
    Store days 0% 21.94s
    Store days 100% 17.09s
    Total time 61.82s


Versions:
python: 3.5.6
xlread: 1.1.0
openpyxl: 2.6.0dev

xlrd
    Workbook loaded 67.13s
    OptimizationData 0.24s
    Output Model 0.00s
    >>DATA>> 0.00s
    Store days 0% 0.08s
    Store days 100% 0.07s
    Total time 67.52s

openpyxl
    Workbook loaded 115.50s
    OptimizationData 0.00s
    Output Model 0.00s
    >>DATA>> 0.00s
    Store days 0% 0.00s
    Store days 100% 0.00s
    Total time 115.50s

openpyxl, read-only
    Workbook loaded 1.25s
    OptimizationData 38.46s
    Output Model 0.00s
    >>DATA>> 0.00s
    Store days 0% 29.54s
    Store days 100% 22.78s
    Total time 92.04s

openpyxl, read-only, values only
    Workbook loaded 1.30s
    OptimizationData 27.08s
    Output Model 0.00s
    >>DATA>> 0.00s
    Store days 0% 27.09s
    Store days 100% 21.13s
    Total time 76.59s


Versions:
python: 3.6.7
xlread: 1.1.0
openpyxl: 2.6.0dev

xlrd
    Workbook loaded 52.04s
    OptimizationData 0.23s
    Output Model 0.00s
    >>DATA>> 0.00s
    Store days 0% 0.08s
    Store days 100% 0.07s
    Total time 52.42s

openpyxl
    Workbook loaded 91.79s
    OptimizationData 0.00s
    Output Model 0.00s
    >>DATA>> 0.00s
    Store days 0% 0.00s
    Store days 100% 0.00s
    Total time 91.79s

openpyxl, read-only
    Workbook loaded 1.08s
    OptimizationData 25.53s
    Output Model 0.00s
    >>DATA>> 0.00s
    Store days 0% 23.02s
    Store days 100% 17.97s
    Total time 67.61s

openpyxl, read-only, values only
    Workbook loaded 1.08s
    OptimizationData 20.90s
    Output Model 0.01s
    >>DATA>> 0.00s
    Store days 0% 21.05s
    Store days 100% 16.15s
    Total time 59.20s


Versions:
python: 3.7.1
xlread: 1.1.0
openpyxl: 2.6.0dev

xlrd
    Workbook loaded 49.78s
    OptimizationData 0.22s
    Output Model 0.00s
    >>DATA>> 0.00s
    Store days 0% 0.07s
    Store days 100% 0.06s
    Total time 50.13s

openpyxl
    Workbook loaded 88.81s
    OptimizationData 0.00s
    Output Model 0.00s
    >>DATA>> 0.00s
    Store days 0% 0.00s
    Store days 100% 0.00s
    Total time 88.81s

openpyxl, read-only
    Workbook loaded 0.94s
    OptimizationData 21.73s
    Output Model 0.00s
    >>DATA>> 0.00s
    Store days 0% 24.94s
    Store days 100% 17.21s
    Total time 64.82s

openpyxl, read-only, values only
    Workbook loaded 0.97s
    OptimizationData 19.94s
    Output Model 0.00s
    >>DATA>> 0.00s
    Store days 0% 19.88s
    Store days 100% 15.42s
    Total time 56.20s