在Excel中使用python函数（xlwings）

天上飞鸡 2020-12-07

展开全文

背景

Excel中的自定义函数使用VBA开发，缺乏python的灵活性，而python中有便于数据处理的库，比如numpy/requests/sqlalchemy等，还能用matplotlib画图

直接调用python是不可能的，间接的技术实现方式有xll插件和com组件

xll的方式产品是PyXLL，收费

com组件通信的方式，产品是xlwings，是由ExcelPython项目整合而来的

xlwings中的ExcelPython部分，就是COM组件与python通信的部分，大概4-5年没有什么更新了

环境

Windows 10 x64

Excel 365 x64

Miniconda python3 x64

xlwings 0.17.1

官方使用方法

官方的方法是先xlwings quickstart project_name，初始化一个项目文件夹出来，包含xlsm和py

然后xlwings addin install，把xla插件安装到excel的自动启动目录（XLSTART）

与Matlab编译插件后导入Excel类似，需要打开一个设置：

文件->选项->信任中心->信任中心设置...->宏设置->信任对VBA工程对象模型的访问

打开项目中的xlsm文件，在VBA中已经有一个Sub了，这个不是UDF

如果要把UDF添加到当前的workbook，要在xlwings菜单中先设置python环境

它支持官网python和conda两种配置方式

其中官方python由COM程序直接调用pythonw，不会出现cmd窗口

而conda环境需要先activate，这一部需要在cmd中由conda.bat处理环境变量，因此会出现一个黑窗口，没有内容，需要手动关闭。后台服务启动后，再次运行就不会出现这个窗口了。重新导入函数时，或者关闭后台后再次运行，还会出现

然后在菜单（Ribbon）上点击Import Function即可导入UDF，与下文myfunction函数内容一致

定制使用方法

首先要找到VBA中调用后台的方法。xlwings的VBA是加密的，先找工具把它解密，然后看代码

主要都是在处理运行环境，比如路径：python目录和当前文件夹的路径等，系统：win64/32/mac等

最终就是加载一个编译好的DLL，调用其中的函数

加载DLL的方法

Declare PtrSafe Function XLPyDLLActivateAuto Lib "xlwings64-0.17.1.dll" (ByRef result As Variant, Optional ByVal Config As String = "", Optional ByVal mode As Long = 1) As Long
Private Declare PtrSafe Function LoadLibrary Lib "kernel32" Alias "LoadLibraryA" (ByVal lpLibFileName As String) As Long
Function Py2()
    LoadLibrary ("C:\Users\cdarling\Miniconda3\envs\xlwings\xlwings64-0.17.1.dll")
    cmd = "C:\Users\cdarling\Miniconda3\envs\xlwings\pythonw.exe -B -c ""import sys, os;" & _
            "sys.path[0:0]=[r'C:\Users\cdarling\PycharmProjects\xlwings_sample'];" & _
            "import init_env;" & _
            "import xlwings; xlwings.server.serve('$(CLSID)')"
    If 0 <> XLPyDLLActivateAuto(Py2, cmd, 1) Then Err.Raise 1000, Description:=Py2
End Function

其中的参数cmd，是python的运行命令和参数，需要在参数中用字符串写好py脚本

如果要关闭后台，第三个参数由1改成-1即可，如果cmd字符串一样，就会关闭原来的后台了

找不到原来的cmd字符串的话，可以在任务管理器中找到它，运行命令行可以看到cmd那一大串

然后就能调用它了

Function myfunction(x)
    If TypeOf Application.Caller Is Range Then On Error GoTo failed
    myfunction = Py2.CallUDF("sample", "myfunction", Array(x), ThisWorkbook, Application.Caller)
    Exit Function
failed:
    myfunction = Err.Description
End Function

其实不需要报错的话，只要其中的Py2.CallUDF一行就行了

它调用的python文件sample.py如下

import xlwings as xw
import numpy as np

#@xw.func
@xw.arg('x',np.array)
def myfunction(x):
    print(x.T)
    return x+2

@xw.func
def hi():
    return 3

@xw.sub
def test1():
    wb=xw.Book.caller()
    wb.sheets[0].range("A1").value="hello from py"

if __name__ ==  '__main__':
    xw.serve()

在Excel中创建3x3的数字，然后选中另一块3x3区域，写上=myfunction(A1:C3)按Ctrl+Shift+Enter即可

如果要用UDF的方式，使用后台COM服务运行Sub，代码如下

Public Function RunPython2(PythonCommand As String)
Py2.SetAttr Py2.Module("xlwings._xlwindows"), "BOOK_CALLER", ActiveWorkbook
Py2.Exec "" & PythonCommand & ""
End Function

Sub test1()
'RunPython2 ("import xlwings;xlwings.Book.caller().sheets[0].range('A1').value='hi xlwings'")
RunPython2 ("import sample;sample.test1()")
End Sub

为了使用更加灵活的conda环境，又不想出现黑窗口，研究了它出现的原因和解决办法

conda.bat激活conda环境需要在cmd中运行，而pythonw不需要

如果能在pythonw中完成同样的操作，即可避开cmd命令和黑窗口了

尝试了conda.cli.activate，但它说它已经deprecated，而且尝试没有成功，能import xlwings，但不能import numpy，说无法加载DLL

手工对比sys.path和os.environ['PATH']后，了解到PATH变量中需要加入几个路径

那么问题就来了：要在字符串中配置吗，要在VBA中处理路径吗

也不用，可以在相应conda环境的site-package目录中加入启动脚本

文件位置：

Miniconda3\envs\xlwings\Lib\site-packages\sitecustomize.py

内容就是看看conda activate xlwings之后，os.environ['PATH']里多了啥，就：

=[新加入的路径]+os.environ['PATH']

import sys,os
import pathlib
p=pathlib.Path(sys.path[0]).parent
os.environ['PATH']=';'.join([str(pp) for pp in (p,p/'Library'/'mingw-w64'/'bin',p/'Library'/'usr'/'bin',p/'Library'/'bin',p/'Scripts',p/'bin')])+os.environ['PATH']