代码之家 › 专栏 › 技术社区 › Justin Frahm

Openpyxl:如何解决打开工作簿的问题

openpyxl excel python

Justin Frahm · 技术社区 · 7 年前

tl;博士: 找出挂断的原因有哪些好的第一步 openpyxl 当它试图加载工作簿时?

长版本: 所以,我遇到了一些“为什么它不起作用”之类的问题 openpyxl 但在发现/解决问题的实际尝试方面,还没有看到太多。

我刚开始退房 openpyxl 这看起来很有希望,但在刚开始的时候,我遇到了一个问题:我有很多非常复杂的工作簿。我想做一个很好的尝试,至少从他们那里读取数据。我正在使用的工作簿不是 巨大的 (~ 750kB),但它确实包含很多内容:条件格式、数据验证、命名范围、vba内容等。当我尝试打开工作簿时,我收到一条关于数据验证的警告(好的,没什么大不了的),但随后它启动CPU,对长的时间——我不知道它是否会结束,因为不可避免地,我需要继续前进,所以我放弃了。不管怎样,如果加载完成的话,它的速度太慢了,没有什么用处。

因此,如果有人能建议一些坚实的第一步来确定阻碍是什么,我会很高兴,这样我就可以通过从工作簿中删除有冒犯性的内容,或者最好通过在 python 一边处理事情更顺利。

为了清楚起见,以下是我开始使用的两行代码:

from openpyxl import Workbook, load_workbook
wb = load_workbook('book.xlsm')

1 回复 | 直到 7 年前

Justin Frahm 7 年前

正如@charlecklark所猜测的,我的特定工作簿的问题是整个列的数据验证集。为了给这个问题提供一个令人满意的答案,我做了一些实验,试着看看我如何能够自己推断出这一点。由于我认为我不可能写一个能真正涵盖其他任何人问题的方法,我尝试了两种方法来看待这个问题,基于@BoarGules和@Charlecklark的建议,并将其作为示例:

方法1:将工作簿拆分为较小的部分,比较加载

如果你想弄清楚是什么阻碍了这个过程,我建议你好好想想工作簿中包含的内容以及可能导致的原因 openpyxl 要做一系列额外的处理(在方法2中详细介绍)。与其简单地将每个工作表拆分为一个新工作簿并尝试加载每个工作表(我这样做了,但大多数较小的工作簿都不会加载-我的大多数工作表基本上都有相同的结构和加载问题),不如试着思考您有哪些内容-数据验证、条件格式、您有哪些内容-并一次删除一种内容类型。

当我删除工作簿中的所有数据篡改时,它突然像快照一样加载了!

方法2:更加熟悉源代码并尝试 profile -通过你的方式了解问题所在

我发现这个方法更令人满意,因为至少现在我对如何使用这个方法有了一个(非常模糊的)想法 openpyxl 加载工作簿,但此方法确实需要遍历源代码来思考问题-如果不想这样做,请坚持使用方法1。此方法还需要有一个好的示例工作簿,该工作簿加载OK以进行比较。对我来说,因为我的第一次尝试是跟随@charlechurk的猜测并删除所有数据验证,所以我使用“固定”工作簿进行比较,这有点欺骗,但很好。

与我的好的工作簿中,我在 workbook_load 函数以查看其外观。我发现按“tottime”或花费的总时间对结果排序最有用 在里面 每个功能:

>>>import profile
>>>from openpyxl import load_workbook
>>>profile.run('wb = load_workbook("good.xlsm")', sort='tottime')

     4228306 function calls (4186125 primitive calls) in 12.859 seconds

   Ordered by: internal time

   ncalls  tottime  percall  cumtime  percall filename:lineno(function)
    84364    1.828    0.000    6.234    0.000 worksheet.py:138(parse_cell)
   267315    0.625    0.000    0.625    0.000 :0(get)
   197422    0.594    0.000    0.953    0.000 ElementTree.py:1286(read_events)
      284    0.578    0.002    0.578    0.002 :0(feed)
12986/8974    0.516    0.000    2.125    0.000 serialisable.py:42(from_tree)
   538565    0.500    0.000    0.500    0.000 :0(isinstance)
    89339    0.500    0.000    1.000    0.000 cell.py:43(coordinate_from_string)
24877/9318    0.438    0.000    0.984    0.000 serialisable.py:187(__hash__)
   193137    0.422    0.000    0.422    0.000 :0(match)
     5119    0.422    0.000    6.734    0.001 worksheet.py:259(parse_row)
    73194    0.406    0.000    0.562    0.000 base.py:40(__set__)
   168920    0.375    0.000    0.375    0.000 :0(find)
   197144    0.344    0.000    1.906    0.000 ElementTree.py:1218(iterator)
   251298    0.312    0.000    0.312    0.000 :0(getattr)
    84364    0.312    0.000    0.812    0.000 cell.py:106(__init__)
    84364    0.297    0.000    1.203    0.000 cell.py:181(coordinate_to_tuple)
...

我运行了加载此工作簿和其他一些工作簿的配置文件,看起来执行时间主要花在工作表上。py模块(如上所述)和serialisable中。py模块,我认为这很有意义,因为这是大部分读取/处理数据的地方。

作为比较,当我让 令人不快的 工作簿加载一段时间后中止,以下是我对配置文件的了解:

>>>import profile
>>>from openpyxl import load_workbook
>>>profile.run('wb = load_workbook("bad.xlsm")', sort='tottime')

     14111962 function calls (14076527 primitive calls) in 27.797 seconds

   Ordered by: internal time

   ncalls  tottime  percall  cumtime  percall filename:lineno(function)
  3045757    9.797    0.000   20.359    0.000 cell.py:157(rows_from_range)
6091514/6091513    7.062    0.000   10.562    0.000 cell.py:166(<genexpr>)
  3045783    3.500    0.000    3.500    0.000 :0(format)
       19    2.797    0.147   23.156    1.219 :0(extend)
       19    0.469    0.025   23.625    1.243 datavalidation.py:59(expand_cell_ranges)
   366802    0.375    0.000    0.375    0.000 :0(isinstance)
24877/9318    0.344    0.000    0.672    0.000 serialisable.py:187(__hash__)
    15947    0.234    0.000    1.125    0.000 worksheet.py:138(parse_cell)
12686/8831    0.219    0.000   25.172    0.003 serialisable.py:42(from_tree)
   250829    0.188    0.000    0.188    0.000 :0(getattr)
       90    0.172    0.002    0.172    0.002 :0(feed)
    63452    0.172    0.000    0.297    0.000 base.py:40(__set__)
    54974    0.125    0.000    0.125    0.000 :0(get)
    44462    0.125    0.000    0.125    0.000 :0(match)
    18172    0.109    0.000    0.203    0.000 cell.py:43(coordinate_from_string)
    56006    0.094    0.000    0.156    0.000 ElementTree.py:1286(read_events)
    83605    0.078    0.000    0.078    0.000 base.py:25(__set__)
    17709    0.078    0.000    0.141    0.000 sequence.py:24(__set__)
...

因此,查看此概要文件,您可以看到大部分执行时间都花在处理单元地址上( rows_from_range )而不是像我们在第一个概要文件中看到的那样查看实际数据。我想这不是我想要的。如果你看一下profile表中的第五行,我们也花了很多时间在( cumtime 列)的 datavalidation.py 作用 expand_cell_ranges ,尽管它只被调用过几次,但在另一个配置文件的顶部附近没有显示。当我翻阅源代码时,我看到了 展开\u cell\u范围 函数调用 \u范围中的行\u 循环中的函数!我认为,从这里,我们可以合理地得出结论,在这种情况下,数据验证导致 openpyxl 尝试处理一整批没有任何有用信息的单元格地址。因为我已经知道我的工作簿为整列空单元格设置了数据验证,所以我认为这是对诊断的有力确认。

如果任何阅读本文的人需要尝试反向工程来诊断无法加载的工作簿,我会将上面的第一个配置文件与加载问题工作簿的配置文件进行比较,看看有什么变化。这至少应该为猜测提供一个良好的起点 为什么? 它变了。