背景

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的方法

  1. 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
  2. Private Declare PtrSafe Function LoadLibrary Lib "kernel32" Alias "LoadLibraryA" (ByVal lpLibFileName As String) As Long
  3. Function Py2()
  4.     LoadLibrary ("C:\Users\cdarling\Miniconda3\envs\xlwings\xlwings64-0.17.1.dll")
  5.     cmd = "C:\Users\cdarling\Miniconda3\envs\xlwings\pythonw.exe -B -c ""import sys, os;" & _
  6.             "sys.path[0:0]=[r'C:\Users\cdarling\PycharmProjects\xlwings_sample'];" & _
  7.             "import init_env;" & _
  8.             "import xlwings; xlwings.server.serve('$(CLSID)')"
  9.     If 0 <> XLPyDLLActivateAuto(Py2, cmd, 1) Then Err.Raise 1000, Description:=Py2
  10. End Function

其中的参数cmd，是python的运行命令和参数，需要在参数中用字符串写好py脚本
如果要关闭后台，第三个参数由1改成-1即可，如果cmd字符串一样，就会关闭原来的后台了
找不到原来的cmd字符串的话，可以在任务管理器中找到它，运行命令行可以看到cmd那一大串

然后就能调用它了

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

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

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

  1. import xlwings as xw
  2. import numpy as np
  4. #@xw.func
  5. @xw.arg('x',np.array)
  6. def myfunction(x):
  7.     print(x.T)
  8.     return x+2
  10. @xw.func
  11. def hi():
  12.     return 3
  14. @xw.sub
  15. def test1():
  16.     wb=xw.Book.caller()
  17.     wb.sheets[0].range("A1").value="hello from py"
  19. if __name__ ==  '__main__':
  20.     xw.serve()

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

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

  1. Public Function RunPython2(PythonCommand As String)
  2. Py2.SetAttr Py2.Module("xlwings._xlwindows"), "BOOK_CALLER", ActiveWorkbook
  3. Py2.Exec "" & PythonCommand & ""
  4. End Function
  6. Sub test1()
  7. 'RunPython2 ("import xlwings;xlwings.Book.caller().sheets[0].range('A1').value='hi xlwings'")
  8. RunPython2 ("import sample;sample.test1()")
  9. 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’]

  1. import sys,os
  2. import pathlib
  3. p=pathlib.Path(sys.path[0]).parent
  4. 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']

要点

conda环境，比如叫xlwings
在VBA中加载dll

配置环境变量PATH的py脚本或字符串命令，以加载numpy的DLL
配置环境变量PATH的py脚本或字符串命令，以加载自己的py文件（模块）
根据以上信息，编写运行COM通信的指令

写成一个xlsm/xlam，放入自动启动，即可每次调用相应函数