openpyxl返回公式系列的空单元格值 [英] openpyxl returning empty cell values for formula series

问题描述

重要:python 3.4.3 | openpyxl 2.2.3 | Excel 2013

Vitals: python 3.4.3 | openpyxl 2.2.3 | Excel 2013

就像每个人都知道的那样，您可以使用Excel的填充手柄快速在列[1,2,3,4,5,6，etc]下建立数字系列，这也适用于公式[= sum(B1， C1)，= sum(B2，C2)，= sum(B3，C3)等].但是，当我尝试使用openpyxl来获取通过Excel中的填充手柄添加的公式时，这些单元格被报告为空.

Like everyone knows you can use Excel's fill handle to quickly set up a number series down a column [1,2,3,4,5,6,etc] and this works with formula as well [=sum(B1,C1), =sum(B2,C2), =sum(B3,C3), etc]. However when I attempt to use openpyxl to nab formula added using the fill handle in Excel, those cells are reported to be empty.

它与原始代码不兼容，因此我创建了一个包含以下内容的简单脚本:

It didn't work with my original code so I created a simple script containing this:

for row in ws.rows:
  for cell in row:
    print('my value is:',cell.value)

并针对这个很小的(aww)Excel文件运行它.我在A1上使用了填充手柄，以使用公式填充A2-A5. (我只是为了清楚起见显示公式，实际上我在A列中看到的是结果.)

And ran it against this tiny (aww) Excel file. I used the fill handle on A1 to populate A2-A5 with formulas. (I'm just showing the formula for clarity, what I actually see in column A are the results.)

#      A       B   C
1 =SUM(B1:C1)  1   2
2 =SUM(B2:C2)  10  20
3 =SUM(B3:C3)  20  25
4 =SUM(B4:C4)  90  42
5 =SUM(B5:C5)  64  4

这是控制台中的输出:

my value is: =SUM(B1:C1)
my value is: 1
my value is: 2
my value is: =SUM(B2:C2)
my value is: 10
my value is: 20
my value is: =
my value is: 20
my value is: 25
my value is: =
my value is: 90
my value is: 42
my value is: =
my value is: 64
my value is: 4

openpyxl读取A1和A2中的公式，但不读取A3-A5中的公式.我针对几个简单的数字序列尝试了此代码段，并且openpyxl在读取这些单元格时没有问题...问题似乎仅在于公式.我缺少明显的东西吗?

openpyxl reads the formula in A1 and A2 but not A3-A5. I tried this snippet against a couple of simple number series and openpyxl didn't have a problem reading those cells...the issue just seems to be with formulas. Am I missing something obvious?

推荐答案

更新3/23/16

此问题已在openpyxl的最新版本(我认为是2.3及更高版本)中得到解决.

Update 3/23/16

This issue has been resolved in recent versions of openpyxl (2.3, and beyond, I believe) PR request

这是openpyxl的已知缺点(错误报告).简短的答案是，当您上下拖动公式时，Excel将使用共享公式"，而openpyxl则无法解析该公式.如果您确实需要此功能，则可以尝试 xlrd .

This is a known shortcoming of openpyxl (bug report). The short answer is that when you drag a formula down or across, Excel uses a "shared formula," which cannot be parsed by openpyxl. If you really need this functionality, you may want to try xlrd.

这让我很烦，所以这是一个非常棘手的解决方法(已在Python 2.7上测试).首先，您必须从此处 获取Robin Macharg的Excel公式解析器.将其另存为xlparse.py在Python路径中的某个位置.现在，从此要点运行代码 ，您应该可以使用get_formula函数获取任何单元格的公式.在大多数情况下，该函数仅返回cell.value，但是只要该函数遇到共享公式，它就会给出正确的值.

This was bugging me, so here's a very hackish workaround (Tested on Python 2.7). First you have to get Robin Macharg's Excel formula parser from here. Save it somewhere in your Python path as xlparse.py. Now, run the code from this gist, and you should be able to use the get_formula function to get the formula for any cell. In most cases, the function simply returns cell.value, but whenever the function encounters a shared formula, it gives the correct value.

注意: openpyxl.reader.worksheet.Worksheet的Monkeypatch可以使openpyxl.load_workbook使用我们的补丁类.确保在调用load_workbook之前运行此代码.如果要以其他方式创建工作簿，则必须在适当的位置修补Worksheet.

Note: The monkeypatch to openpyxl.reader.worksheet.Worksheet is so that openpyxl.load_workbook uses our patched class. Make sure to run this code before calling load_workbook. If you are creating the workbook a different way, you'll have to patch Worksheet in the appropriate place.

这篇关于openpyxl返回公式系列的空单元格值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！