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

(0)

相关推荐