Python win32com模块操作Excel：获取公式依赖关系

发布于：2019-05-02 | 分类：process automation , python/vba/cpp

Excel中某个单元格的计算结果可以作为其他单元格计算公式的一部分，即二者存在依赖/引用的关系。尤其是对于复杂的表格，这种依赖关系可以方便我们追踪数据的相互作用。Excel已经在Formula->Formula Auditing组提供了可视化公式引用关系的功能：Trace Precedents和Trace Dependents，同时也可以通过相应的API来编程获取引用关系。本文即记录使用Python的win32com模块来实现之。

问题描述

假设几个单元格的公式如下：

## sheet1:
A1=1
B1=A1+1
B2=A1+2

## sheet2
B1=Sheet1!A1+1
B1=Sheet1!A1+2

显然B1的值依赖于A1，Excel中将B1称为A1的dependents，A1称为B1的Precedents。同理B2，以及sheet2中的B1、B2都是A1的Dependents；并且，B1和B2也可以有自己的Dependents。这样的话，sheet1!B1、sheet1!B2、sheet2!B1和sheet2!B2是A1的直接Dependents，而sheet1!B1、sheet1!B2、sheet2!B1和sheet2!B2的Dependents都是A1的间接Dependents。

在Excel中将光标定位到A1，然后点击Formula->Formula Auditing->Trace Dependents，会出现三个表示引用关系的箭头。其中两个实线箭头指向当前工作表内的两个引用sheet1!B1和sheet1!B2，虚线箭头指向其他工作表的引用即sheet2!B1和sheet2!B2。双击虚线箭头会出现Go To对话框，其中列出了sheet2!B1和sheet2!B2，选择相应记录即可跳转到目标位置。

Trace Dependents操作及其意义同理。

接下来，我们的问题是如何使用Python的win32com模块找出A1的所有Dependents。

准备工作

win32com模块是pywin32的一部分，因此在命令行运行下面指令进行安装

pip install pywin32

关于win32com操作Excel的基本代码可以参考

Python win32com模块操作Excel：基本读写

Python win32com模块操作Excel：VBA模块读写

当前工作表内的引用

ExcelRange对象的两个属性precedents和dependents记录了当前工作表范围内的依赖/引用关系，本例中

Range('A1').dependents = Range('B1'), Range('B2')

考虑到该属性不能在某个单元格不存在公式上的引用关系时使用，则可以组织Python代码如下：

def get_direct_precedents(rng):
    '''get all precedents of current range from current sheet'''
    try:
        res = [x.address for x in rng.precedents]
    except Exception as e:
        res = []

    return res

def get_direct_dependents(rng):
    '''get all dependents of current range from current sheet'''
    try:
        res = [x.address for x in rng.dependents]
    except Exception as e:
        res = []

    return res

工作簿内的引用

本例中，如何才能获取到sheet2工作表中B1对sheet1中A1的引用关系呢？我们需要使用更一般的获取引用关系的函数NavigateArrow 1：

Range.NavigateArrow (TowardPrecedent, ArrowNumber, LinkNumber)

这个函数名称很形象，对应Excel中显示依赖/引用箭头的操作，其中，

  • TowardPrecedent表示查找箭头的方向，True表示查找precedents，False表示查找dependents
  • ArrowNumber表示箭头的序号，例如本例中有三个箭头，则依次取为1，2，3
  • LinkNumber仅对工作表外引用有效，表示工作表外引用的序号。例如本例中的sheet2!B1和sheet2!B2，对应了序号1，2

这样的话，Range('A1').NavigateArrow(False, 3, 2)即表示Sheet2!B2。

NavigateArrow仅仅获取了直接引用，继续递归直接引用即可获取所有的间接引用了。结合此思路，参考代码如下：

def get_all_dependents(xlApp, rng):
    '''get all dependents of current range from all worksheets'''
    # show dependents arrows first
    rng.ShowDependents()

    i, j = 0, 0
    res = set()
    while True:
        i += 1
        # navigate to next dependent:
        # - True -> precedents direction
        # - False -> dependent direction
        returnRange = rng.NavigateArrow(False, i)

        # if it's an external reference -> parent sheets are different
        if rng.Parent.Name != returnRange.Parent.Name:
            while returnRange:
                j += 1
                try:
                    returnRange = rng.NavigateArrow(False, i, j)
                    res.add((returnRange.Parent.Name, returnRange.address))
                    res |= get_all_dependents(xlApp, returnRange)

                except Exception as e:
                    # out of range: NavigateArrow method of Range class failed
                    returnRange = None   

        # if it's an internal reference
        else:
            # the last reference would be itself -> then stop the loop
            # for merged ranges, the last reference is the first cell
            if xlApp.Intersect(returnRange, rng):
                break

            res.add((returnRange.Parent.Name, returnRange.address))
            res |= get_all_dependents(xlApp, returnRange)

    return res

至此，主要内容已结束。对于引用关系复杂的工作簿，上述返回集合可能非常大，因此考虑生成器的版本：

def get_all_dependents(xlApp, rng):
    '''get all dependents of current range from all worksheets'''
    # show dependents arrows first
    rng.ShowDependents()

    i, j = 0, 0
    while True:
        i += 1
        # navigate to next dependent:
        # - True -> precedents direction
        # - False -> dependent direction
        returnRange = rng.NavigateArrow(False, i)

        # if it's an external reference -> parent sheets are different
        if rng.Parent.Name != returnRange.Parent.Name:
            while returnRange:
                j += 1
                try:
                    returnRange = rng.NavigateArrow(False, i, j)
                    yield (returnRange.Parent.Name, returnRange.address)
                    # it does not work if just call this generator, 
                    # instead it should be iterated explicitly
                    # get_all_dependents(xlApp, returnRange)
                    for x in get_all_dependents(xlApp, returnRange):
                        yield x
                except Exception as e:
                    # out of range: NavigateArrow method of Range class failed
                    returnRange = None   

        # if it's an internal reference
        else:
            # the last reference would be itself -> then stop the loop
            # for merged ranges, the last reference is the first cell
            if xlApp.Intersect(returnRange, rng):
                break

            yield (returnRange.Parent.Name, returnRange.address)
            for x in get_all_dependents(xlApp, returnRange):
                yield x

yield关键字可以方便地将普通函数转为生成器，需要注意的是转换递归函数时，需要显式地迭代生成器，否则它不会自动计算下一个值。

  1. Range.NavigateArrow method (Excel) ↩