Excel中的自定義函數(shù)使用VBA開發(fā)，缺乏python的靈活性，而python中有便于數(shù)據(jù)處理的庫，比如numpy/requests/sqlalchemy等，還能用matplotlib畫圖
直接調(diào)用python是不可能的，間接的技術(shù)實現(xiàn)方式有xll插件和com組件
xll的方式產(chǎn)品是PyXLL，收費
com組件通信的方式，產(chǎn)品是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編譯插件后導(dǎo)入Excel類似，需要打開一個設(shè)置：
文件->選項->信任中心->信任中心設(shè)置...->宏設(shè)置->信任對VBA工程對象模型的訪問
打開項目中的xlsm文件，在VBA中已經(jīng)有一個Sub了，這個不是UDF
如果要把UDF添加到當(dāng)前的workbook，要在xlwings菜單中先設(shè)置python環(huán)境
它支持官網(wǎng)python和conda兩種配置方式
其中官方python由COM程序直接調(diào)用pythonw，不會出現(xiàn)cmd窗口
而conda環(huán)境需要先activate，這一部需要在cmd中由conda.bat處理環(huán)境變量，因此會出現(xiàn)一個黑窗口，沒有內(nèi)容，需要手動關(guān)閉。后臺服務(wù)啟動后，再次運行就不會出現(xiàn)這個窗口了。重新導(dǎo)入函數(shù)時，或者關(guān)閉后臺后再次運行，還會出現(xiàn)
然后在菜單（Ribbon）上點擊Import Function即可導(dǎo)入UDF，與下文myfunction函數(shù)內(nèi)容一致
首先要找到VBA中調(diào)用后臺的方法。xlwings的VBA是加密的，先找工具把它解密，然后看代碼
主要都是在處理運行環(huán)境，比如路徑：python目錄和當(dāng)前文件夾的路徑等，系統(tǒng)：win64/32/mac等
最終就是加載一個編譯好的DLL，調(diào)用其中的函數(shù)
加載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
其中的參數(shù)cmd，是python的運行命令和參數(shù)，需要在參數(shù)中用字符串寫好py腳本
如果要關(guān)閉后臺，第三個參數(shù)由1改成-1即可，如果cmd字符串一樣，就會關(guān)閉原來的后臺了
找不到原來的cmd字符串的話，可以在任務(wù)管理器中找到它，運行命令行可以看到cmd那一大串
然后就能調(diào)用它了
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一行就行了
它調(diào)用的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中創(chuàng)建3x3的數(shù)字，然后選中另一塊3x3區(qū)域，寫上=myfunction(A1:C3)按Ctrl+Shift+Enter即可
如果要用UDF的方式，使用后臺COM服務(wù)運行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環(huán)境，又不想出現(xiàn)黑窗口，研究了它出現(xiàn)的原因和解決辦法
conda.bat激活conda環(huán)境需要在cmd中運行，而pythonw不需要
如果能在pythonw中完成同樣的操作，即可避開cmd命令和黑窗口了
嘗試了conda.cli.activate，但它說它已經(jīng)deprecated，而且嘗試沒有成功，能import xlwings，但不能import numpy，說無法加載DLL
手工對比sys.path和os.environ['PATH']后，了解到PATH變量中需要加入幾個路徑
那么問題就來了：要在字符串中配置嗎，要在VBA中處理路徑嗎
也不用，可以在相應(yīng)conda環(huán)境的site-package目錄中加入啟動腳本
文件位置：
Miniconda3\envs\xlwings\Lib\site-packages\sitecustomize.py
內(nèi)容就是看看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環(huán)境，比如叫xlwings
在VBA中加載dll
配置環(huán)境變量PATH的py腳本或字符串命令，以加載numpy的DLL
配置環(huán)境變量PATH的py腳本或字符串命令，以加載自己的py文件（模塊）
根據(jù)以上信息，編寫運行COM通信的指令
寫成一個xlsm/xlam，放入自動啟動，即可每次調(diào)用相應(yīng)函數(shù)
