从excel/vba调用python脚本

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个参数(scalars)和一个文本文件，然后返回一个double的vector。我想在vba中编写一个宏来调用这个python代码，并在同一个excel表中编写结果。我想知道最简单的方法是什么，以下是我发现的一些东西:

• call the shell() function in vba but it doesn't seem so easy to get the return value.

  调用vba中的shell()函数，但是获取返回值似乎并不那么容易。

• 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

  将python代码注册为COM对象，并从vba——>调用它，我不知道该怎么做，所以如果您有一些示例，它将非常受欢迎

• 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中创建一个geoprocessing对象，然后添加工具箱，然后我们可以直接通过geoprocessing对象使用这个自定义工具，但这也是我不知道该怎么做的事情。

Any tips?

任何建议吗?

6 个解决方案

#1

Follow these steps carefully

遵循以下步骤仔细

1. Go to Activestate and get ActivePython 2.5.7 MSI installer.
   I had DLL hell problems with 2.6.x
2. 转到Activestate并获取ActivePython 2.5.7 MSI安装程序。我在2。6。x上遇到了DLL问题
3. Install in your Windows machine
4. 安装在您的Windows机器

5. once install is complete open Command Prompt and go to

   安装完成后，打开命令提示符并转到

   C:\Python25\lib\site-packages\win32comext\axscript\client

   C:\ Python25 \ lib \网站\ win32comext \ axscript \客户机

6. execute \> python pyscript.py you should see message Registered: Python

   执行python pyscript \ >。您应该看到已注册的消息:Python

7. Go to ms office excel and open worksheet

   到ms office excel中打开工作表

8. Go to Tools > Macros > Visual Basic Editor
9. 转到工具>宏> Visual Basic编辑器
10. Add a reference to the Microsoft Script control
11. 添加对Microsoft脚本控件的引用
12. Add a new User Form. In the UserForm add a CommandButton
13. 添加一个新的用户表单。在UserForm中添加一个命令按钮

14. Switch to the code editor and Insert the following code

    切换到代码编辑器并插入以下代码

    Dim WithEvents PyScript As MSScriptControl.ScriptControl

    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

执行。在必要的时候享受和扩展

#2

Here is a good link for Excel from/to Python usage:

下面是Excel与Python用法的一个很好的链接:

continuum.io/using-excel

continuum.io /使用excel

mentions xlwings, DataNitro, ExPy, PyXLL, XLLoop, openpyxl, xlrd, xlsxwriter, xlwt

提到xlwings, DataNitro, ExPy, PyXLL, XLLoop, openpyxl, xlrd, xlsxwriter, xlwt

Also I found that ExcelPython is under active development.

我还发现ExcelPython正在积极开发中。

1. https://github.com/ericremoreynolds/excelpython
2. https://github.com/ericremoreynolds/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脚本的谷歌文档、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):

这不是免费的方法，但值得一提(在《福布斯》和《纽约时报》上):

https://datanitro.com

https://datanitro.com

---

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编写的函数。

#3

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 hook来指导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( )

#4

Updated 2018

xlwings is 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.
• 脚本:使用接近VBA的语法从Python中自动化/与Excel交互。
• Macros: Replace your messy VBA macros with clean and powerful Python code.
• 宏:用干净而强大的Python代码替换混乱的VBA宏。

• UDFs: Write User Defined Functions (UDFs) in Python (Windows only).

  udf:在Python中编写用户定义的函数(udf)。

• Installation

  安装

• Quickstart

  快速入门

#5

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。

#6

Another open source python-excel in process com tool. This allows executing python scripts from excel in a tightly integrated manner.

另一个开放源码python-excel中的process com工具。这允许以一种紧密集成的方式从excel中执行python脚本。

https://pypi.python.org/pypi/Python-For-Excel/beta,%201.1

https://pypi.python.org/pypi/Python-For-Excel/beta,% 201.1