在Excel中使用python函数（xlwings）
背景
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 LongPrivate Declare PtrSafe Function LoadLibrary Lib "kernel32" Alias "LoadLibraryA" (ByVal lpLibFileName As String) As LongFunction 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:=Py2End 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 Functionfailed: myfunction = Err.DescriptionEnd Function其实不需要报错的话，只要其中的Py2.CallUDF一行就行了
它调用的python文件sample.py如下
import xlwings as xwimport numpy as np#@xw.func@xw.arg('x',np.array)def myfunction(x): print(x.T) return x+2@xw.funcdef hi(): return 3@xw.subdef 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", ActiveWorkbookPy2.Exec "" & PythonCommand & ""End FunctionSub 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,osimport pathlibp=pathlib.Path(sys.path[0]).parentos.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，放入自动启动，即可每次调用相应函数