从 excel/vba 调用 python 脚本
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3567365/
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
Calling python script from excel/vba
提问by Anninha
I have a python code that reads 3 arguments (scalars) and a text files and then returns me a vector of double. I want to write a macro in vba to call this python code and write the results in one of the same excel sheet. I wanted to know what was the easiest way to do it, here are some stuffs that I found:
我有一个 python 代码，它读取 3 个参数（标量）和一个文本文件，然后返回一个双精度向量。我想在 vba 中编写一个宏来调用此 python 代码并将结果写入同一张 Excel 表中。我想知道最简单的方法是什么，这里有一些我发现的东西：
call the shell() function in vba but it doesn't seem so easy to get the return value.
register the python code as a COM object and call it from vba--> i don't know how to do that so if you have some examples it would be more than welcome
create a custom tool in a custom toolbox, in vba create a geoprocessing object and then addtoolbox and then we can use the custom tool directly via the geoprocessing object but this is something as well that I don't know how to do..
在 vba 中调用 shell() 函数，但是获取返回值似乎并不那么容易。
将 python 代码注册为 COM 对象并从 vba 调用它--> 我不知道怎么做，所以如果你有一些例子，那将是非常受欢迎的
在自定义工具箱中创建自定义工具，在 vba 中创建一个地理处理对象，然后添加工具箱，然后我们可以通过地理处理对象直接使用自定义工具，但这也是我不知道该怎么做的事情。
Any tips?
有小费吗？
回答by Katriel
Do you have to call the Python code as a macro? You could use COM hooks within the Python script to direct Excel and avoid having to use another language:
您是否必须将 Python 代码作为宏调用？您可以在 Python 脚本中使用 COM 挂钩来引导 Excel 并避免使用其他语言：
import win32com.client
# Start Excel
xlApp = win32com.client.Dispatch( "Excel.Application" )
workbook = xlApp.Workbooks.Open( <some-file> )
sheet = workbook.Sheets( <some-sheet> )
sheet.Activate( )
# Get values
spam = sheet.Cells( 1, 1 ).Value
# Process values
...
# Write values
sheet.Cells( ..., ... ).Value = <result>
# Goodbye Excel
workbook.Save( )
workbook.Close( )
xlApp.Quit( )
回答by renick
Follow these steps carefully
仔细按照这些步骤
- Go to Activestate and get ActivePython 2.5.7MSI installer.
I had DLL hell problems with 2.6.x - Install in your Windows machine
once install is complete open Command Prompt and go to
C:\Python25\lib\site-packages\win32comext\axscript\client
execute
\> python pyscript.pyyou should see message Registered: PythonGo to ms office excel and open worksheet
- Go to Tools > Macros > Visual Basic Editor
- Add a reference to the Microsoft Script control
- Add a new User Form. In the UserForm add a CommandButton
Switch to the code editor and Insert the following code
Dim WithEvents PyScript As MSScriptControl.ScriptControl
Private Sub CommandButton1_Click() If PyScript Is Nothing Then Set PyScript = New MSScriptControl.ScriptControl PyScript.Language = "python" PyScript.AddObject "Sheet", Workbooks(1).Sheets(1) PyScript.AllowUI = True End If PyScript.ExecuteStatement "Sheet.cells(1,1).value='Hello'" End Sub
- 转到 Activestate 并获取ActivePython 2.5.7MSI 安装程序。
我在 2.6.x 上遇到了 DLL 地狱问题 - 安装在你的 Windows 机器上
安装完成后打开命令提示符并转到
C:\Python25\lib\site-packages\win32comext\axscript\client
执行
\> python pyscript.py你应该看到消息 Registered: Python转到 ms office excel 并打开工作表
- 转到工具 > 宏 > Visual Basic 编辑器
- 添加对Microsoft Script 控件的引用
- 添加一个新的用户表单。在用户窗体中添加一个命令按钮
切换到代码编辑器并插入以下代码
Dim WithEvents PyScript 作为 MSScriptControl.ScriptControl
Private Sub CommandButton1_Click() If PyScript Is Nothing Then Set PyScript = New MSScriptControl.ScriptControl PyScript.Language = "python" PyScript.AddObject "Sheet", Workbooks(1).Sheets(1) PyScript.AllowUI = True End If PyScript.ExecuteStatement "Sheet.cells(1,1).value='Hello'" End Sub
Execute. Enjoy and expand as necessary
执行。根据需要享受和扩展
回答by denfromufa
Here is a good link for Excel from/to Python usage:
这是 Excel from/to Python 使用的一个很好的链接：
continuum.io/using-excel
continuum.io/using-excel
mentions xlwings, DataNitro, ExPy, PyXLL, XLLoop, openpyxl, xlrd, xlsxwriter, xlwt
提到了 xlwings、DataNitro、ExPy、PyXLL、XLoop、openpyxl、xlrd、xlsxwriter、xlwt
Also I found that ExcelPython is under active development.
我还发现 ExcelPython 正在积极开发中。
2.
2.
What you can do with VBA + Python is following:
您可以使用 VBA + Python 执行以下操作：
Compile your py scripts that take inputs and generate outputs as text files or from console. Then VBA will prepare input for py, call the pre-compiled py script and read back its output.
编译您的 py 脚本，这些脚本接受输入并将输出作为文本文件或从控制台生成。然后 VBA 将为 py 准备输入，调用预编译的 py 脚本并读回其输出。
3.
3.
Consider Google Docs, OpenOffice or LibreOffice which support Python scripts.
考虑支持 Python 脚本的 Google Docs、OpenOffice 或 LibreOffice。
This is assuming that available options with COM or MS script interfaces do not satisfy your needs.
这是假设带有 COM 或 MS 脚本接口的可用选项不能满足您的需求。
This is not free approach, but worth mentioning (featured in Forbes and New York Times):
这不是免费的方法，但值得一提（《福布斯》和《纽约时报》精选）：
This is not free for commercial use:
这不是免费用于商业用途：
PyXLL - Excel addin that enables functions written in Python to be called in Excel.
PyXLL - Excel 插件，允许在 Excel 中调用用 Python 编写的函数。
回答by ehremo
There's a tutorial on CodeProject on how to do this.
CodeProject 上有一个关于如何执行此操作的教程。
See http://www.codeproject.com/Articles/639887/Calling-Python-code-from-Excel-with-ExcelPython.
请参阅http://www.codeproject.com/Articles/639887/Calling-Python-code-from-Excel-with-ExcelPython。
回答by Testautomation
Another open source python-excel in process com tool. This allows executing python scripts from excel in a tightly integrated manner.
另一个开源python-excel in process com工具。这允许以紧密集成的方式从 excel 执行 python 脚本。
回答by Christopher Peisert
Updated 2018
2018 年更新
xlwingsis a BSD-licensed Python library that makes it easy to call Python from Excel and vice versa.
xlwings是一个 BSD 许可的 Python 库，它可以轻松地从 Excel 调用 Python，反之亦然。
- Scripting: Automate/interact with Excel from Python using a syntax that is close to VBA.
- Macros: Replace your messy VBA macros with clean and powerful Python code.
UDFs: Write User Defined Functions (UDFs) in Python (Windows only).
