有没有办法在 Excel-VBA 中调用 Python 代码？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/45410316/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is there a way to call a Python code in Excel-VBA?
提问by Pratheek P Manangi
I have an Excel file (Main.xlsm) containing macros. I have a Python file (python.py) to generate a subsidiary Excel file (sub.xlsx) which I would further call in the macros of Main.xlsm-file. This sub.xlsx-file which is generated by the run of python.py is saved in the same working directory.
我有一个包含宏的 Excel 文件 (Main.xlsm)。我有一个 Python 文件 (python.py) 来生成一个辅助 Excel 文件 (sub.xlsx)，我将在 Main.xlsm 文件的宏中进一步调用它。这个由 python.py 运行生成的 sub.xlsx 文件保存在同一工作目录中。
Now I want to make this python.py to be executed during the run of the Main.xlsm macros and then use this xlsx-file. I basically want to reduce the step of executing python.py externally. Is there a command for that? I am new to VBA.
现在我想让这个 python.py 在 Main.xlsm 宏的运行期间被执行，然后使用这个 xlsx 文件。我基本上想减少外部执行python.py的步骤。有命令吗？我是 VBA 的新手。
回答by Uri Goren
The simplest way is to run the python interpreter with the Shellcommand
最简单的方法是使用Shell命令运行python解释器
Shell ("python.exe " & yourScript & " " & arguments)
回答by Gabor
Yes, there is. My preferred way of doing this is through xlwings (https://www.xlwings.org/), but there are several other options as well. XlWings is great because it's free, open source and easy to use, with great documentation. There are some feature limitations though, so you'd have to check if it fits your needs.
就在这里。我的首选方法是通过 xlwings ( https://www.xlwings.org/)，但还有其他几种选择。XlWings 很棒，因为它是免费的、开源的、易于使用的，还有很好的文档。但是有一些功能限制，因此您必须检查它是否符合您的需求。
回答by Florent B.
There are multiple ways tu run a python script with VBA depending on whether you need to wait for the end of the execution and know if it went without error.
有多种方法可以使用 VBA 运行 python 脚本，具体取决于您是否需要等待执行结束并知道它是否没有错误。
With Shell, asynchronous with console:
使用Shell，与控制台异步：
Public Sub RunPython(file As String, ParamArray args())
Shell "python.exe """ & file & """ " & Join(args, " ")
End Sub
With Shell, synchronous without console:
使用Shell，无需控制台即可同步：
Public Function RunPython(file As String, ParamArray args())
Shell "pythonw.exe """ & file & """ " & Join(args, " ")
End Function
With WScript.Shell, synchronous without console and with exit code:
使用WScript.Shell，无需控制台和退出代码即可同步：
Public Function RunPython(file As String, ParamArray args()) As Long
Dim obj As Object
Set obj = CreateObject("WScript.Shell")
RunPython = obj.Run("pythonw.exe """ & file & """ " & Join(args, " "), 0, True)
End Function
回答by S Meaden
I had a whole Python month on my blog right here. I establish a pattern which I call the gateway class which is a COM enabled Python class, it will register itself if run from the command line and once registered is instantiated with CreateObject("foo.bar").
我在我的博客上度过了整个Python 月。我建立了一个模式，我称之为网关类，它是一个启用 COM 的 Python 类，如果从命令行运行，它将自行注册，一旦注册，将使用 CreateObject("foo.bar") 进行实例化。
Here is a good example of VBA calling a Python class that uses some scipy functions
这是 VBA 调用使用一些 scipy 函数的 Python 类的一个很好的例子
import numpy as np
import pandas as pd
from scipy.stats import skewnorm
class PythonSkewedNormal(object):
_reg_clsid_ = "{1583241D-27EA-4A01-ACFB-4905810F6B98}"
_reg_progid_ = 'SciPyInVBA.PythonSkewedNormal'
_public_methods_ = ['GeneratePopulation', 'BinnedSkewedNormal']
def GeneratePopulation(self, a, sz):
# https://docs.scipy.org/doc/numpy-1.15.1/reference/generated/numpy.random.seed.html
np.random.seed(10)
# https://docs.scipy.org/doc/scipy-0.19.1/reference/generated/scipy.stats.skewnorm.html
return skewnorm.rvs(a, size=sz).tolist()
def BinnedSkewedNormal(self, a, sz, bins):
# https://docs.scipy.org/doc/numpy-1.15.1/reference/generated/numpy.random.seed.html
np.random.seed(10)
# https://docs.scipy.org/doc/scipy-0.19.1/reference/generated/scipy.stats.skewnorm.html
pop = skewnorm.rvs(a, size=sz)
bins2 = np.array(bins)
bins3 = pd.cut(pop, bins2)
table = pd.value_counts(bins3, sort=False)
table.index = table.index.astype(str)
return table.reset_index().values.tolist()
if __name__ == '__main__':
print("Registering COM server...")
import win32com.server.register
win32com.server.register.UseCommandLine(PythonSkewedNormal)
and the calling VBA code
和调用 VBA 代码
Option Explicit
Sub TestPythonSkewedNormal()
Dim skewedNormal As Object
Set skewedNormal = CreateObject("SciPyInVBA.PythonSkewedNormal")
Dim lSize As Long
lSize = 100
Dim shtData As Excel.Worksheet
Set shtData = ThisWorkbook.Worksheets.Item("Sheet3") '<--- change sheet to your circumstances
shtData.Cells.Clear
Dim vBins
vBins = Array(-5, -4, -3, -2, -1, 0, 1, 2, 3, 4, 5)
'Stop
Dim vBinnedData
vBinnedData = skewedNormal.BinnedSkewedNormal(-5, lSize, vBins)
Dim rngData As Excel.Range
Set rngData = shtData.Cells(2, 1).Resize(UBound(vBins) - LBound(vBins), 2)
rngData.Value2 = vBinnedData
'Stop
End Sub
Full commentary can be found at the original blog entry here
The advantage here is that there is no shelling. When the code it returns, you know it has finished, with shelling once has to check if the shelled process has ended etc. This gateway class is much better IMHO.
这里的优点是没有炮击。当它返回代码时，你知道它已经完成，脱壳一次必须检查脱壳进程是否已经结束等。恕我直言，这个网关类要好得多。
