在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,放入自动启动,即可每次调用相应函数